Instantly share code, notes, and snippets.

Embed
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. Run the setup function (you'll need to do this twice - 1st time to grant acces to Script Properties)
2. Share > Publish as service ... set security level and enable service
3. Copy the service URL and post this in your form/script action
4. Insert column names on the DATA sheet matching the parameter names of the data you are passing
*/
function doGet(e) { // change to doPost(e) if you are recieving POST data
var ss = SpreadsheetApp.openById(ScriptProperties.getProperty('active'));
var sheet = ss.getSheetByName("DATA");
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; //read headers
var nextRow = sheet.getLastRow(); // get next row
var cell = sheet.getRange('a1');
var col = 0;
for (i in headers){ // loop through the headers and if a parameter name matches the header name insert the value
if (headers[i] == "Timestamp"){
val = new Date();
} else {
val = e.parameter[headers[i]];
}
cell.offset(nextRow, col).setValue(val);
col++;
}
//http://www.google.com/support/forum/p/apps-script/thread?tid=04d9d3d4922b8bfb&hl=en
var app = UiApp.createApplication(); // included this part for debugging so you can see what data is coming in
var panel = app.createVerticalPanel();
for( p in e.parameters){
panel.add(app.createLabel(p +" "+e.parameters[p]));
}
app.add(panel);
return app;
}
//http://www.google.sc/support/forum/p/apps-script/thread?tid=345591f349a25cb4&hl=en
function setUp() {
ScriptProperties.setProperty('active', SpreadsheetApp.getActiveSpreadsheet().getId());
}
@MichaelFBA

This comment has been minimized.

MichaelFBA commented Nov 8, 2013

Thanks! Works like a charm

@eduser

This comment has been minimized.

eduser commented Jun 16, 2014

Great job. Thanks.

@beppe9000

This comment has been minimized.

beppe9000 commented Jun 23, 2014

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

This comment has been minimized.

burgerga commented Jun 26, 2014

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

This comment has been minimized.

mschachtel1 commented Jul 24, 2014

Can anybody help with this error:

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

-thanks

@weisjohn

This comment has been minimized.

weisjohn commented Mar 27, 2015

@petewnr

This comment has been minimized.

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

This comment has been minimized.

jromeem commented May 17, 2015

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

@robhadfield

This comment has been minimized.

robhadfield commented Oct 21, 2015

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

This comment has been minimized.

d3netxer commented Nov 23, 2015

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

This comment has been minimized.

cotelouis commented Nov 24, 2015

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

This comment has been minimized.

jeremiaholsen commented Dec 17, 2015

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

This comment has been minimized.

cdholjes commented Dec 29, 2015

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

This comment has been minimized.

coreyphillips commented Jan 19, 2016

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

This comment has been minimized.

Illya-Anyol commented Feb 18, 2016

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

This comment has been minimized.

souparno commented Apr 7, 2016

awsome work (y)

@souparno

This comment has been minimized.

souparno commented Apr 8, 2016

the headRow variable at line 56 is left unused

@souparno

This comment has been minimized.

souparno commented Apr 13, 2016

@wannaco

This comment has been minimized.

wannaco commented Apr 24, 2016

Awesome !!!!!

@alexmelman

This comment has been minimized.

alexmelman commented May 7, 2016

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

This comment has been minimized.

followgeo commented May 19, 2016

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

This comment has been minimized.

shubhambhartiya commented Jul 13, 2016

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

This comment has been minimized.

dracarys1312 commented Jul 22, 2016

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

@imnotberg

This comment has been minimized.

imnotberg commented Jul 28, 2016

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.

@myexpertiseforyou

This comment has been minimized.

myexpertiseforyou commented Aug 2, 2016

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

This comment has been minimized.

AishwaryT commented Oct 20, 2016

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

This comment has been minimized.

dassram17 commented Jan 5, 2017

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

@karan-ta

This comment has been minimized.

karan-ta commented Apr 13, 2017

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

@karan-ta

This comment has been minimized.

karan-ta commented Apr 13, 2017

@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

This comment has been minimized.

ErNavi commented Jul 24, 2017

Does it still works?

@kingslyroche

This comment has been minimized.

kingslyroche commented Oct 2, 2017

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

@aravindk1992

This comment has been minimized.

aravindk1992 commented Oct 7, 2017

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

This comment has been minimized.

adrianoresende commented Oct 18, 2017

I get error "Failed to load https://script.google.com/macros/s/.../exec: Response to preflight request doesn't pass access control check: No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'https://bar.com' is therefore not allowed access. The response had HTTP status code 405."

How I fix this?

@dezostus

This comment has been minimized.

dezostus commented Jan 5, 2018

@aravindk1992 thanks for the code! it works

@eli-oat

This comment has been minimized.

eli-oat commented Jan 25, 2018

@aravindk1992, would you mind giving an example of how to define to what spreadsheet your code writes?

    // next set where we write the data - you could write to multiple/alternate destinations
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var sheet = ss.getSheets()[0];     
@followgeo

This comment has been minimized.

followgeo commented Feb 1, 2018

Has anyone been able to modify this code to update a specific row on a spreadsheet?

@hamx0r

This comment has been minimized.

hamx0r commented Feb 12, 2018

@followgeo The intent of this Gist is to add successive data to a sheet, as from an input form. The full Google Sheets API](https://developers.google.com/sheets/api/guides/values) is a good tool to use for updating a specific row.

@hamx0r

This comment has been minimized.

hamx0r commented Feb 12, 2018

@mhawksey as @souprano noted, headRow is unused yet should be used as the 1st arg of the very next line:
var headers = sheet.getRange(headRow, 1, 1, sheet.getLastColumn()).getValues()[0];
I've made this change in my fork of your Gist.
https://gist.github.com/hamx0r/b851531d8546565c23deab926ee6867e

@shubhambhartiya I had the same problem because of 2 reasons:

  1. I had a typo in my client code for the header name (ie your sheet column is emaild but maybe your client is using a parameter email)
  2. I had more columns than I had fields in my HTTP request (ie I had 10 columns in my sheet, but only posted 7 key:value pairs, so 3 columns had undefined every time a new row was added)
@jjgh

This comment has been minimized.

jjgh commented Feb 15, 2018

Is there a way to fix the CORS error when doing a POST request?

@Prag1396

This comment has been minimized.

Prag1396 commented Feb 18, 2018

how does this code work it does not even have the url for the spreadsheet to write to?

@rocobiz

This comment has been minimized.

rocobiz commented Mar 16, 2018

How do you have someone that does not have a gmail account insert a record into the sheet when they complete form?

@shaungt1

This comment has been minimized.

shaungt1 commented Mar 23, 2018

How could I use this to get All my data from Firebase? I have a database that has like 5 text fields, maps data, and a few images?? I would like to get this to a spreadsheet on Google sheets, I have tried other techniques that fail do to the limitations of the Data objects and Rows/Col

@neeksor

This comment has been minimized.

neeksor commented Apr 6, 2018

Prag1396 - line 52
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app

@ownindra

This comment has been minimized.

ownindra commented Aug 18, 2018

function doGet(e) {
var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1NMXd3bsq7pu2v3y3g6EdJ2g5yDmaRfOOpXAFGKZjn5E/edit#gid=0");
var sheet = ss.getSheetByName("Sheet1");

addUser(e,sheet);
}

function doPost(e) {
var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1NMXd3bsq7pu2v3y3g6EdJ2g5yDmaRfOOpXAFGKZjn5E/edit#gid=0");
var sheet = ss.getSheetByName("Sheet1");

addUser(e,sheet);
}

function addUser(e,sheet) {
var id = e.parameter.id ;
var name = e.parameter.name ;
var gender = e.parameter.gender ;
var kota = e.parameter.kota

sheet.appendRow([id,name,gender,kota]);
}

how to send data to the getRange spreadsheet ('C2'), in google script?

capture
1capture

@ownindra

This comment has been minimized.

ownindra commented Aug 18, 2018

how to send data to the Range spreadsheet in ('C2'), in google script?

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