abbood abbood - 9 months ago 23
Ajax Question

how to make web form post directly to google sheets without authentication page

Background



I simply want to create a landing page that has a form that saves visitors email address in a google sheet.

I found this useful post that has a google app script that does the work. I followed the instructions (on my localhost) and here is the request code:

request = $.ajax({
url: "https://script.google.com/macros/s/my_app_script_id/exec",
type: "post",
data: serializedData
});


but when I click on submit I get this error:


XMLHttpRequest cannot load
https://script.google.com/macros/s/my_app_script_id/exec.
Response to preflight request doesn't pass access control check: No
'Access-Control-Allow-Origin' header is present on the requested
resource. Origin 'http://localhost:8000' is therefore not allowed
access. The response had HTTP status code 405.


Which is kind of weird, considering that I have already published the app to be accessible from everyone as depicted here:

enter image description here

Fair enough, I need to use CORS to access Google APIs.

Problem



The instructions above require that users authenticate themselves, so this is what a user flow would look like on my landing page:

given
user adds email in text field

when
user clicks on get started button

then


expected

the form should submit just fine

actual

a google authentication screen shows up, and asks people to authorize the app

Question



How can I make the web form run the google app script without

Update



Interestingly, I tested the same script on the blog post and it works fine, but if I copy the url of the script there
https://script.google.com/macros/s/AKfycbzV--xTooSkBLufMs4AnrCTdwZxVNtycTE4JNtaCze2UijXAg8/exec"
and call it from my webservice, I get the same CORS error message.

So this is proof that the script can't just be called by anyone, it must be whitelisted somehow. How can I do that?

Update 2



the author of the blog implied that i should be using https, working on setting that up

Update 3



I'm using JSONP right now, but somehow the way the google app script interprets the JSONP data is weird.

So as instructed by Spencer Easton I'm calling it like so:

var url = "https://script.google.com/macros/s/AK...g8/exec?data=" + serializedData +"&callback=?";
$.getJSON(url, successCallBack).fail(failCallback)


however I keep on getting undefined,

enter image description here

I tried to debug my google app script code using instructions from here like so:

function fakeGet() {
var eventObject =
{
"parameter": {
"email": "hindi",
"callback": "fakecallback"
}
}
doGet(eventObject);
}


This code worked perfectly and updated the spreadsheet as expected. So what am I missing here? How is google app script exactly interpreting the jsonp data?

Answer Source

Since you are returning JSON you have to use JSONP.

https://developers.google.com/apps-script/guides/content#serving_jsonp_in_web_pages http://ramblings.mcpher.com/Home/excelquirks/gassnips/jsonpnotp

For you it would roughly look like:

postToSheet.js

var url = "https://script.google.com/macros/s/AK...g8/exec?data=" + serializedData +"&callback=?";
$.getJSON(url, successCallBack).fail(failCallback)

code.gs

...
 // 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 callback = e.parameter.callback; // required for JSONP
...
return ContentService
        .createTextOutput(callback+'('+ JSON.stringify({"result":"success", "row": nextRow})+')')
        .setMimeType(ContentService.MimeType.JAVASCRIPT);