Publish your scripts to the Apps Script Gallery

Wednesday, March 10, 2010 | 11:20 AM

Today, we are excited to make Google Apps Script available to everyone. Some of you may already be familiar with Google Apps Script within Google Apps, but in case you are new to it, Google Apps Script provides a powerful and flexible scripting environment that lets you automate actions across your spreadsheets, sites, calendars, and many other services.

An important new feature of Apps Script is a script gallery, where developers can easily publish their scripts to make them accessible to everyone. You can find the gallery by going to Insert and then selecting any Google spreadsheet.

Recently, the Google Apps team in New York put together a Movie Night script to help us easily figure out which movies were playing nearby and vote for our favorites - you can read more about it here.  Let’s take a closer look at how the script works and how we published it to the new Apps Script Gallery.

We start by bringing up the script editor from a spreadsheet (Tools -> Scripts -> Script editor...).

The first step is to fetch a list of movies playing in a given area at a given time.  We use the Google search results for a movie query as follows:

varresults = UrlFetchApp.fetch(''+
             zipcode +'&dq=movies&sort=1').getContentText();
vardoc = Xml.parse(results,true);

We can then use Apps Script’s handy Xml service to parse the results.  The next step is to send an email to our friends asking them to vote.  This is the slightly tricky part:

  1. In the spreadsheet, select Form -> Create a form to open the form creation window.
  2. Add two questions, one titled “Movie”, and the other “Attendee” - we don’t care too much about any other text as it will all be replaced by the script at run-time.
  3. Close the form creation window.
  4. In the spreadsheet, select Form -> Go to live form, and copy the ‘formkey’ parameter from the address bar.
  5. Open the script in the editor, and insert the ‘formkey’ into line 2 of the script.

Phew!  That was the tricky part.  In summary, we just created a form for the spreadsheet, but instead of using that form directly, we’re going to use a form that is dynamically generated by the script.  However, we still need the special key to correctly route submissions to the spreadsheet (and to validate those submissions).

After that, we can put together a list of recipients by calling the contacts service and reading the Gmail ‘Friends’ group:

varcontactGroup = ContactsApp.findContactGroup(’System Group: Friends’);
varpeople = contactGroup.getContacts();
Lastly, we put the movie thumbnails and descriptions in the email body - tailored to each recipient, so that we’ll know who voted:

varemailBody = "";
for(variinmovies) {
 // add the image, title etc to emailBody (as HTML)
varaddresses = getFriends_();
for(variinaddresses) {
 var email = email_header + form_key + emailBody + email_footer;
 MailApp.sendEmail(addresses[i],"Movie tonight?", "", {htmlBody: email});

Check out the documentation and get started building your scripts - we look forward to seeing your gallery submissions.  To share your masterpiece with the world, select Share -> Publish Script...from the script editor - it's that easy!

Also, for those attending Google I/O this year, be sure check out the Google Apps Script talk on the Enterprise track.

Posted by Nikhil Singhal, Google Apps Script Engineer


tazz_ben said...

Is persistant storage of any form in this release? This is huge first step, but without storage you can have settings which is necessary when you are distributing to the public.

Beben said...

brand new anymore apps...
wow cool

tanoan said...

I am a little confused, hopefully not doefully.

line1: var results = UrlFetchApp.fetch(''+
zipcode +'&dq=movies&sort=1').getContentText();
newline2: someformkeyhere
line2: var doc = Xml.parse(results,true);

Is this the correct interpretation?

John Bokma said...

It looks like the syntax highlighting code has eaten the spaces.

victor said...

very cool

S. Goldstein said...

There is a problem with the "search field" when trying to search for scripts. This issue happens on Chrome and Firefox and on other computers that I've tried in my office. Someone should certainly look into this.

Saurabh Gupta said...

S. Goldstein - Sorry for the inconvenience. We are aware of this problem and will get it fixed shortly. If you have any questions, please do not hesitate to post them on this thread.