Google Spreadsheets as a Database – INSERT with Apps Script form POST/GET submit method

Update 04/07/2014: I’ve revisited this code and made a number of improvements. Read more in Google Sheets as a Database – INSERT with Apps Script using POST/GET methods (with ajax example)

Update: See comment by James to streamline this even more.

I was recently rediscovering an old Hirst post on Google Spreadsheets as a Database in which he demos an “interface for constructing URIs to query Google spreadsheets using the Google query language” . I found myself at this post after researching a ‘cunning idea’ I’ve in development and also because Google Search ‘probably’ knows best.

Anyway this post is unrelated to the ‘cunning idea’ but I got thinking one of the aspects of databases is getting data in. I tackled this when I was at Dev8D back in February with the post Collecting any data in a Google Spreadsheet using GET or POST. At the time I was planning a follow-up post to show a practical application but never got around to it.

So here is a more refined piece of code which actually records data submitted from your own form or custom code:

   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
   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   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);
  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]));
  return app;
function setUp() {
  ScriptProperties.setProperty('active', SpreadsheetApp.getActiveSpreadsheet().getId());

There are a couple of ways you can submit data. You can use a basic HTML form (this example shows how you can avoid redirecting to the Google macro/service page using a hidden iframe (I tried using ajax to submit the data without refresh but you run into cross domain/XHTTP security issues. I imagine you could get around this by having a intermediary PHP page that passes the data as POST or GET. If you are going down either of these solutions remember to allow anonymous access when you publish as a service in Google Spreadsheets)). Here’s the spreadsheet where the data from the basic form example is being collected.

There are other ways to customise input to Google Spreadsheets e.g. my custom bookmarking service using Google Spreadsheet post.

Let me know if I’ve forgotten anything …


Join the conversation

comment 84 comments
  • James Canada

    Hi Martin, I was playing with the demo above and have two suggestions.
    1) I commented out the APP stuff and used Logger.log instead.
    2) The return data from the API was over 300K. The hack of putting it into a hidden iframe works, but there is still a bunch of bandwidth wasted with a useless return. Instead I added ‘return undefined’ at the end of the spreadsheet doPost(e) script and the problem was solved.
    Cheers James from Canada

  • Neamat

    Thank you so much for your great post.
    I tried the same example but I failed.. Nothing is written in the spreadsheet and nothing is returned in the hidden iframe! .. I wonder what is wrong.. Is there anyway I can debug it?

    • James Canada

      Hi Neamat, start with the obvious. Make sure your spreadsheet is shared correctly and use Firebug (or similar) to watch the network requests. When I am doing quick tests, I often forget to share the spreadsheet!

    • Martin Hawksey

      If you are using GET enter your Google Apps Script publish as a service url in your browser address bar and add &oneofyourvariables=anyvalue (changing oneofyourvariables for one of your spreadsheet column names) and see what happens

  • Neamat

    It worked! Thank you so much 🙂 I was just going to send you a message telling you that it worked. I just forgot to change the sheet name to “DATA” 🙂 .. Thanks again.

  • Adam

    Hi, when I hit my submit button in any other browser than chrome I get to the google login in page. I want anyone to be able to submit, what am i missing?

    • Martin Hawksey

      might be that you need to enable anonymous access when you publish as a service

      • Adam

        If I tick the allow anonymous access, its an unexpected error and not saved… :s
        Thanks for your effort though!

        • Adam

          Ok, now Its working. I deleted the other spreadsheets i had and all of the sudden I was able to allow anonymous access! Maybe there is some restriction on how many spreadsheets you are allowed to publish?
          Thank you Martin!

          • Martin Hawksey

            Sounds like it was an apps script glitch – glad its working now

  • Dana

    Hi Martin,
    This is a really basic question and the answer is probably obvious to anyone with more experience, but we just switched over to Google Apps at work and I’m really new to all of this. One thing we have struggled with is the need for more sophisticated forms than Google Forms allows. Specifically, I would love to have a Google Spreadsheet with a full list of possible answers to a question, and have the form refer to that spreadsheet to create a drop-down list of possible answers to a question. Could this be done using the method you’re describing?
    – Dana

    • Martin Hawksey

      Hi Dana – It is possible to populate a custom form with spreadsheet data but it’s not entirely straight forward. One way would be to use the UI Services feature of Google App Script. This video contains an overview
      You might also want to check out who specialise in this area. The site has lots of tutorials and they provide free consultation to education and non-profits

      • Dana

        Thanks Martin, I really appreciate the quick response!

    • James

      Hi Dana, I have not been follow along, but the short answer is YES. You can skip the default Google Forms UI and make your own, then Post the data back to Spreadsheets. I did a demo awhile back. It was not that hard using jQuery and just double check the network flow in Firebug. I switched some servers around and lost stuff that was not critical. If you are stuck, I could look for the demo.
      Cheers James

      • Dana

        Hi James,
        If you can find the demo, I would love to take a look. As I said, I’m really new to all of this, and having examples makes everything much faster!
        – Dana

  • James Canada

    I will take a look for it. But in the meantime (from memory) here are some steps:
    1) The easiest way is to create the exact form you want using the standard Google Forms UI. This way all the plumbing is done. But it is somewhat ugly. Make the form in one go (don’t move stuff around). This way your idenifers will be sequential (just a bit easier later)
    2) Run it while watching in Firebug.
    3) Grab the useful stuff, like input ids and the post URL and delete the rest.
    4) Create the nice form with your own CSS
    5) I think I used jQuery .Serialize within the Ajax call to get the form data and post to spreadsheet. The Spreadsheet does not care about the form, just the parameters/values.
    6) Then use Martins trick to have the post back go to an iframe so your page doesn’t refresh. I displayed the standard Google Forms Thankyou in the iFrame.
    Hope this helps, are you using jQuery?

    • Stuart

      Do you have an example up yet? I’ve been trying to get this to work with jQuery but so far it hasn’t been. I’m new to this and am unsure how to both do it and test for what is wrong. The jquery ajax I’m currently using is:
      if (validateForm === true) {
      type: ‘post’,
      url: ‘’,
      data: $(“#workPLZ”).serialize()
      else {}
      I changed Martin’s code to doPost and have put in the appropriate headers, changed the sheet name to “DATA”, etc.. I’m just lost and would really appreciate help!

  • Dana

    Hi James,
    I’m not using much of anything yet! I’ve done a small amount of scripting in other languages, but JavaScript is new to me. We just switched over to Google Apps at work, and I’m trying to figure out how many of our processes could be migrated to Google Docs. It’s clear that most things would require a lot of scripting in order to make them work, so the new question is how much I can learn and how much time it would take to implement everything. I really appreciate your help and Martin’s, I have a much better idea of the challenges and solutions now than I did earlier this week.
    – Dana

  • James Canada

    Hi Dana, I too have not done much with Google Apps Script, but plan on using it a lot to get the most value out of Google Docs. When I mention jQuery, it is only used on web pages, not in Google Apps Script (I don’t think) but it would be nice. jQuery is one of many “libraries” designed to make it more productive (and fun) to work with Javascript. I highly recommend it. Also, being very popular, there are lots of free training videos on how to use it. Once you get the hang of it, you will never look back. The other thing to learn is Firebug in Firefox.

  • Mark

    I have just created a form and I am using the right code. I debugged the form and checked the network in the chrome developer tools and it published the information in the right place but I get this error
    XMLHttpRequest cannot load Origin is not allowed by Access-Control-Allow-Origin.
    I also get this in the header area in the network tab;
    User-Agent:Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/535.19 (KHTML, like Gecko) Chrome/18.0.1025.163 Safari/535.19
    Query String Parametersview URL encoded
    Form Dataview URL encoded
    Does anyone know what it could be that causes this problem at all?
    Here is the URL for the form:
    Any help would be much appreciated.

    • pickovven

      Hi Mark,
      I got the cross site scripting error in my console in Chrome too. Even with this error though, I was still able to get the data to post to the spreadsheet.
      I’m a newbie but I wonder if the error will appear no matter what and the reason you aren’t seeing your information POST is caused by something else.
      Good luck!

  • Clem Clarke

    Hi everyone.
    A year or so ago, I set up a few “beautiful?” google forms. One is to collect some User Information, and another to collect Calendar Information.
    Please note that these sites aren’t fully operation yet – I still have a problem getting the Calendar API to allow me to put Time Zones in, which I require. This involves using V3 of that Calendar Javascript API, which I don’t think works with Google Sites. If anyone knows how to do this, please let me know!
    Anyway – the forms.
    The first form is for a calendar. It has drop down lists and a calendar selection panel or data/time picker. You can see it in action here:
    The website associated is called:
    (See note below about using REST, too)
    The second form is for gathering people’s information. The form is here:
    And the website:
    If I can help anyone, please let me know.
    Clem Clarke
    PS: I had a devil of a job finding the right parameters and so on to make a call to the Google Calendar to allow others to enter an event using the REST protocol. You might find the text below useful (you will have to change the SRC parameters to your own calendar and people need to be able to access the calendar).
    Try this:

  • PowerPivot for Excel as a XML/A Server | Gobán Saor

    […] to enable a PowerPivot workbook to act as a “pull server”, and having come across a neat trick to allow a Google Spreadsheet to operate as a proxy between such Excel “steam-powered […]

  • Eric

    I can’t seem to publish it as a service. When I do Publish it only gives me the Publish as Web App option

    • Martin Hawksey

      Google changed the wording slightly -should be the same thing

      • Eric

        Thanks! That worked when I set it to All users, even anonymous.

  • Jon

    Ok, I just posted into a Google Group a question that it appears you answered on the 10th of July. I am wondering if you would take a look at the question in Google and see if there is any advice you can provide to me. This is the URL for the Google Groups question I just barely posed.

    • Jon

      I am such an idiot. The URL is contained within the Script Engine not the Spreadsheet. Doh!

      • Martin Hawksey

        Glad you figured it out 🙂

  • Bolutife Ogunsola

    Hi, check out this custom form creator for Google Drive:

    • Martin Hawksey

      hmm needs a better landing page, you need to explain what the product is before asking permission to access someone’s account

  • [FREE] Receive SMS messages in a Google Spreadsheet for a textwall, voting and more (UK only) JISC CETIS MASHe

    […] The main change is to directly ingest SMS messages into a Google Spreadsheet (using 30 lines of code) which makes it easier for manipulation and presentation. The method for doing this is relatively simple because the IntelliSoftware gateway has a HTTP interface and you can also use Google Spreadsheets as a Database – INSERT with Apps Script form POST/GET submit method. […]

  • Jason Stein

    I have been able to replicate your example, and am very happy about. One question that I have, is how do I reference an array that is passed to the spreadsheet? I added the following three check boxes to your example:
    Amy O’Hare
    Daylen Moccasin
    Gage Milligan
    Basic spam prevention (enter any 4 digit number):
    and they report in the debug section, but I can’t seem to get at the individual elements of Grade10[]

  • Ville

    How do I get the GetId() in the SetUp function to work in the first place? I can do the access run but the second run just gives me an error.

    • Martin Hawksey

      what error does it give you?

  • rsm

    Thanks for all this, great work. I have all this working (form with parameters, submit, they are entered into the spreadsheet. However, then errors start to happen but all are see are cryptic errors on the html response (like “TypeError: Cannot call method “split” of undefined.”), no line given. My script has several hundred lines…
    My question is: how to debug this thing? Logger.log() does not work as the response seem to go to the browser via this app that is returned by doPost() and Logger.log() says there are “no logs”.

  • rsm

    Work around for Logger.log() not working for doPosts(): create another spreadsheet, where you log whatever you want. It is a lot more useful than Logger.log() anyway, you can have multiple columns, timestamps, and you do not lose the info between runs. I found this elsewhere and adapted it.
    col 1: timestamp
    cols 2 and 3: text to log
    //Logger.log workaround
    function loggit(value1,value2){
    var sheet = SpreadsheetApp.openById(‘ id goes here’).getSheetByName(‘sheet name goes here’);
    var row = sheet.getLastRow();
    var now = new Date(); // optional timestamp
    row = row + 1;
    sheet.getRange(row, 1).setValue(now.format(“dd/m h:MM:ss TT”)); // this is an optional custom time format function
    sheet.getRange(row, 2).setValue(value1);
    sheet.getRange(row, 3).setValue(value2);

  • rsm

    One additional question: Would you have any ideas on how to provide immediate feedback to the user who submits a post (that his request is being processed)? My doPost() script is taking about 10 to 15 seconds to process, way too long for the user to hang on watching his browser doing nothing.
    When you use a standard Google Form submit, it appears that their post function goes off asynchronously: a very fast response is given to the user, while the script is running in the background. Not so, with our own doPosts(), where the browser waits for the doPost() completion.
    I tried a javascript workound (showing an animated gif upon pressing submit), not the best solution.
    Any ideas on how to accept the data, fire off the remainder of script asynchronously, and return a response to the browser right away?
    Thank you.

  • Alex

    Hello everyone! I was wondering if it could be possible to write a code into a personalized html form page which would count the entries in the google spreadsheet table and after x entries it would disable a choice or a check-box answer from the html form page.
    For exemple:
    Which type would you like to wear?
    Multiple choices:
    1. Type 1
    2. Type 2
    Let’s say 50 people choose Type 1 the code would count the Type 1 entries in the spreadsheet and after that it would disable the Type one choice and the remaining people could choose only Type 2.
    If it’s too complicated using google spreadsheet could you give me a hint how could I make this?

  • Alex

    The only problem with this that the “Left” column it goes into minus/negative, which means that more people can choose a specific answer than the maximum limit. This would be really great if somehow that specific answer could be disabled from the form when the value in the “Left” column equals zero. If you have an idea how I could do this I would appreciate it if not, thank you anyway! 🙂

  • sourab gupta

    I am new on this.Can you please tell me that how FORM data can be saved in google spreadsheet using javascript.
    I had created one spreasdheet “”.
    But don’t know hoe to save data through javascript/json.

  • Sunny

    The code seemed to had worked for everyone here except me. Mr. Martin Hawksey, everybody is not a code programming ninja as you didn’t elaborate more on your instruction for newbies like me.
    Could you please explain more on how to install the script. For instance;
    1. Run the setup function (you’ll need to do this twice – 1st time to grant acces to Script Properties)
    — And then second time run what…?
    2. Share > Publish as service … set security level and enable service.
    — You didn’t say the location and I didn’t see “Publish as service” anywhere. Where is this? On Spreadsheet or Script Editor?
    3. Copy the service URL and post this in your form/script action
    — Where is this? Spreadsheet and Script Editor have their own publish options so which are you referring to? The path may be helpful.
    4. Insert column names on the DATA sheet matching the parameter names of the data you are passing
    — You see, here you mentioned “sheet”, which is very understandable and easily locatable.
    In the script, is there any changes that are required to be made before put in use?
    And must my form have some unique tags to work well?
    Please, help I really need the your script. And thanks for your great job.

  • Nbrane

    Hi and thank you for the post.
    The script works fine. However, I wonder if you have any idea if there’s a way to let the page know if the addition of a row in a spreadsheet was a success or not? For example by writing some javascript to the hidden iframe on success or failure that can write a message to the page so that the person filling out the form knows if it really was registered or not?

  • Sarah

    Thanks for this very helpful post. However, I cannot get beyond the run setUp part. It gives me the following error:
    TypeError: Cannot call method “getId” of null. (line 53, file “writeSheet”)
    Does the spreadsheet need to be made public before this code will work? (not an option for me unfortunately) Or is there something else I am missing?
    I created a spreadsheet called “DATA” with a header called “Date”. (I replaced “timestamp” in your code with Date). Any insight you can offer would be much appreciated!

    • Sarah

      So for some reason, getActiveSpreadhseet was always returning a null value for me. I got around this by explicitly giving the function the url and id of the spreadsheet I want to use:
      function setUp() {
      var ss = SpreadsheetApp.openByUrl(“
      key={the key}”);
      var id = ‘{the key}’;
      ScriptProperties.setProperty(‘active’, id);
      and everything works fine now 🙂 Thanks again for this!

  • Sasha

    I keep getting an error of
    TypeError: Cannot read property “parameter” from undefined. (line 37, file “Code”)
    I’m quite the noob, but it seems to me that parameter isn’t defined.
    I’m missing something obvious, aren’t I?

  • Sasha

    I keep getting an error of:
    TypeError: Cannot read property “parameter” from undefined
    I’m quite the noob, but I don’t see parameter defined anywhere.
    I’m missing something obvious, aren’t I?

  • Sasha

    Sorry, so much of a noob I double posted.

  • Willis

    I realize it’s been a long time since the OP, but I’m trying to execute this and getting the error:
    TypeError: Cannot call method “getRange” of null. (line 5, file “Code”)
    Is it possible this won’t work anymore with the new google sheets?
    Can anyone advise?

  • miteshan

    Using above code, is it possible to write to spreadsheet(in different sheets) from different forms

  • Genesis

    is there a way to have code reverse the process?… (i.e. have an existing database and hit a search button where it will query a matching item from the database with a sub data)

  • Alexander Ivanov

    Hi, pleace re-build this code for new Properties Services

  • Nacho

    Hi, Martin.
    Very useful script! I’ve been trying to modify it a little bit, adding email sending when new record creates in spreadsheet. But when I add in your script something like this
    function sendEmails() {
    var subject = ‘subject’;
    var message = ‘message’;
    MailApp.sendEmail(‘[email protected]’, subject, message);
    it doesn’t work (((
    Have any idea?

  • wondershow1

    Hi Martin
    I have a question. When I am deploying a spreadsheet based web app, I need to update the app script many times to tune my web app service. But the google web app seems to have a bug, when you publish a spreadsheet based web app and after that you want to modify your script, the modification cant not be updated to the real service.(although the code has been saved). For example, I used to have a doGet method in my web app script and had it published, now I want to add a doPost method, after my client submit data , the only response I get will be “Script function not found: doPost”, although in the google side i have updated the script and republished it.

    • Martin Hawksey

      Hi – when you make changes to a script published as a web you need to save a new version File > Manage versions and then update the web app to that version Resources > Publish as web app (I think).

      • wondershow1

        How to save a new version? I just modify the file in the script editor and save then publish, the version of project has only option, so i clicked the update. But I can not see the result of my modification when I access my service.

        • wondershow1

          Hi, I now how to update the version now, thanks!

      • wondershow1

        Hi Martin,
        I have a new question. Since I want to submit JSON data (a very long array) to a spreadsheet based web app, in this case, how can I make the web app to handle the json submitted data? I think my question is how to tweak doPost to handle posted JSon data(not form data).

        • Martin Hawksey

          Google Apps Script uses a Javascript syntax making it easy to go both ways (JSON.parse() or JSON.stringify())

          • wondershow1

            Hi Martin, maybe I am not good at JS. can you please be a little more specific. I think the JSON-data based Ajax request will be handled by the doPost function(is this correct?). The code in your example only handles regular submitted data, but I dont know how to modify it to handle json data. Can you please give some example?

  • Wylie Digdon

    so how would you recommend capturing data like usenmame dynamically from a googlesheet or even the google user profile?

    • Martin Hawksey

      I don’t think this is possible

  • Giuseppe Calamita

    Hello, I was thinking to a completely different approach; how to customize any row of a google spreadsheet as a record; in a way you may not move any field of a row with operation like sort or else; a kind of block for any row that may enables any operation available but the ones that involve moving/splitting those rows (in terms of their fields) of the spreadsheet?
    Thank you.

  • waseem

    Hi Martin ,
    i have created app script and followed all steps.but it is displaying some error i believe it is not getting spreadsheet
    {“result”:”error”,”error”:{“message”:”Cannot call method \”getRange\” of null.”,”name”:”TypeError”,”fileName”:”Code (cvi)”,”lineNumber”:41,”stack”:”\tat Code (cvi):41 (handleResponse)\n\tat Code (cvi):19 (doGet)\n”}}
    in app script when i run setup() it was giving error of unknown method getid() i provided my sheet id there and now it is displaying above error in json when i open the target file in browser using ajax call
    any guide would be appreciated

  • michal

    Im having a bit of problems with making it work… I’m sure I’m making a very simple and stupid mistake somewhere…
    I have copied the html:
    And switched the links to google script and google sheet to the ones pointing to a copy I made from
    Doesn’t work though, after hitting ‘submit’, the html does not return a message about success nor failure.
    The google sheet is published and made editable to anyone with a link.
    Same with the google script. Should I set the google script
    “Exexcute the app as” : Me ?
    “Who has access to the app?” : Anyone, even anonimous ?

  • KajiTetsushi

    Hi, Martin
    I was led to this article from StackOverflow (
    Pretty outdated, I know, but I was hoping you’d make it very obvious for newcomers like myself that you’ve made an update to the post. I had to search the latest comments ( for the latest iteration. Had I not the patience, I would’ve thought that this solution no longer works and looked somewhere else feverishly for an answer.
    Thank you for sharing, by the way. The 2014 script works.

    • KajiTetsushi

      Ah, dang it. I didn’t read the fine print on the top of the page. I’m terribly sorry, Martin. Please ignore my post.

  • Archit Chandra

    Hello Martin
    Your 2014 post’s comments are closed so I thought this is where I should comment. I was trying to understand the code used and replicate it, but ran into a few problems. The method .getPublicLock() seems to have been deprecated and I’m not sure what to use now to make the script run. To be honest, I can’t really understand everything about how we’re trying to achieve this. It would be amazing if you could suggest a few introductory resources for me to check out with which I can learn how to use Google Apps Script better.

  • Daniel Bravo

    Thanks for the fantastic tutorial.
    I would like to write override a specific row where I find a value I pass in post.
    Could you help me?
    Cheers from Portugal

    • Martin Hawksey

      There are a number of ways you can do this unfortunately they all have varying degree of code as there is no simple update row based of value built-in method. This post contains one method for getting a row number based on a value which would be used instead nextRow

Comments are closed.