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

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

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

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

  1. Pingback:

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

    Reply
  3. 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?

    Reply
    1. 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!

      Reply
    2. Post author

      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

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

    Reply
  5. 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?

    Regards
    Adam

    Reply
        1. 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!

          Reply
  6. 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?

    Thanks,
    - Dana

    Reply
    1. Post author

      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 http://www.youtube.com/user/GoogleDocsCommunity#p/c/0/5VmEPo6Rkq4

      You might also want to check out http://simpleappssolutions.com/ who specialise in this area. The site has lots of tutorials and they provide free consultation to education and non-profits

      Martin

      Reply
    2. Post author

      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

      Reply
      1. 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!

        Thanks,
        - Dana

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

    Reply
    1. Stuart

      James,

      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) {
      $.ajax({
      type: 'post',
      url: 'https://docs.google.com/spreadsheet/ccc?key=0AlwuDjMUxwhqdGp1WU1KQ0FoUGZpbFRuUDRzRkszc3c',
      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!

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

    Thanks,
    - Dana

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

    Reply
  10. Pingback:

  11. Mark

    Hello.

    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 https://docs.google.com/macros/exec?service=AKfycbydEiEGwZxvMHOJmtCT_6TiwEPUnh2xRW7WD3eU. Origin http://betaburo.markdunbavan.co.uk is not allowed by Access-Control-Allow-Origin.

    I also get this in the header area in the network tab;

    Accept:*/*
    Content-Type:application/x-www-form-urlencoded
    Origin:http://betaburo.markdunbavan.co.uk
    Referer:http://betaburo.markdunbavan.co.uk/
    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
    service:AKfycbydEiEGwZxvMHOJmtCT_6TiwEPUnh2xRW7WD3eU
    Form Dataview URL encoded
    field1:Mark
    field2:Dunbavan
    field11:polligraf
    field3:04/27/12
    field7:1
    field8:10

    Does anyone know what it could be that causes this problem at all?

    Here is the URL for the form: http://betaburo.markdunbavan.co.uk

    Any help would be much appreciated.

    Thanks,
    Mark

    Reply
    1. 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!

      Owen

      Reply
  12. 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:
    https://sites.google.com/site/enlighteningactivities/members-add-events

    The website associated is called: http://www.EnlighteningActivities.com

    (See note below about using REST, too)

    The second form is for gathering people's information. The form is here:
    http://members.iinet.net.au/~mitsu2/ConnectingPeople.html

    And the website: http://www.ConnectingLightWorkers.com/

    If I can help anyone, please let me know.

    Cheers,
    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:
    http://www.google.com/calendar/event?src=default&action=TEMPLATE&text=Add%20a%20Brief%20Description%20of%20Your%20Event%20Here.&dates=20120501T130000Z/20120501T130000Z&details=You%20can%20provide%20a%20long%20description%20of%20the%20event%20here%2C%20and%20you%20may%20copy%20and%20paste%20if%20you%20prefer.&location=Place%20the%20Website%20here%20where%20the%20talk%20can%20be%20listened%20to.&trp=false&sprop=www.EnlighteningActivities.com&sprop=name:Enlightening%20Activities&ctz=America%2FNew_York

    Reply
  13. Pingback:

  14. Pingback:

  15. Pingback:

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

    Reply
  17. Pingback:

  18. 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:
    Name:

    Comment:

    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[]

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

    Reply
  20. rsm

    Martin,

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

    Thanks.

    Reply
  21. 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);
    return;
    }

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

    Reply
  23. 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?

    Reply
  24. 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! :)

    Reply
  25. sourab gupta

    Hi,
    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 "https://docs.google.com/spreadsheet/pub?key=0AiWulaVw0yv0dF95Qnk1TXh4ck5HdEVQZmw1YUNfTFE&output=html".

    But don't know hoe to save data through javascript/json.

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

    Reply
  27. 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?

    Reply
  28. 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!

    Reply
    1. 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("https://docs.google.com/spreadsheet/ccc?
      key={the key}");
      var id = '{the key}';
      ScriptProperties.setProperty('active', id);
      }

      and everything works fine now :) Thanks again for this!

      Reply
  29. 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?

    Reply
  30. 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?

    Reply
  31. Willis

    Hi,

    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?

    Reply
  32. 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)

    Reply
  33. 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?

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

    Reply
    1. Post author

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

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

        Reply
      2. 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).

        Reply
          1. 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?

Leave a Reply