CODEWITHSUNDEEP
javascriptjqueryajaxgoogle-apps-scriptcors
abbood
abbood

Reputation: 23558

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 article 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?

Upvotes: 2

Views: 2911

Answers (1)

Spencer Easton
Spencer Easton

Reputation: 5782

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

Upvotes: 1

Related Questions