Web Data3- Part 1. Google Sheets API to Web Page

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Getting data from the web and being able to manipulate it and display it on your own website.

Using Google sheets to refresh the data at specified intervals allows you to get live feed from sites that do not have an API for drawing the data down.

At the end of the last post I was not happy with the iFrame link from Google Sheets to a web page.I wanted to download the raw data so I could choose how to display it.

Google Sheets has an API that sounds pretty cool. Not only can you read google sheets and display the data on a web page, you can also push the data from the web page to the Google sheets, create new sheets and populate them too in an automated manner.

The reason this is cool is that you get spreadsheet functionality and also API functionality as far as getting and pushing data around. It is flexible. You do not need a site to have an API, if it has tables that update, grab the tables and put them into Sheets and update at controlled intervals.

You can push the data through to a web page and format it as you like. I was interested in the dataTables format but you could just do a HTML table format, with refreshed data. So you are using your Sheets as a sort of refreshing database from other data on the web.

Following on from the previous post, I read that I needed to export the data from Google Sheets in a JSON format if I want to display the data as a dataTable in a web page. I had a lot of failures. See end of post resources that I initially tested. That was not the way to go.

Resources

You need to use Google Sheets API . There are 3 video tutorials that show how to set this up, I have shown all 3 links as they are not in order in YouTube:

The first 2 set up reading from google sheets to Web, the 3rd is how to write from the web to Google Sheets. Anthony Brunson’s tutorials are really good in my opinion. There are also a couple on how to use Google Calendar API to set up an Web page appointments scheduler. There is also a Github link for extra code for the first 2 vids.

Process

The first part is to set up an API key. I think the first video goes into that pretty well, so just follow that to do it.

I’ve set up this sheet with data from NZ Stock Exchange data and brought it in on one tab (ImportData) and tidied it up on another tab (DisplayData). The DisplayData tab is the information I want send to my web page via the API GET process.

It is important to get the spreadsheetId which is the string off numbers in the browser URL, copy this string as you need it to identify your spreadsheet.

This data is static at the moment and is not being refreshed, I’ll do that later.

We’ll go now to Google Sheets /API v4/ Reference Tab :

https://developers.google.com/sheets/api/reference/rest/

We are looking for SPREADSHEET VALUES  & GET (request):

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get

To the right of the page there is “TRY THIS API” panel. It has 2 red highlighted boxes of data you have to supply. In this case, the spreadsheetID and the range.

We have the spreadsheetID from the URL in the browser to the Google Sheet & the range can be specified as a sheet tab , eg DisplayData, or to a range on the spreadsheet tab such as DisplayData!A1:C7, so it will only go and look at those specified cells.Then we hit the execute button.

As this is a test area, we are not using the API key, so we need to authenticate that we have the right to look at this Google Sheet, so it asks for an account. I choose an account.

It then asks for permissions to let the Get request go in and look and grab things in the following areas, Google Drive, & Google Spreadsheets. click allow and the Get request executes.

There is a 200 code (successful). And you can see the JSON formatted data of the output in bottom right panel. So the Test works.

If you try doing this in Postman with the same Get request, the Cors blocks it because it isn’t authenticated (this is where I had all my problems (see appendix)). I was not using the API key for authentication and was blocked as I wasn’t authorised to look at the Google Sheet.

So, scroll down on the spreadsheet.values.get page and you’ll see some examples. I will use the browser example and copy/paste the code into Notepad++

In the code, after saving it ( Actually, I think in the tutorial he saved it as a PHP file, not an HTML file) so OOPS, save as a PHP file.

 You need to fill in the blanks and then copy this file as a .php one across to your server (you may be testing on a local Server) I have uploaded it to my https://cr8ive.cf/ site and in the Credentials limited the use of the API key to only calls from this site.

Now, the basic code, cut/pasted from the spreadsheet.values.get page works, for a simple 3 col x 4 row spreadsheet and a 10 column/162 row shares spreadsheet that is pushed to the console. The web page gives you a SignIn & SignOut button and once you click on the signIn button it requests you to sign in  (I have to check if once you have shared the spreadsheet to the web whether people need to sign in with their own google account (you need a gmail account for google sheets, even if you are only the recipient and not the creator)) .

It generates the json structure in the console (I tried pushing to Document.Write() but it just showed [object,Object] so not useful. I get some warnings in firefox that you don’t get in chrome.

Once I did the additions to the code I got a error and an uncaught exception. It is on a later GET to :

GET  https://ssl.gstatic.com/accounts/o/3723580519-idpiframe.js (so looking at another js file).

So that will need a bit more debugging.

Process so far

Quite limited success in the process. I can :

  1. get a json output only to CONSOLE so far, of the spreadsheet data.
  2. I still need to sign in via a button at the top of the page.
  3. I can connect to the data, I just cannot get it out at the moment in the manner that I require it to display on a web page.
  4. Basic boilerplate code from example works but the tutorial add on’s  to that code don’t at this point.

Moving on, my next steps are :

  1. The output needs to be captured so that there is not an error on output, and that I can control where the data goes (not only to console).
  2. The process in the tutorial gives a fixed 3 column by 8 row grid for the table on the page. This table size needs to respond to the size of the data. using xxx.length to get the number of rows and a similar process to get the number of columns.
  3. To be able to do an automatically log in (so page refreshes in Browser with updated information) . I think there may be something I can do with the button, my only concern is that I may need to give details of my gmail account. If this is so, then I’ll need to setup another, open, gmail account to host the spreadsheets (and possibly write a copy to a secure email account- maybe automate that) .

So, link to  basic PHP file that works is here. This displays the page, you have to look at page source to see the code.

This is an example of seeing something on a tutorial where they make it look so easy, when in fact, what you had envisaged using the process for ends up being a bit more complicated. A lot of things are going on but the tutorial process doesn’t highlight them.

My learning process is to take some code, try and adapt it, then figure out what is happening.

At the end of the process, I want to be able to display dynamic tables on the web page and some graphs too.  Hopefully the next post will be able to do that.

Appendix. Failed attempts at connecting Google Sheets to Web

I had a couple of links at the end of the last post :

Use Google sheets as the database and use a JQuery DataTable on the web page to format the table data you want displayed. There is this discussion about it.  Also a link to a JSFiddle example. All for getting a more tidy output than an iFrame. I have tested these and they do not work. I think Google Sheets process has been updated, as if I replicate the data table displayed in the JSFiddle example and try and link the code to my spreadsheet, I get a cors block to the data. (Frustrating).

There is a post here on sheets to JSON  (images wont show)  with a link to a Github JS code for the process. I then found this video that steps through using Pamela Fox’s code here:

This saves to a JSON file on your Drive that you can link to, I still get a cors error:

Cross-Origin Request Blocked: The Same Origin Policy disallows reading the remote resource at https://……… (Reason: CORS header ‘Access-Control-Allow-Origin’ missing).

Even after making the file public for sharing on the web! So not sure how to deal with this.

I thought this article “Use a Google Spreadsheet as your JSON backend” was interesting as it gave a couple of different approaches but on testing neither of them worked for me.

This article has some simple code for testing, no date on the article unfortunately, and it doesn’t work either.

96 Comments

Add a Comment

Your email address will not be published. Required fields are marked *