AJAX post to google spreadsheet

Go To StackoverFlow.com

22

I am attempting to post form data to a google spreadsheet. Currently, if the form is validated, then the following occurs:

if (validateForm === true) {
        $.ajax({
            type: 'post',
            url: 'https://docs.google.com/spreadsheet/ccc?key=0AlwuDjMUxwhqdGp1WU1KQ0FoUGZpbFRuUDRzRkszc3c',
            data: $("#workPLZ").serialize(),
            success: alert($("#workPLZ").serialize())
        });
    }
    else {}

I used the success setting to verify my form data is being serialized properly (it is) and that it is successful. However, my google spreadsheet is not being updated (no data is going through). I used the example code here, changing doGet to doPost (http://mashe.hawksey.info/2011/10/google-spreadsheets-as-a-database-insert-with-apps-script-form-postget-submit-method/), and have made the google spreadsheet publicly available (and editable by anyone). I followed the instructions, copying in the code to googledocs and then running the setUp twice (first time asked for permission, second time I ran it I didn't notice anything happen). Can anyone help me? I feel like I am super close.

2012-04-03 19:26
by Stuart Nelson
typically cross site scripting is prevented (i.e. ajax post to a domain other than the current one you're on), and while it will return a 200 code, it actually fails silently. Are you sure this isn't the case - Kristian 2012-04-03 19:33
I have no idea - I'm relatively new to all this. How can I check - Stuart Nelson 2012-04-03 20:07
workPLZ. how I feel 99% of the tim - Jacob Raccuia 2013-11-25 16:49


35

Alright, I came up with a solution. After being informed of the cross-domain AJAX issues, I decided to go ahead and follow to a "t" the method used by the article author at http://mashe.hawksey.info/2011/10/google-spreadsheets-as-a-database-insert-with-apps-script-form-postget-submit-method/.

To post data to your google spreadsheet, first make the spreadsheet and change the sheet name (lower left hand corner) to DATA. Next, open the script editor (Tools ==> Script Editor) in your spreadsheet and paste the script from the article. Change "doGet(e)" to "doPost(e)". Run the setUp script twice. The first time it will ask for permission to run (grant it), then the second time you select to run it you won't get any popup indication it has run (I ran mine in the editor so it said "running setUp" above the code entry area, but that was all). After that, select "Publish" in the script editor, and then select "Publish as Service". Click the "Allow anyone to invoke this service" radio button and the "Allow anonymous access" check box. Copy the URL (IMPORTANT!) and click "Enable Service". This was the "difficult part".

In your HTML form, each element that you're submitting must have a "name" attribute (e.g. ) This name is how the data is sent - each entry is attached to its name. Make sure that for every piece of form data you're collecting, it has a name, and that name is entered as a column on your spreadsheet (that's how it maps the data from your form to your spreadsheet). For your form, set the method to post and the action to your "Publish as Service" URL (which I told you to save) like this:

<form id="formID" method="post" action="URL" target="hidden_iframe">

I included a form id so I can select the form and submit it with jquery. In your HTML, before the above form, add your hidden iframe:

<iframe name="hidden_iframe" id="hidden_iframe" style="display:none;"></iframe>

Set some sort of form validation (not necessary, but if every field isn't filled out you'll get incomplete data in your spreadsheet), and if it is validated have it call a jquery .submit(). e.g.:

    if (formValidation === true){
           $("#formID").submit();
    }
    else {}

So that's that. Good luck!

2012-04-04 16:00
by Stuart Nelson
The final piece of the puzzle is some sort of response confirmation which is documented here using the iframe's onload(). It's certainly not bulletproof but it's a start. http://stackoverflow.com/questions/467008/asynchronous-cross-domain-post-request-via-javascript#answer-46706 - Dylan Valade 2013-04-13 00:56
Thanks a lot for your answer, you made my day - GG. 2014-03-17 10:53
@Stuart Nelson . There is no Publish as Service instead there is deploy as web app ! should we select that - user1788736 2015-10-04 19:39
this answer is over 3 years old, I have no idea what the current set up is like - Stuart Nelson 2015-10-05 07:23
Its working in 2017 als - Mansoorkhan Cherupuzha 2017-03-21 10:07
@MansoorkhanCherupuzha may I see a simplified version of your use case? I'm working on https://stackoverflow.com/questions/43897395/qr-code-to-update-spreadsheet and would be glad to share whatever I eventually get to work - icedwater 2017-05-10 15:56


11

As Google Apps Script now has a ContentService that can return JSON responses it is possible to make ajax requests without using the hidden iframe. As the article author of the original solution I've published an updated version of this technique which includes an ajax example

Users may also want to consider switching to this new version as it utilises other new Google Apps Script Services, in particular:

2014-07-06 09:00
by mhawksey
The link to your article is dea - P Hemans 2015-03-21 19:56
Server woes - should be bac - mhawksey 2015-03-22 17:38
@mhawksey can you help? http://stackoverflow.com/questions/33180172/why-does-mailapp-sendemail-from-google-spreadsheet-script-not-work I'm using your script but trying to send an email when the form gets POSTed - nelsonic 2015-10-16 21:58
@mhawksey: your sample code and answer should be on Stackoverflow precisely to avoid case where personal sites go down - Hugolpz 2018-02-13 18:41


3

I will show you the EASY WAY to send data to a Google SpreadSheet without AJAX neither Google Form, or PHP...just Google SpreadSheet and HTML or even Android.

  1. Create a new Google SpreadSheet.
  2. Open Tools/script editor

You just need two files in the editor a HTML and a Code.gs:

as an example:

  1. Go to File/new HTML name this file=Index.html:

    <!DOCTYPE html>
    <html>
    <head>
    <base target="_top">
    
    <script>
    function Enviar(){
    
    var txt1=document.getElementById("txt1").value;
    var txt2=document.getElementById("txt2").value;
    var txt3=document.getElementById("txt3").value;
    google.script.run.doSomething(txt1,txt2,txt3);
    }
    </script>
    </head>
    
    <body>
    Prueba de Envio de informacion<br>
    <input type="text" value="EMAIL" name="txt1" id="txt1"><br>
    <input type="text" value="CEDULA" name="txt2" id="txt2"><br>
    <input type="text" value="NOMBRE" name="txt3" id="txt3"><BR>
    <Button name="btn1" onClick="Enviar();">Enviar</button>
    </body>
    
    </html>
    

there are 3 fields to send: EMAIL, CEDULA, NOMBRE

  1. In the same ScriptEditor go to de Code.gs file and type:

    function doGet() {
    return HtmlService.createHtmlOutputFromFile('Index')
    .setSandboxMode(HtmlService.SandboxMode.IFRAME);
    }
    
    function doSomething(s1,s2,s3){
    
    Logger.log('datos:'+s1+"  "+s2+"  "+s3);
    var enlace="https://docs.google.com/spreadsheets/d/
    1XuAXmUeGz2Ffr11R8YZNihLE_HSck9Hf_mRtFSXjWGw/edit";
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var ss = SpreadsheetApp.openByUrl(enlace);
    var sheet = ss.getSheets()[0];
    sheet.appendRow([s1, s2, s3]);
    Logger.log(ss.getName());
    }
    

where enlace is the url of your SpreadSheet

  1. Publish as Application App and get the url of your new Script. Now you can use this url as embed in your HTML application or android app. That's it. The user will be asked for permission to open this script
2016-04-03 02:08
by user5807327