Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pushing data to Google spreadsheet through JavaScript running in browser

Tags:

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.

like image 909
Martin Dimitrov Avatar asked Nov 10 '13 08:11

Martin Dimitrov


People also ask

Can Google Sheets automatically pull data from a website?

Using Google Sheets as a basic web scraper Turns out, basic web scraping, automatically grabbing data from websites, is possible right in your Google Sheet, without needing to write any code. You can extract specific information from a website and show it in your Google Sheet using some of Sheets' special formulas.


2 Answers

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:

contact form

Result (row in sheet):

row in sheet

Hope it helps others.

like image 74
nelsonic Avatar answered Sep 28 '22 04:09

nelsonic


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.

like image 39
dev Avatar answered Sep 28 '22 04:09

dev