​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 Script...in 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('http://www.google.com/movies?hl=en&near='+
             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