Pushing data to Google spreadsheet through JavaScript running in browser

Question

I am working on an web application where I would like to allow the user to push data to her own Google spreadsheet.

Firstly I tried to use Google APIs Client Library for JavaScript but it doesn't seem to cover the Spreadsheet API (https://developers.google.com/apis-explorer/#p/).

Then I decided to use directly the Google Spreadsheets API version 3.0. I manage to retrieve the user's spreadsheets using jQuery and JSONP:

$.ajax({
  url: 'https://spreadsheets.google.com/feeds/spreadsheets/private/full?alt=json-in-script&access_token=' + access_token,
  dataType: 'JSONP',
  success: function(data){
    // use the spreadsheets
  }
});

In the same method I retrieve the sheets from the user selected spreadsheet. Then I have to POST the data to the selected sheet. And here comes the problem: can't do it using JSONP. And the Google server seems not to support CORS. I get the following error in the browser:

XMLHttpRequest cannot load https://spreadsheets.google.com/feeds/... Origin ..mysite.. is not allowed by Access-Control-Allow-Origin.

Thanks for looking into this.

Solution 1

I was looking into this too about 8 months. I stumbled across a blog post written by Martin Hawskey. I followed the guide here and I was able to set up a HTML form posting to a spreadsheet.

Effectively you set up a published web app inside the spreadsheet that can receive the data. To get around the CORS issues you target a hidden iframe on the page. I would replicate the code in this post but there is a fair bit of it.

DEMO

• Here is the HTML form.
• Here is the spreadsheet receiving the data.

I'll provide some advice that I wish I was given when I started looking at this. If you can... try and set up a PHP server you can use. Posting the data is a lot easier and flexible. I now use Zend GData religiously at work and wish I had found it sooner :)

EDIT

Marting Hawskey has updated this to support a AJAX submission without the use of a hidden iframe. See here.

Solution 2

Step-by-step instructions with screenshots

After reading Martin Hawskey's good introduction (to sending data from an HTML form to a Google Spreadsheet) and seeing a few gaps/assumptions, we decided to write a detailed/comprehensive tutorial with step-by-step instructions which a few people have found useful:

https://github.com/dwyl/html-form-send-email-via-google-script-without-server

The script saves any data sent via HTTP POST in the Google Spreadsheet, and optionally forwards the content to an email address. (useful if you want to be notified of new data)

HTML Form:

Result (row in sheet):

Hope it helps others.