Google Apps Script: Lite Contacts Directory for Sites or as a Service

A while back I came across a guest post on the Google Apps Developer blog by Steve Webster at Dito which demonstrated an Autocomplete Email Addresses in Apps Script. Not only was this a great demonstration of what you can do with the Apps Script UI but Steve and the guys at Dito had made the script available for others to try.

So recently when I was asked to make a staff directory gadget to sit in Google Sites I immediately thought of Steve’s example and used this as the foundation for my own GAS Staff Directory. This script allows a users to search a Google Spreadsheet of contact information, filtering by different parameters and edit their own information personal information. The following video demonstrates the gadget in action, you can also try it published as a service or embedded in a Google Site (Note: names/details used in the examples aren’t accurate just a selection of Scottish authors):

<

The even better news is that with the kind permission of Steve and the folks at Dito the code for this gadget is being made available for all under an open source license. So here it is:

*** GAS Contact Directory ***

How to deploy

A word of warning I haven’t spent much time making this script customisable but have added lots of commenting so if you don’t want a ‘Role’ box or want to change it’s label you’ll need to do some tweaking.

  1. Grab a copy of the template spreadsheet
  2. Populate it with your staff information
  3. Open Tools > Script editor … and Run > getID (you may need to run this twice, the first time to authorize the script), then open View > Logs… In the log should be a spreadsheet ID for you to copy and paste into line 16 replacing the existing ID
  4. This gadget also has the option for signed in users to add or edit their personal details if you would like to disable this change the enableEdit to false on line 17
  5. You might also like to edit the instructions the user sees on the search (textOverview) and edit tab (editText)

At this point you have two options: publish the spreadsheet as a service (this gives you a dedicated url BTW there is still a bug that prevents you embedding this page into your own site by using a iframe. Here’s the discussion thread on issue 657); or embed as a gadget in Google Sites.

Publish as service

  1. From the script editor select Share > Publish as Service…. For the gadget to be visible to other users you need to allow anyone to invoke the service. If you are allowing anonymous access it is advised that you disable the edit tab (See No#4 above)
  2. Check ‘Enable Service’ and copy the URL
  3. Click Save.

Embed as a gadget in Google Sites

  1. From the Script editor in Google Spreadsheet copy all of the code
  2. Open your Google Site and navigate to the Manage Site page
  3. Select App Script then ‘Add new script’ pasting the code from the spreadsheet script editor
  4. Save then Run > doGet to authorize the script
  5. Open the page you would like to add the gadget to and enter edit mode.
  6. Move the cursor to the point in the page where you would like to insert the gadget then Insert > Apps Script Gadget
  7. Various setting for you to play with (the minimum dimensions are 275x632px)
  8. Save the setting, then save the page

I won’t bore you with the code but here is a link to download/view it separately (I’m most proud of the find(haystack, needles) function which can also comma separated values). Big thanks again to Dito for letting me modify and redistribute their code. If you are looking for a more heavy weight solution to this you might want to check out the  Dito Directory on the Google Apps Marketplace.

35 thoughts on “Google Apps Script: Lite Contacts Directory for Sites or as a Service

  1. melissa

    Looks great, but I can't seem to use the template

    "We're sorry, your spreadsheet cannot be copied at this time. "

    Reply
  2. Hey Martin, as usual, I google something and your website seems to come up.

    I couldnt figure how to use Google App Script inside my Google Site, (all it was was a link I was overlooking) but somehow your site came up and in the midst of this awesome post I both learnt something new, got my answer and I'm onto a new idea.

    I must say, you're inspiring me to take my Google Apps Scripting skills (JavaScript) plus my python (cuz I like Google App Engine) skills to another level.

    Kudos.

    Btw, is there another blog as awesome as yours for Google App Engine?

    Reply
    1. Post author

      Hi Mark - glad you found what you were looking for ;) Dabbled with App Engine a year or so ago but retreated back to Apps Script (not overly familiar with what's going on in App Engine).

      Thanks,
      Martin

      Reply
    1. Post author

      Hi - yes. To add an image add a column to your spreadsheet with a photo url and then in the writeResults function (line 368+) you need to add an image holder:

      var pictureHolder = app.createImage(r[column_number_of_picture_urls]);

      You can add CSS styling to the image holder with for example pictureHolder.setStyleAttribute("float", "right"); (the css attribute needs to be normalised e.g. padding-left is paddingLeft

      More info on the createImage options here

      then add itemElement.add(pictureHolder);

      towards the end of the function. For example to add after the name holder insert the line above after itemElement.add(nameHolder);

      Hope this helps.

      Reply
  3. Hi Martin,

    Thank you for the code. On running the search the image comes up as a white broken square. Is there a type of image required (i.e. gif , jpg) ? Also cna you store the image in Google Docs for access?

    Regards

    James

    Reply
    1. Post author

      Hi - make sure you are referencing the right spreadsheet id at the beginning of the script (all web friendly image formats should work). Picasa might be a place to upload images.
      Martin

      Reply
  4. Sorin

    Hi. Your code looks really nice. Is there a way to customize the Results list and make the email into a hyperlink (e.g. mailto: [email protected]). I tried that on line 376 (contactHolder.setText(0 , 1, "HA Email: "+r[JA_EMAIL_COL]+"");) and I am getting HTML entities like < instead of the working HTML code.

    Thanks

    Reply
      1. Kevin Johnson

        Martin,
        Thank you for creating this. I'm giving it a go.

        I'm trying to get the mailto: link to work but it appears the createinlinehyperlink method is depreciated so I tried createAnchor. It looks correct on the page but the underlying link is an odd redirect error. I don't know if this is Google Sites parsing the link in a particular way or what. Any ideas?
        Thanks,
        Kevin

        Reply
        1. Post author

          Ah that is a problem. when you create the anchor do you use mailto in it? My thought was if you just use an email address in the anchor Google would detect it as that and handle the redirect differently
          Martin

          Reply
          1. Kevin Johnson

            I tried various combos... with and without mailto, etc, and it's as if google always parses it and re-writes the link in the context of a google site, not a simple link.

  5. Andrew

    Hi Martin,

    Thanks very much for this - it's very useful. I'm setting this up for an organisation in Ecuador - not all of the employees have an email address, so I've modified it so that a google login is not required. But I can't seem to find a simple way of modifying the script so that someone can modify anyone else's contact details, rather than only the logged-in user themselves.

    Any help would be much appreciated.

    Regards,

    Andrew

    Reply
    1. Andrew

      Andrew / Martin

      Nice to know more Spanish-speaking people to handle Google Sites.
      I've recently seen this great blog and I've tried to use the code listed for my Intranet that is in Google Sites but I get an error a message from Google Drive. The spreadsheet has permissions for my organization but despite insert it I get the error that the document does not exist

      Some help pls

      Reply
  6. James Sanger

    Hi Martin,

    We have a problem with the Google Apps Script since Google activate Google Drive yesterday. Does your script work with Google Drive?

    Regards

    James

    Reply
  7. Errol Sweetland

    Hi Martin,

    I'm trying to set up an IT Support staff directory for my workplace using a Google Site. After carrying out extensive searches your excellent script fits my purpose exactly. My problem is embedding in a Google Site do you know if any headway has been made in resolving the bug? On this web page you provide an google site example demo of the directory how did you achieve this given the bug.

    And finally if deploying the script as a service can content be added to the generated URL page such as a title and an explanation on how to use the search, to make the link a little more user friendly. I'm not a programmer so your help would be appreciated.

    Regards
    Errol

    Reply
    1. Post author

      Hi Errol - As far as I'm aware embedding in other websites other than Google Sites still isn't possible. Within the code there is a variable called textOverview which you can customise with instructions. Unfortunately the rest of the interface is generated through code and isn't easy to customise without programming knowledge.

      Martin

      Reply
      1. Errol

        Hi Martin,

        Thanks for replying, the web site is actually a google site, when I try to embed the gadget, a message saying the document does not exist comes up. My error was thinking the bug referred to a personal google site. I've since found that embedding the gadget produces an incorrect web link, changing this for the correct one before embedding the gadget solves my issue.

        Anyway your app is brill, on to tweaking!!

        Errol

        Reply
  8. Hi Martin,
    I have designed a site using sites.google.com for our organisation's internal use. And users having id and password with the google apps user in our domain can only have access to this site (http://www.hopeindia.net).
    When-ever I make a change in the site then there is a mail notification to me being owner of the site. But I want that is something I am posting on a particular page (Announcement) of the site then a mail should be triggered to all the users of the domain (hopeindia.net).
    So please tell me how to do this.
    Thanks in advance.

    Reply
  9. Shaguna

    Hi Martin,

    I'm trying to insert a gadget, but getting the following error on my sites page:

    "
    Sorry, the page (or document) you have requested does not exist.
    Please check the address and try again.
    Want to create your own online docs?
    Google Docs makes it easy to create, store and share online documents, spreadsheets and presentations.
    Learn more at docs.google.com."

    I've followed all the steps mentioned above (including editing the spreadsheet id in code.) While adding a new app script, i tried choosing drive, spreadsheet, blank project. But all in vain. Not sure what am I doing wrong.

    Please help.

    Reply
  10. Trying to add your script for our company directory. I am getting an error:

    "Cannot convert NaN to . (line 455)"

    Line 455 is:

    var data = sheetOwner.getRange(1, column, sheetOwner.getLastRow(), 1).getValues();

    Please help!

    Reply
  11. Gomez

    hi there,

    I have followed all the steps but after I inserted the gadget as as Google Apps script for Google Sites, I am getting the following error:

    Sorry, the file you have requested does not exist.

    Please check the address and try again.

    Get stuff done with Google Drive

    Apps in Google Drive make it easy to create, store and share online documents, spreadsheets, presentations and more.

    Learn more at drive.google.com/start/apps.

    Reply
  12. Hi Martin, I tried the script and it seems to me there is a problem in editing records.
    After selecting a record and updated using the "edit" if I try to call the record subsequently stretched, the "Saved" is not active. You have to change something in the script? thank

    Reply
  13. Joe Sebastian

    Hi Martin,
    Great work.. I was able to deploy as a service but when i copy the code and try to add it to web site scripts, getID throws me an error "TypeError: Cannot call method "getId" of null. (line 39, file "Code")". Can you please help me on this?
    Thank you,
    Joe

    Reply

Leave a Reply