Web Data 4 – Web Page to Google Sheets Update with API

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Following on from the previous post  I want to try and be able to update a Google Sheet from a web page via the link.

I will be following this video:

Unfortunately there is not a link to code for this addition, unlike videos 1 & 2 so I’ll need to write it from looking at what is on the screen ( look at my comments at the bottom, code there).

On watching the video I did note that you had to choose an option for Scope from the links above. In the first example, writing to web from Google Sheets ‘https://www.googleapis.com/auth/spreadsheets.readonly was chosen, but in this exercise, we will be writing to the sheets so the scope should be  :

 // TODO: Authorize using one of the following scopes:
      //   'https://www.googleapis.com/auth/drive'
      //   'https://www.googleapis.com/auth/drive.file'
      //   'https://www.googleapis.com/auth/drive.readonly'
      //   'https://www.googleapis.com/auth/spreadsheets'
      //   'https://www.googleapis.com/auth/spreadsheets.readonly'
      var SCOPE = 'https://www.googleapis.com/auth/spreadsheets'

Another issue in the video is that he is very thorough about the coding but does not discuss the new save button. The Save button is what send the code back from the web page to the Google Sheets.

You need to add another function:

function handleSaveClick() {
makeApiCall(action=”write”);
}

And another button:

<button id=”save-button” onclick=”handleSaveClick()”>Save</button>

and then it all works.

Process

For this exercise I’m going to use a basic spreadsheet (not an updating one from the web), so we can see the data update. Note, the links below you may be able to access, or maybe not, as far as I can ascertain the gmail account is needed to get into a site, and sometimes only specific ones, even when the data/access is made public.

Here is the link to the spreadsheet:

https://docs.google.com/spreadsheets/d/15tH8Tw8n-RhLsngG5ZLLgZ5CneEwBok–ugvzEictSM/edit?usp=sharing

Here is the link to the webpage php file (right hand click on page to get the code):

https://cr8ive.cf/data/sheets/indexBackToSheets.php

An interesting thing in this is that  in the ValueInputOption the data is ‘RAW’. This was mentioned in the video by Anthony.

1. In spreadsheet column A we will make DataType “Number”, you see that it becomes Right Justified in the cell. (I tested using SUM() and it added cells together in column A).

2. On Web it becomes Left Justified, DataType “Text”. So you cannot use the data for number formulas. When sending data back from Web to Spreadsheet it converts the spreadsheet to DataType “Text”. So you cannot do any number manipulation with it.

looking at the ValueInputOption page you only seem to have 2 options, see below ( Note, why has it a default that doesn’t work???).

After changing ValueInputOption from “Raw” to “User_Entered”:

So  a “Number” formatted column in the spreadsheet transfers and is displayed as a “Text” formatted column on the Web Page. When it is posted back to the Spreadsheet it converts back to a “Number” column. Any formulae that are in Cells that are displayed on the Web page are lost, but any outside the web page are still function and stay active.

End Comment

This is cool. I like it. In some ways this is like having a connection to a database and having a 2 way process of 1/ retrieving data from the database & 2/ being able to update the database. A CRUD process

(Create/Read/Update/Delete) but in a more toned down manner using spreadsheets and web pages.

In a database interface you’d use forms to be able to manage the data for the uninitiated. People who were not interested in the code but only in the results of pushing/pulling data from the database.  The point I’m trying to make is that there are a lot of people who are comfortable with spreadsheets but not databases. So they can manipulate the data more readily to suit their needs with a spreadsheet, where in other circumstances they’d walk away from databases. They would be prepared to get their hands dirty with spreadsheets.

An interesting part of this is the ValueInputOption = “User_Entered”. In this way, formulae will work on the pushed/pulled data.

This process, as its JavaScript, is temperamental. It takes a while to get it set up, but once setup its pretty robust.

As I’m not familiar with Google Sheets, I’m not sure what the Excel VBA macro equivalent is (maybe “scripts”?) . From interwongling I get this which says Google App Script (GAS) [Cool acronym!] is JavaScript. O, woe is me. I do find JavaScript impressive in what it does, but its syntax is so temperamental. I find I spend hours debugging a short bit of  JS code.

Google Sheets API Methods

So far, I’ve only been using the GET & UPDATE requests of the Google Sheets API. There are a few more to explore (red arrows are the ones I’ve used so far):

security OAuth2 issues

So far the process has been great with the Google Sheets API, apart from the authentication. This is the next part of the challenge. If that can be overcome then this is certainly a versatile tool.

In the next post I will need to try and get my head around the authentication process. Maybe the power of the Authentication process  is controlling who has access to the data and who can update it. I personally like to have an open process and then start closing it up as required, rather than micro-managing authorisations, a very time wasting exercise in my opinion. Still, some people enjoy it.

I have found a couple of interesting areas to explore regarding the logging in to the sites with Google developers OAuth2 and a playground for exploring how to automate the web access to the google Sheet.

A daft exercise

I was really happy when I got this up and running,. I thought wouldn’t it be great to be able to have a web page so that I could put my expenses into a web page and it’ll update the Sheet! I did do it. Then I stood back and thought about it, why not just open the Google Sheet and do it directly? I felt a bit daft! This is not the appropriate use of this process.

 

Add a Comment