AJAX post to google spreadsheet

Question

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.

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, Apr 03 '12 at 19:33
I have no idea - I'm relatively new to all this. How can I check? — Stuart Nelson, Apr 03 '12 at 20:07

Stuart Nelson · Accepted Answer · 2012-04-04T18:31:00.267

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!

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-467065 — Dylan Valade, Apr 13 '13 at 00:56
@Stuart Nelson . There is no Publish as Service instead there is deploy as web app ! should we select that ? — user1788736, Oct 04 '15 at 19:39
this answer is over 3 years old, I have no idea what the current set up is like. — Stuart Nelson, Oct 05 '15 at 07:23
@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, May 10 '17 at 15:56

score 13 · Answer 2 · answered Jul 06 '14 at 09:00

13

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:

using LockService to prevent concurrent write access; and
switching to the new PropertiesService to store script data

answered Jul 06 '14 at 09:00

mhawksey

2,013
5
23
61

The link to your article is dead – CarbonMan Mar 21 '15 at 19:56
Server woes - should be back – mhawksey Mar 22 '15 at 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 Oct 16 '15 at 21:58
@mhawksey: your sample code and answer should be on Stackoverflow precisely to avoid case where personal sites go down. – Hugolpz Feb 13 '18 at 18:41

score 3 · Answer 3 · answered Apr 03 '16 at 02:08

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.

Create a new Google SpreadSheet.
Open Tools/script editor

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

as an example:

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

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

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

AJAX post to google spreadsheet

3 Answers3

Linked