Google Spreadsheet

At ALT we are a Google Apps for Education user and make extensive use of Google Drive for creating and publishing documents. Some of our documents, such as member lists or timetables, get regularly updated and updating links in our websites can be a chore. One of the nice features of documents in Google Drive is you have a couple of ways of publishing documents so anyone can view (no log in required). For Google Sheets the main options are getting a shareable link:

share with others  ... because sharing is good ;)

Or using the ‘publish to the web’ option:

publish to web new school

In the ‘old’ version of Google Sheets the ‘Publish to the web’ option included lots of file types, not just as a web page:

publish-to-web old skool

You can still get new Google Sheets to generate download links for other formats … it’s just a bit complicated. The complicated bit is working out which url tweaks you need. For example, with your magic goggles on you might start spotting a pattern in this url which I’ve line breaked to make easier to see:

Remembering all these switches isn’t easy so for our team I’ve shared a simple template with lets us drop in a shared Google Sheet link, choose options for layout and get a url. Here’s a copy in case you find useful:

PDF Link for Google Sheet Template
PDF Link for Google Sheet Template


My old colleague at Cetis, David Sherlock, posted a nice little  ‘Twitter Question/Revision Bot’. This uses a .csv file of questions and multiple choice answers which get randomly tweeted out using a Python script. David designed the project with a Raspberry Pi in mind but also highlights it can be easily run on any Unix like environment such as Mac OS X or Linux. As not everyone is going to have easy access to this here’s how you can do something similar with Google Sheets (if you don’t want to play copy and paste coding make a copy of this sheet).

1. Setting up a Google Sheets environment to handle it

  1. Start with a new Google Sheet and like David have six columns in each row with question, answer, three options (one of which the correct one) and an extra row to record if the question has been asked.
  2. In your spreadsheet open Tools > Script editor and when asked start a ‘Blank Project’
  3. In the new editor window select Resources > Libraries (this will first prompt you to give your project a name, I called mine TwitBot.
  4. In the ‘Find a Library’ box enter MarIlVOhstkJA6QjPgCWAHIq9hSqx7jwh and click Select
  5. You should now see ‘TwtrService’ listed as one of the libraries. In the ‘Version’ dropdown select the latest version (at time of writing 14), and click Save

TwtrService is a library I’ve written so interact with the Twitter API. You can read more about it here.

In the code window add the following code and click save:

function setup() {
  if (TwtrService.isUserConnectedToTwitter()){
   var result = Browser.msgBox("Twitter Authorisation",
                   "You appear to already be connected to Twitter.\\n\\nWould you like to run the setup again?",
    // Process the user's response.
    if (result == 'yes') {
      // User clicked "Yes".
  } else {

The above code uses the TwtrService to help you set up Twitter access if required

Your script window should look like this:

script editor

2. Create a Twitter App

If you’ve used my other TAGS templates you can reuse your details for this. To see if Twitter App details are required from the script window select Run > setup (if setup isn’t listed you need to first save your code). Running setup will start the first part of the authentication process. Click continue and review the authentication required and ‘Accept’ if you are happy.

Auth required

Going back to the spreadsheet you started there should now be a dialog window asking you to do something. If you haven’t setup a Twitter App before it should look like this:

Twitter app creation

Follow the instructions onscreen to create your app.

Important: The Twitter account you use to authorise access is the one that will send out the tweets

3. Write a Python Google Apps Script

Add the code below to your existing script project and save:

var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheet = doc.getSheetByName('Sheet1');

function tweetQuestion(){
  var ran = Math.floor(Math.random() * (sheet.getLastRow()-1)) + 2;
  var row = sheet.getRange(ran, 1, 1, sheet.getLastColumn()).getValues()[0];
  if (row[5] !== ""){
    tweetQuestion(); // if already asked pulls another random row
  } else {
    var tweet =  "Q: " + row[0];
    var tweet2 =  row[2];
    var tweet3 =  row[3];
    var tweet4 =  row[4];'statuses/update', {status: tweet});
    var options = [tweet2,tweet3,tweet4];
    shuffle(options);'statuses/update', {status: "A: " + options[0]);'statuses/update', {status: "B: " + options[1]);'statuses/update', {status: "C: " + options[2]);
    sheet.getRange(ran, 6).setValue(new Date());

function shuffle(a,b,c,d){//array,placeholder,placeholder,placeholder

4. Time the script to run every hour or so

In the script editor select Resources > Current project’s triggers and click ‘No triggers set up. Click here to add one now.’. Select to run tweetQuestion every hour (or your preference), and also click ‘notification’ so you can get an email if the script fails. Finally click ‘Save’

Timed triggers

What will happen now is the function even if you don’t  have the spreadsheet or script editor open or even your browser.

Important: when this script runs out of questions it will go into an infinite loop. You can go back into the trigger window to remove the function at any point. If you don’t you’ll end up using all of your script runtime quota. You homework is to figure a way to get the script to bail if there are no questions left.

1 Comment

+Tom Smith kindly reminded me that there are existing Google Form Add-ons to achieve a similar result without having to mess with code. So you might want to have a look at Choice Eliminator by Bjorn Behrendt and formLimiter by Andrew Stillman

You can imagine the scenario, you’ve got a Google Form perhaps setup as a booking form and a select option for picking timeslots. As the slots fill up you’d like the option to be removed. I was surprised not to find an example of this so here is a rough sketch of some code that might do the job. We start with a basic form with a ‘choose from list’ option:

Select option

and in the linked Google Sheet we have an extra sheet to keep tally:


In this sheet I’ve got a column with quotas for each of my select options and a calculation for how many are left =B2-COUNTIF(responses!B:B,"="&A2)

To dynamically modify the Google Form we need to add some code. Here’s a basic snippet that reads our options and quotas and rewrites the select options (note the inline comments for bits you’ll need to edit):

function availableSlots(){
  var form = FormApp.openByUrl('URL_OF_YOUR_FORM'); // TODO add your form url
  // need to read what slots are available
  var slots = SpreadsheetApp
                .getValues(); // TODO make sure getRange matches your quota range
  var choice = [];
  // loop through our available slots
  for (s in slots){
    // test if slot still available
    if (slots[s][0] != "" && slots[s][2] > 0){ 
      choice.push(slots[s][0]); // if so we add to temp array
  var formItems = form.getItems(FormApp.ItemType.LIST); // our form list items
  // TODO assumption that first select list is the one you want to change  
  // change formItems[n] if you have more than one select list
  // and we just rewrite all the options to ones that are free

To enable this you need to use the Tools > Script editor in you Google Sheet and then Resources > Current project’s triggers. At this point to may be prompted for authorisation to read the data and change the form. Once that’s done add the availableSlots function to trigger on form submit.

Current project’s triggers

If you’d like to shortcut all but this last step make a copy of this template.


Some things to be aware of. If you have concurrent users submitting the form someone may sneak in. Apps Script has ways of preventing this but not as far as I’m aware for Google Forms. It’s worth remembering that do don’t need to use Google Forms at all for getting data into a Google Sheet and if you can host your own HTML form you can make POST/GET requests. If you develop this example further I’d be interested to hear about it. Enjoy!

1 Comment

In the Apps Script G+ Community Scott Marquardt asked:

Youtube API question.

Argh, I can't find a single example of anything like this. With my current knowledge, I have a hard time inferring from the API reference documents alone, to Apps Script.

My goal is a script to add an item to a Youtube playlist.  My intended workflow is a Google form to enter video IDs into a Google sheet, with a triggered script adding each new video ID to a hard-coded playlist.

I'm unashamedly panhandling here, on behalf of what would be a really valuable use case in a dozen of our special education schools.

I agree with Scott that the Apps Script Advanced Services documents (here’s YouTube) don’t give you much to go on. The solution is actually relatively simple once you get your head around the shape of the data required. So here is a form linked to this playlist (I’ll turn this off as soon as it starts  getting spammed). In the spreadsheet receiving responses the Tools > Script editor has the following code (in the script you’ll see where you need to hard code the playlist you want to add videos to):

Update: Scott asked about adding notes/comments to playlist items and I've updated the code to do this to the gist

To get this working there are a couple of hoops to jump through. As the YouTube API is an Advanced Service and it must be enabled before use. For this project to do this you need to be in the Script Editor then:

  1. Click Resources > Advanced Google Services…
  2. Scroll down to YouTube Data API to turn it on then click the ‘Google Developers Console link:
    Enabling Advanced Services
  3. In the Google Developers Console find and turn on the YouTube API. After it’s enabled you can close the Console window

You can only interact with YouTube Channels the account authorised to run the script is the owner of (see related post by me on this).

The final step is to setup the script to trigger when the form is submitted. To do this while still in the Script Editor  select Resources > Current project’s triggers and add a run addVideoToPlaylist event on form submit.

Current project triggers

If you would prefer to not do all the code copy and pasting you can File > Make a copy of this sheet (you’ll still need to enable Advanced Services and the form submit.

There’s obviously lots more you could do with this script like putting submissions into a moderation queue but I’ll let you go and play with those ideas.

You’ll find lots of blog posts explaining how to do this the other way, converting a table from Wikipedia into a spreadsheet. I even cover this myself in Feeding Google Spreadsheets: Exercises in using importHTML. This post looks at taking a .csv or a spreadsheet and formatting it as a Wikipedia table.

I’ve been on a bit of a Wikipedia journey expertly guided by a friend, Simon Knight (@sjgknight) … who happens to be Wikimedia UK Vice Chair. The journey has included Wiki ShootMe which has been covered by Alan Levine (@cogdog) and a wonderful Teaching Wikipedia Editing session you can still watch from Connected Courses.

One of the great things about Wikipedia is there is no shortage of information on how to do stuff. Before jumping in I had a quick look for some existing tools and came across this article on Importing (converting) content to Wikipedia (MediaWiki) format. Listed is the Spreadsheet-to-MediaWiki-table-Converter. This is some code you would more normally run on a server possibly used as a library within another project. Wanting something simpler I looked for and found guidance on creating a sortable table. The guidance helpfully includes an example copied below:

{| class="wikitable sortable"
! name
! data
! more data
| cats
| 273
| 53
| dogs
| 65
| 8,492
| mice
| 1,649
| 548

And here is what it looks like:

namedatamore data

Hopefully you can quickly see the pattern, you start with the column headings, one per line of wikicode and then each of the rows again one value per row for each cell. This type of pattern is easy to code … if you can code … … if you want to code. As I was composing my first line I remembered an old project Templated Export for Google Spreadsheets:

Templated Export allows users to reshape and selectively publish data from Google Spreadsheets in custom formats. The tool is powered by Google App Script which allows integration with Google Spreadsheets using existing authenticated access. Example uses include: turning data from a spreadsheet into a RSS feed and publishing data from a spreadsheet in JSON format to be used by other tools/services. The framework for creating a custom wrapper for your data means there are many more opportunities.

Templated Export lets you customise use a basic reference system to create you own output pattern:

Templated Export

There is some learning curve using this and original post has details on setting this up and using it. The basic concept is you use a cell reference as a way to build up your pattern. You can save your templates which means if you update your data in your Google Sheet you can open the Templated Export interface and download it again or use a saved ‘published url’.

Templated Export hasn’t been touched for two years and is perhaps a candidate for some ‘modern’ Google Apps Script techniques such as publishing as an Add-on. If the thought of playing with Google Apps Script doesn’t take you fancy, Templated Export is based on a feature of OpenRefine called Templating Exporter. OpenRefine can be downloaded and run from your desktop and can handle a variety of data file formats.

So go forth and fill Wikipedia with lovely tables for everyone else to reference in ‘Wikipedia table export’ posts ;)

1 Comment

I previously posted about TwtrService: A Twitter API client library for Google Apps Script which makes it easy to interact with Twitter from Google Drive applications like Google Sheets. One of the nice things about TwtrService is that once you setup a connection to Twitter you can use it many times in different projects, basically allowing you to do stuff in one line of code. In the post I said I’d share some of the examples of things I make so here is the first one, EasyTweetSheet.

What it does

At the Association for Learning Technology we organise lots of events. We only have a small staff team so having someone sending out tweets during the event can be a problem. We could use a Twitter client like Tweetdeck or Hootsuite  to schedule tweets during the day. One issue is if something goes wrong like the livestream not working or a session starting late you can look a bit silly. The solution was to draft our tweets in a Google Sheet and have a link we click when we want the message to be sent. Below is a screenshot for the one we used at this year’s ALT Annual Conference:

EasyTweetSheet used at #altc 2014

How to get you own copy working

  1. Open this copy of the EasyTweetSheet templateand File > Make a copy
  2. In your copy open Tools > Script editor and follow the instructions
  3. Start filling the ‘text’ column with what you want to tweet which should enable the ‘tweet’ link

IMPORANT: If you’ve used my other tools like TAGS this template will use the Twitter account you used to set it up. To use a different Twitter account to send the tweets from
replace YOUR_CONSUMER_KEY and YOUR_CONSUMER_SECRET in lines 34-35 of the Script editor code with your Twitter application key/secret. When you Run > setup switch back to the Sheet view and follow the instructions.



As part of the latest release of TAGS (Twitter Archiving Google Sheet) I moved a lot of the code into a Google Apps Script Library. Libraries are a great way to bundle and release code allowing you to write your own classes and methods. To see some of the functionality already being developed by the Apps Script community you should have a look at the Google Apps Script Samples site and the excellent work Bruce McPherson has done which includes a EzyOauth2 library.

One of the things you can do with libraries is wrap one library into another. When rewriting TAGS it made sense to strip out a separate Twitter client library that I and others could use in different projects. Based on the work by Arun Nagarajan at Google, TwtrService provides  access to Twitter's REST API. The work I’ve done is to add some UI and configuration methods to try to streamline the authentication flow. As part of this developers can offer authentication routes using their own Twitter application or use an application created by users. This particular angle is a result of one of the design principles for TAGS, that every copy of the template should use a Twitter application owned by the user. The reason behind this is to distribute the risk. If Twitter were to suspend my data access because a TAGS user abused their API it would suspend access for all TAGS users. By requiring TAGS users to register their own application with Twitter the responsibility to abide by Twitter’s terms of service lies with them. So in TAGS the auth flow looks like this

The result is hopefully a flexible library that developers can integrate into their own projects or by getting users to register their own.

Over the next couple of weeks I'll be sharing some examples applications we've developed at ALT. In the meantime this post serves as a basic introduction to TwtrService and covers:

Overview of TwtrService

The TwtrService library for Google Apps Script centrally stores your Twitter access details allowing them to accessed from multiple script projects without the need for re-authentication. TwtrService is designed to allow you to directly use the Twitter’s v1.1 REST API GET and POST methods. For example to return Twitter search results for the search ‘Google Apps Script’ you would use:

var data = TwtrService.get('', {q: 'Google Apps Script'});

The url string can also be abbreviated to:

var data = TwtrService.get('search/tweets', {q: 'Google Apps Script'});

Additional options can be passed in the parameters array. For example to return 100 results for the same search you would use:

var data = TwtrService.get('search/tweets', {q: 'Google Apps Script', count: 100});

The project key for this library is MarIlVOhstkJA6QjPgCWAHIq9hSqx7jwh and the TwtrService methods are documented here.

To use the Twitter REST methods TwtrService first needs authenticated access. TwtrService has some built-in methods to do this detailed below. Once a user has authenticated access the TwtrService library stores these as User Properties. This means when a user has authenticated once with TwtrService using the library in another container-bound or standalone Apps Script immediately gives them access to Twitter API results using the get/post methods. In terms of security User Properties are limited to the effective user of the current script.


Quick start: Personal access

If you would just like to use TwtrService for your Apps Script projects the easiest way to get started is to register a Twitter application and enter it’s details on this page (if you are interested here is the source code for the interface).

Note: If you are already a user of TAGS you’ll already be able to use TwtrService without the step above.

In your Apps Script project you’d like to use the Twitter API in the Script Editor window use Resources > Libraries and add the service  using the project key MarIlVOhstkJA6QjPgCWAHIq9hSqx7jwh.

In your project you can now use the TwtrService.get() and methods. The documentation for get() is detailed below (post is the same but uses HTTP POST):

get(string url, Object parameters)

GET wrapper for request to the Twitter REST API. For full documentation of API method endpoints and parameters see For example to get last 100 tweets containing 'Google Apps Script': var data = TwtrService.get('search/tweets', {q: 'Google Apps Script', count: 100});

urlstringTwitter REST API resource url. This can be either long form e.g. or abbreviated e.g. search/tweets
parametersObjectadditional API parameters as detailed in the Twitter REST API documentation e.g. for search results a search string and count is specified by {q: 'Google Apps Script', count: 100}.
Return Values:
ObjectAPI response in JSON format.

Quick start: Personal access in Sheets and Documents

If you would like to replicate the TAGS authentication flow where users enter their Twitter application key/secret TwtrService comes with a number of UI methods. For TAGS the following code is used:

* Launches key/secret and auth flow
function setup() {
  if (TwtrService.isUserConnectedToTwitter()){
   var result = Browser.msgBox("Twitter Authorisation", 
                   "You appear to already be connected to Twitter.\\n\\nWould you like to run the setup again?", 
    // Process the user's response.
    if (result == 'yes') {
      // User clicked "Yes".
  } else {

* Used as part of setup() to process form data
function processForm(formObject) {

Quick Start: Shared Twitter Key/Secret

The earlier examples have assumed the user registers their own Twitter application. For scenarios where you would like to have the option for users to have authenticated access using a dedicated Twitter API key/secret it is possible to initialize these values. An example application code can be found here which is also deployed here.

Similar to earlier examples once a user authenticates access with your key/secret as long as these values are also initialized in other script projects the user will have access to the Twitter API via TwtrService.

Instructions for creating a Twitter Application to use with TwtrService

TwtrService requires you to have a registered Twitter application. If you are If you haven’t already done this here are some steps you need to get started:

  1. Register for an API key with Twitter at (if you've already registered for a TAGS sheet you can reuse your existing API Key and Secret).
    • Name, description and website can be anything you like
    • Important Include the Callback URL
  2. Read the 'Developer Rules of the Road' before clicking 'Create your Twitter application'

On the Twitter site your application should include a ‘Keys and Access Tokens’ tab which includes the Consumer Key (API Key) and Consumer Secret (API Secret) you’ll need.



Amazing to think TAGS has been going for over 4 years now. Version 6 is a major code rewrite and the most important new feature is a better setup processes. No more digging into the script editor to run functions, no more entering your Twitter API key and secret each time you create a new archive. Both these things are history thanks to some base code released by Arun Nagarajan at Google. Now you enter/register for a Twitter API key and secret once and each copy of TAGS you make will remember these. This is made possible by incorporating this functionality as a custom Apps Script library I’ve called TwtrService. TwtrService makes it easy to make calls to all of Twitter’s API and I’ll be explaining how it works in another post.

Version 6 comes with some other new features. The one that was most requested was archiving favourited tweets. There was a quick hack version of TAGS that did this but was limited to the last 200. Now when you setup a favourite archive you can get up to the last 3,000 favourited tweets. Another option with TAGS v6.0 is to use Google’s new version of Sheets. This gives TAGS more capacity and performance. One issue however with new Sheets is it isn’t very stable with the Google Visualisation API which is used in TAGSExplorer.

With TAGS v6.0 I’ve also created a dedicated support site. So if you have any questions or need help head over to where you can also get the latest version of TAGS.

1 Comment

Thanks to a recommendation by Josef Šlerka (@josefslerka) I got a chance to speak at WebExpo 14 (#webexpo) about some of my work using Google Sheets for Twitter data mining and analysis. I’m always get uneasy at speaking at events for web professionals/developers about spreadsheets as … it is spreadsheets.

Dangers aside, particularly ‘spreadsheet addiction’, hopefully I was able to covey  the possibilities of using Google Apps Script turbocharged Google Sheets. To do this I focused on some of the work I’ve been doing interacting with the Twitter search API. I’m sure many people are familiar with the Twitter Archiving Google Sheet (TAGS) but perhaps not aware of some of the experiments I’ve used it for. Some of these include:

(I was surprised myself to discover the whole project started in 2010)

After my talk it was great that some fellow educators came to speak to me interested in using Google Apps Script for social data mining as part of their curriculum. Slides for my talk and abstract are at the end of this post and the link bundle is here and the #webexpo TAGS archive is here.

Thanks also to my fellow GDE Ivan Kutil for capturing a picture of me dwarfed by one of my own sheets.




There is growing interest in the use of data to provide actionable insight. This interest goes beyond the professional analysts and just as fields such as mathematics and astronomy have benefited from the enthusiastic amateur so does data science. Social networks are a rich playground of data and whilst many provide access to their data via APIs but access via this route can be daunting. You can of course turn to 'analytics as a service' sites which will take your credentials and provide you with some answers, but often this can be what they want to tell you and not what you want to hear. A solution is the spreadsheet. Spreadsheets provide an interface for data exploration for those with basic skills. With Google Sheets the opportunities increase exponentially, not just in terms of collaboration, but also with the power of Google Apps Script. Apps Script provides easy integration into other Google products and services, such as Google Analytics, as well as third party APIs like Twitter. In this presentation we show how Google Sheets can become a rich playground where data from different services can be collected and analysed.

1 Comment

Back in 2011 I showed how you can use Google Apps Script to write POST/GET data to a Google Sheet. Over the years a couple of things have changed in Apps Script so I thought it was worth a revisit.  The main changes are:

The core concept behind the script is the same. You have a Google Sheet with a set of header column names that matches the names of the data you are passing through. For example if I had a form with:

<input name="bar" type="text" value="" />

I'd need a sheet with the column name 'bar'. For this post I’m going to assume we use a container bound Apps Script in a Google Sheet, but you could easily modify this for a standalone script. So to start you can either create or open an existing Sheet and click Tools > Script editor and enter the code below or copy this template.


There are a couple of ways you can use this script to collect data. You could use a very traditional HTML form using the web app url as the action parameter. This would send users to a very unattractive JSON response which you could alternatively beautify using the HTMLService. A nicer solution is to use AJAX to submit the data without refreshing or moving page. Below is a simple form based on this Stackoverflow jQuery Ajax POST example which sends responses to this Google Sheet (if you are reading this via RSS/Email you need to visit this post):

The only real change to the stackoverflow example is to specify the destination web app url:

// fire off the request to /form.php
		request = $.ajax({
			url: "",
			type: "post",
			data: serializedData

The example is using POST but you can also use GET. There is more you can do when handling the data at the Apps Script end other than writing to a Google Sheet. For example, if you wanted to send an email on each submission you could use the MailApp service and add something like:

MailApp.sendEmail("youremailaddress", "a subject", JSON.stringify(e.parameters));

in the try statement. If you do this there are a couple of things to remember. First Apps Script web apps using versioning. This means changes to your script are not 'live' until you push a new version. To do this you need to save your new script and then from the Script Editor select File > Manage versions... and 'Save New Version' before going into Publish > Deploy as web app and updating Project Version. Also when you add new services to your script the authentication scope changes and you need to approve additional services. For example, if you add the MailApp service to your code you need to give permission to send email. The easiest way to trigger this in this example is in the Script Editor Run > setup. I'm sure there are other trip ups but hopefully this gets you most of the way

Google Sheet/Apps Script Code

//  1. Enter sheet name where data is to be written below
        var SHEET_NAME = "Sheet1";
//  2. Run > setup
//  3. Publish > Deploy as web app 
//    - enter Project Version name and click 'Save New Version' 
//    - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously) 
//  4. Copy the 'Current web app URL' and post this in your form/script action 
//  5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e){
  return handleResponse(e);

function doPost(e){
  return handleResponse(e);

function handleResponse(e) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // [1]
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // wait 30 seconds before conceding defeat.
  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);
    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = []; 
    // loop through the header columns
    for (i in headers){
      if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
        row.push(new Date());
      } else { // else use header name to get data
    // more efficient to set values as [][] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
  } finally { //release lock

function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());