Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Google App Script to insert data to a google spreadsheet via POST or GET - updated version as per https://mashe.hawksey.info/2014/07/google-sheets-as-a-database-insert-with-apps-script-using-postget-methods-with-ajax-example/
/*
Copyright 2011 Martin Hawksey
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/
// Usage
// 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] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
// 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
row.push(e.parameter[headers[i]]);
}
}
// 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}))
.setMimeType(ContentService.MimeType.JSON);
} catch(e){
// if error return this
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally { //release lock
lock.releaseLock();
}
}
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}

Thanks! Works like a charm

eduser commented Jun 16, 2014

Great job. Thanks.

I get some errors: in the setup function(2nd time run): TypeError: unable to call "getId" of null. (line 53) (i'm translating from italian) and in the doget: argument not valid: id (line 27)

I think this is because of a change in the Sheets API that it doesn't report the active spreadsheet anymore from a standalone script. Just delete the setUp function, and replace ScriptProperties.getProperty('active') in line 27 by the id of your spreadsheet and it should work fine.

Can anybody help with this error:

Cannot read property "parameter" from undefined. (line 37)

-thanks

petewnr commented Apr 23, 2015

Got it working as per burgerga

Get rid of the setup function and replace line 27 with:

var ss = SpreadsheetApp.openById("your_sheet_ID");

Then should work.

jromeem commented May 17, 2015

still works following the edits from @burgerga and @petewnr
thanks for this! 👍

Great little script - got it working too.

Re the Cannot read property "parameter" from undefined. (line 37) error - this only seems to happen when running the app in the script console. Try creating a HTML form and posting the data from the form. Adding the inline params into the URL doesn't seem to work - assuming it's rewritten/redirected. happy to share my code if it would be of use to anyone.

It seems to be common to run into a "Authorisation" errors too - if you encounter this, republish your web app and bump the version.

Just notes from my debugging 👍

just for my own knowledge, on line 62, if I use a value other than "Timestamp" it doesn't work. For example, if I create a header named "Date" and substitute "Date" with "Timestamp" on line 62. It does not work anymore. Why is this?

When I attempt to use the form on a PHP page, using the Ajax connection, I'm getting an console error reading "No 'Access-Control-Allow-Origin' header is present on the requested resource."

Do I need to set up Google API on my web page first?

Im a novice. Heres what I want to do. I have a contact form on my website and a google sheet I use. How can I have users inserted into the google sheet using the contact form? Any help would be appreciated. Thanks!

i'm getting this error. not sure why

{"result":"error","error":{"message":""SHEET_NAME" is not defined.","name":"ReferenceError","fileName":"Code (doPost)","lineNumber":20,"stack":"\tat Code (doPost):20 (handleResponse)\n\tat Code (doPost):6 (doPost)\n"}}

Thanks for this bit of code, really helped me out. I needed to make a revision to get it working (you can see that here if you want to make an update: https://gist.github.com/coreyphillips/f27025c86b838cc5372c/revisions), but works like a charm otherwise.

Hi. I placed my form in Bootstrap modal, and Submit button just reload my page without sending data.
But when form is placed directly to page - it works fine.
Any ideas?
Thanks.

souparno commented Apr 7, 2016

awsome work (y)

souparno commented Apr 8, 2016

the headRow variable at line 56 is left unused

wannaco commented Apr 24, 2016

Awesome !!!!!

Any thoughts on how to dynamically add a new column in the destination sheet if one of the web form parameters sent to the sheet does not already exist as a column name?

Hi All, This has worked great for me in adding new records, but can someone point me in the right direction on how to update an existing row based on a unique field or column entry?

Hi there,
While using this script, everything works fine(No error and logs) but the email field in the spreadsheet is not getting rightly filled and comes undefined. I am not able to solve the error. PFA the image. Its the exact same script.

undefined

I see that your code only works with spreadsheet with one sheet, is there any way to work with spreadsheet has many sheets?

When you say in line 4 under usage- "post this in your form/script action"

Do we need to create a new form from the spreadsheet and alter the url?

Please clarify- thanks for the code.

Hey Can i write google calendar API interaction in app script? Or how i can send my data from client JavaScript file. I want to auto update a spreadsheet with fetching data from google calendar and want to update row of spreadsheet.

Thanks in advance

Hey, i have made a file for insert, update, delete and retrive, with similar code. Please check https://github.com/AishwaryT/Google-app-script-crud

i just want to check post request, as per the request i want to save my datas in sheet1 or sheet2 how to alter the sheet names from above script..

@dassram17 i have not tried your requirement.
but you can try to send the sheetname as a parameter of your form.
then access it as e.parameter.sheet_name

good luck.

@imnotberg
no.
just write the form tag in your own html.
in the action - write the url of the webapp from => Publish -> Deploy as a web app
example -

action is your webapp url.
cheers.

ErNavi commented Jul 24, 2017

Does it still works?

yugn27 commented Aug 6, 2017 edited

I don't think so @ ErNavi

working like charm...thank you so much,,,,my first google app is taking shape,...

Thank you for the amazing script. Did not work out of the box, but worked with some minor tweaks.

function doGet(e){
  return handleResponse(e);
}
 
function doPost(e){
  return handleResponse(e);
}
 
function handleResponse(e) {
  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 ss = SpreadsheetApp.getActiveSpreadsheet();
     var sheet = ss.getSheets()[0];     
    // 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(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MMM d yyyy HH:mm:ss"));
      } else { // else use header name to get data
        row.push(e.parameter[headers[i]]);
      }
    }
    // 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}))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
          .setMimeType(ContentService.MimeType.JSON);
  } finally { //release lock
    lock.releaseLock();
  }
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment