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.
- Grab a copy of the template spreadsheet
- Populate it with your staff information
- 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
- 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
- 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
- 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)
- Check ‘Enable Service’ and copy the URL
- Click Save.
Embed as a gadget in Google Sites
- From the Script editor in Google Spreadsheet copy all of the code
- Open your Google Site and navigate to the Manage Site page
- Select App Script then ‘Add new script’ pasting the code from the spreadsheet script editor
- Save then Run > doGet to authorize the script
- Open the page you would like to add the gadget to and enter edit mode.
- Move the cursor to the point in the page where you would like to insert the gadget then Insert > Apps Script Gadget
- Various setting for you to play with (the minimum dimensions are 275x632px)
- 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.