Create a gist now

Instantly share code, notes, and snippets.

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());
}
@MichaelFBA

Thanks! Works like a charm

@eduser
eduser commented Jun 16, 2014

Great job. Thanks.

@beppe9000

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)

@burgerga

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.

@mschachtel1

Can anybody help with this error:

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

-thanks

@petewnr
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
jromeem commented May 17, 2015

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

@robhadfield

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 👍

@d3netxer

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?

@cotelouis

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?

@jeremiaholsen

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!

@cdholjes

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"}}

@coreyphillips

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.

@Illya-Anyol

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
souparno commented Apr 7, 2016

awsome work (y)

@souparno
souparno commented Apr 8, 2016

the headRow variable at line 56 is left unused

@wannaco
wannaco commented Apr 24, 2016

Awesome !!!!!

@alexmelman

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?

@followgeo

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?

@shubhambhartiya

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

@dracarys1312

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

@imnotberg

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.

@surendrakumar1992

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

@AishwaryT

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

@dassram17

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..

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment