I like it when I find some useful code and it works straight away. When it doesn’t I end up spending an awful lot of time trying to figure out what is happening. turning the programmers dark magic into something comprehensible.
Continuing on from the previous post I started to look at the output and code a bit more closely. I think this process is quite useful, so I decided to write about the coding process so that if I revisit this later I wont spend so much time trying to re-figure it out. The main area of focus is on Anthony Brunson’s tutorial number 2 of adding the extra coding to display results to a web page. The google API will just stay as Dark Magic!
Here is link to file I am talking about. Right click on the page and choose “View Page Source” and cut/paste into text editor to explore.
Initially I thought, I have JSON output, great. But didn’t look at it too closely. In fact, because of the spreadsheet layout the information in json is in an array, or rather an array in an array.
If you look at the console output at the top level, it is called object:
If we drill down further, looking at item 0: of the values: we see that it has values of 0: to 9: (so 10 values). At the end there is an item length:10 that tells you how many items there are in array values: 0:
First. Values: 0: is the header column of the spreadsheet, so contains all the column headers (Row 1 in spreadsheet). Secondly, the following values: 1:, 2:, …..161 will all be the data rows.
7: “Percentage Change”
9: “Green Bond”
<prototype>: Array 
1: Array(10)[ “ASB”, “ASB Capital Limited (NS)”, “$0.907”, … ]
So the Json file has 1. Top level object. 2nd level 3 items majorDimension, range, values. And within the item:values there is an array of 162 items, and within that array for each item there is an array of 10 items .
So rows are Object: values: [array 0 to 161]
Object: values: [array 0] [array 0 to 10]
Object: values: [array 1] [array 0 to 10]
Object: values: [array 2] [array 0 to 10] etc
The code takes the result object and loops (iterates through, starting from 0 to 2 (col<3), so in english columns 1,2,3) through the columns (inner for loop- 3rd line of code) then rows (outer for loop-2nd line of code) and pushes these results out as a table.
row 0: col 0, row 0: col1, row 0:col2
row 1: col 0, row 1: col1, row 1:col2
row 2: col 0, row 2: col1, row 2:col2 etc
which would give example results from a table with 3 columns and 3 rows :
So the function gets given a json file and it loops through it.
How big is the JSON file? If you tried using this code on the json output we have which has 10 columns and 162 rows it would get confused and break as it would read the first 3 columns from row 0 correctly and then would try and read the first column of row 1, but the next item in the json array would be column 3 row 0, so it would stop and break.
So we need a way of making sure we have the correct length of the arrays (cols and rows) to feed into the loops of the function, then it won’t break, whatever shape json file array we give it, so we change the code in the function to this:
I don’t quite understand how this part of the code works (I cannot see anything in the html code about border (drawing border around the cells).
Anyway, again it has fixed numbers for rows and columns to iterate through $row<8, $col<3.
Then I thought, wait a minute, we know the size of the spreadsheet that we are calling from Sheets, so we can just put the fixed length in.
So no need to find length of array, that is known. We can count on the spreadsheet column numbers and row numbers. We just need to put these numbers in to the code when we are putting in the spreadsheetId and range etc when we are setting up the code.
I am having a few issues with the data being displayed. Its the sign in issue.
So I am still having issues with accessing the data from the spreadsheet even though I have made the spreadsheet public and accessible to the web. I still have to work through those issues. I have found this site about Auth2 Google playground that I will explore to see if it can help me.
I put the buttons (Sign In/Sign Out) back in and tried to test from my mobile phone but the buttons aren’t working on that. All a bit of a challenge.
Yes, data is displayed on the web page from Google Sheets. Unfortunately with this tutorial it is static, and has a fixed order. There is no highlighting of Column headers nor is there any formatting.
The share market data that I am displaying needs to be sorted to be able to be explored.
The solution is to look at JQuery dataTables which will allow for some formatting and sorting. That is another exercise. The failure issues I highlighted in Part 1 of this process showed dataTables displaying information from Google Sheets, so it is possible. I hope to show on the page where I am displaying current output GoogleSheets data from Web Table displayed in Web Page with Table automatic update the two types of outputs, but that is for another day. DataTable example here.
Will have to load the file to view of the image that they want to put the link into ( eg view in Paint to get x/y coordinates of where the hotspot to go)
Then the Target location & the Hyperlink (to the proposed photo- I think realistically, it has to be part of the set in the specific directory) will needed to be added ( Can have columns for this in Excel Macro)
There may be multiple links from one photo (eg A hall to lots of units), so have to make provision for multiple links)
These need to be processed so that they all end up in the same directory so you can Zip them up and share the whole package.
Maybe the Google Sheets to Web, if bi-directional, can be a web & spreadsheet solution. So I want to explore the updating Google Sheets from the web as per Anthony Brunson’s tutorial number 3. This is the next exercise I want to stuff up.
So what is still outstanding is Login Issues for accessing Data on the web, even with public sheets & setting up JQuery dataTables.