{"id":4263,"date":"2018-08-06T18:33:08","date_gmt":"2018-08-06T18:33:08","guid":{"rendered":"https:\/\/max-drake.cc\/?p=4263"},"modified":"2018-08-07T10:22:49","modified_gmt":"2018-08-07T10:22:49","slug":"web-data3-part-1-google-sheets-api-to-web-page","status":"publish","type":"post","link":"https:\/\/max-drake.cc\/?p=4263","title":{"rendered":"Web Data3- Part 1. Google Sheets API to Web Page"},"content":{"rendered":"<p>Getting data from the web and being able to manipulate it and display it on your own website.<\/p>\n<p>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.<\/p>\n<p>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.<\/p>\n<p>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.<\/p>\n<p>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.<\/p>\n<p>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.<\/p>\n<p>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.<\/p>\n<h3>Resources<\/h3>\n<p>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:<\/p>\n<p>The first 2 set up reading from google sheets to Web, the 3rd is how to write from the web to Google Sheets. <a href=\"https:\/\/www.youtube.com\/channel\/UCm5pREiVM6hwsZKqJww8XuA\" target=\"_blank\" rel=\"noopener\">Anthony Brunson&#8217;s tutorials<\/a> 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.<\/p>\n<p><iframe title=\"Google Sheets API - Read Data - Part 1\" width=\"678\" height=\"381\" data-src=\"https:\/\/www.youtube.com\/embed\/shctaaILCiU?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe><\/p>\n<p><iframe title=\"Google Sheets API - Read Data - Part 2\" width=\"678\" height=\"381\" data-src=\"https:\/\/www.youtube.com\/embed\/GFlgRWirxaM?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe><\/p>\n<p><iframe title=\"Google Sheets API - Read and Write Data\" width=\"678\" height=\"381\" data-src=\"https:\/\/www.youtube.com\/embed\/CG2Ien4X2BI?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe><\/p>\n<h2>Process<\/h2>\n<p>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.<\/p>\n<p>I&#8217;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.<\/p>\n<p>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.<img decoding=\"async\" class=\"wp-image-4271 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im01-1-1024x971.jpg\" alt=\"\" width=\"1514\" height=\"1436\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im01-1-1024x971.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im01-1-300x284.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im01-1-768x728.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im01-1.jpg 1953w\" data-sizes=\"(max-width: 1514px) 100vw, 1514px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1514px; --smush-placeholder-aspect-ratio: 1514\/1436;\" \/><\/p>\n<p>This data is static at the moment and is not being refreshed, I&#8217;ll do that later.<\/p>\n<p>We&#8217;ll go now to Google Sheets \/API v4\/ Reference Tab :<\/p>\n<p><a href=\"https:\/\/developers.google.com\/sheets\/api\/reference\/rest\/\" target=\"_blank\" rel=\"noopener\">https:\/\/developers.google.com\/sheets\/api\/reference\/rest\/<\/a><\/p>\n<p><img decoding=\"async\" class=\"alignnone wp-image-4273 lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im02-1-1024x620.jpg\" alt=\"\" width=\"1508\" height=\"914\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im02-1-1024x620.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im02-1-300x182.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im02-1-768x465.jpg 768w\" data-sizes=\"(max-width: 1508px) 100vw, 1508px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1508px; --smush-placeholder-aspect-ratio: 1508\/914;\" \/><\/p>\n<p>We are looking for SPREADSHEET VALUES\u00a0 &amp; GET (request):<\/p>\n<p><a href=\"https:\/\/developers.google.com\/sheets\/api\/reference\/rest\/v4\/spreadsheets.values\/get\" target=\"_blank\" rel=\"noopener\">https:\/\/developers.google.com\/sheets\/api\/reference\/rest\/v4\/spreadsheets.values\/get<\/a><\/p>\n<p>To the right of the page there is &#8220;TRY THIS API&#8221; panel. It has 2 red highlighted boxes of data you have to supply. In this case, the spreadsheetID and the range.<\/p>\n<p><img decoding=\"async\" class=\"wp-image-4279 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im03-1-1024x634.jpg\" alt=\"\" width=\"1513\" height=\"937\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im03-1-1024x634.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im03-1-300x186.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im03-1-768x476.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im03-1-200x125.jpg 200w\" data-sizes=\"(max-width: 1513px) 100vw, 1513px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1513px; --smush-placeholder-aspect-ratio: 1513\/937;\" \/><\/p>\n<p>We have the spreadsheetID from the URL in the browser to the Google Sheet &amp; 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.<\/p>\n<p><img decoding=\"async\" class=\"wp-image-4278 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im04-1-1024x636.jpg\" alt=\"\" width=\"1474\" height=\"915\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im04-1-1024x636.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im04-1-300x186.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im04-1-768x477.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im04-1-200x125.jpg 200w\" data-sizes=\"(max-width: 1474px) 100vw, 1474px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1474px; --smush-placeholder-aspect-ratio: 1474\/915;\" \/><\/p>\n<p>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.<\/p>\n<p><img decoding=\"async\" class=\"wp-image-4277 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im05-1-1024x636.jpg\" alt=\"\" width=\"1456\" height=\"904\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im05-1-1024x636.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im05-1-300x186.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im05-1-768x477.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im05-1-200x125.jpg 200w\" data-sizes=\"(max-width: 1456px) 100vw, 1456px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1456px; --smush-placeholder-aspect-ratio: 1456\/904;\" \/><\/p>\n<p>It then asks for permissions to let the Get request go in and look and grab things in the following areas, Google Drive, &amp; Google Spreadsheets. click allow and the Get request executes.<\/p>\n<p><img decoding=\"async\" class=\"wp-image-4276 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im06-1024x609.jpg\" alt=\"\" width=\"1464\" height=\"870\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im06-1024x609.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im06-300x178.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im06-768x457.jpg 768w\" data-sizes=\"(max-width: 1464px) 100vw, 1464px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1464px; --smush-placeholder-aspect-ratio: 1464\/870;\" \/><\/p>\n<p>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.<\/p>\n<p><img decoding=\"async\" class=\"wp-image-4275 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im07-1024x599.jpg\" alt=\"\" width=\"1523\" height=\"892\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im07-1024x599.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im07-300x175.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im07-768x449.jpg 768w\" data-sizes=\"(max-width: 1523px) 100vw, 1523px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1523px; --smush-placeholder-aspect-ratio: 1523\/892;\" \/><\/p>\n<p>If you try doing this in Postman with the same Get request, the Cors blocks it because it isn&#8217;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&#8217;t authorised to look at the Google Sheet.<\/p>\n<p><img decoding=\"async\" class=\"wp-image-4274 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im08-1024x783.jpg\" alt=\"\" width=\"1493\" height=\"1141\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im08-1024x783.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im08-300x229.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im08-768x587.jpg 768w\" data-sizes=\"(max-width: 1493px) 100vw, 1493px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1493px; --smush-placeholder-aspect-ratio: 1493\/1141;\" \/><\/p>\n<p>So, scroll down on the spreadsheet.values.get page and you&#8217;ll see some examples. I will use the browser example and copy\/paste the code into Notepad++<\/p>\n<p><img decoding=\"async\" class=\"wp-image-4281 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im09-1024x634.jpg\" alt=\"\" width=\"1472\" height=\"912\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im09-1024x634.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im09-300x186.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im09-768x476.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im09-200x125.jpg 200w\" data-sizes=\"(max-width: 1472px) 100vw, 1472px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1472px; --smush-placeholder-aspect-ratio: 1472\/912;\" \/>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.<\/p>\n<p><img decoding=\"async\" class=\"wp-image-4280 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im10-895x1024.jpg\" alt=\"\" width=\"1473\" height=\"1686\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im10-895x1024.jpg 895w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im10-262x300.jpg 262w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im10-768x879.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im10.jpg 1652w\" data-sizes=\"(max-width: 1473px) 100vw, 1473px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1473px; --smush-placeholder-aspect-ratio: 1473\/1686;\" \/>\u00a0You 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:\/\/max-drake.cc\/ site and in the Credentials limited the use of the API key to only calls from this site.<\/p>\n<p>Now, the basic code, cut\/pasted from the <a href=\"https:\/\/developers.google.com\/sheets\/api\/reference\/rest\/v4\/spreadsheets.values\/get\" target=\"_blank\" rel=\"noopener\"><strong>spreadsheet.values.get page<\/strong><\/a> 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 &amp; SignOut button and once you click on the signIn button it requests you to sign in\u00a0 (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)) .<\/p>\n<p>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&#8217;t get in chrome.<\/p>\n<p><img decoding=\"async\" class=\"wp-image-4288 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im12-1022x1024.jpg\" alt=\"\" width=\"1562\" height=\"1564\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im12-1022x1024.jpg 1022w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im12-150x150.jpg 150w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im12-300x300.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im12-768x770.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im12.jpg 1347w\" data-sizes=\"(max-width: 1562px) 100vw, 1562px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1562px; --smush-placeholder-aspect-ratio: 1562\/1564;\" \/><\/p>\n<p><img decoding=\"async\" class=\"wp-image-4287 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im13-706x1024.jpg\" alt=\"\" width=\"1524\" height=\"2210\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im13-706x1024.jpg 706w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im13-207x300.jpg 207w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im13-768x1114.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im13.jpg 1277w\" data-sizes=\"(max-width: 1524px) 100vw, 1524px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1524px; --smush-placeholder-aspect-ratio: 1524\/2210;\" \/><\/p>\n<p>Once I did the additions to the code I got a error and an uncaught exception. It is on a later GET to :<\/p>\n<p><strong><span class=\"message-body-wrapper\"><span class=\"message-flex-body\"><span class=\"message-body devtools-monospace\"><span class=\"method\">GET\u00a0 <\/span><a class=\"url\" title=\"https:\/\/ssl.gstatic.com\/accounts\/o\/3723580519-idpiframe.js\">https:\/\/ssl.gstatic.com\/accounts\/o\/3723580519-idpiframe.js <\/a><\/span><\/span><\/span><\/strong><span class=\"message-body-wrapper\"><span class=\"message-flex-body\"><span class=\"message-body devtools-monospace\"><a class=\"url\" title=\"https:\/\/ssl.gstatic.com\/accounts\/o\/3723580519-idpiframe.js\">(so looking at another js file). <\/a><\/span><\/span><\/span><\/p>\n<p><img decoding=\"async\" class=\"wp-image-4286 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im14-1024x388.jpg\" alt=\"\" width=\"1572\" height=\"596\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im14-1024x388.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im14-300x114.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im14-768x291.jpg 768w\" data-sizes=\"(max-width: 1572px) 100vw, 1572px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1572px; --smush-placeholder-aspect-ratio: 1572\/596;\" \/> So that will need a bit more debugging.<\/p>\n<h3>Process so far<\/h3>\n<p>Quite limited success in the process. I can :<\/p>\n<ol>\n<li>get a json output only to CONSOLE so far, of the spreadsheet data.<\/li>\n<li>I still need to sign in via a button at the top of the page.<\/li>\n<li>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.<\/li>\n<li>Basic boilerplate code from example works but the tutorial add on&#8217;s\u00a0 to that code don&#8217;t at this point.<\/li>\n<\/ol>\n<p>Moving on, my next steps are :<\/p>\n<ol>\n<li>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).<\/li>\n<li>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.<\/li>\n<li>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&#8217;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) .<\/li>\n<\/ol>\n<p>So, link to\u00a0 basic PHP file that works is <a href=\"https:\/\/max-drake.cc\/data\/sheets\/index.php\" target=\"_blank\" rel=\"noopener\"><strong>here<\/strong>.<\/a> This displays the page, you have to look at page source to see the code.<\/p>\n<p>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&#8217;t highlight them.<\/p>\n<p>My learning process is to take some code, try and adapt it, then figure out what is happening.<\/p>\n<p>At the end of the process, I want to be able to display dynamic tables on the web page and some graphs too.\u00a0 Hopefully the next post will be able to do that.<\/p>\n<h2>Appendix. Failed attempts at connecting Google Sheets to Web<\/h2>\n<p>I had a couple of links at the end of the last post :<\/p>\n<p>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 <a href=\"https:\/\/www.experts-exchange.com\/questions\/28789880\/Google-Sheets-and-Datatables.html\" target=\"_blank\" rel=\"noopener\"><strong>this discussion<\/strong><\/a> about it.\u00a0 Also a <a href=\"https:\/\/jsfiddle.net\/EE_RainerJ\/2e5f1at8\/\" target=\"_blank\" rel=\"noopener\"><strong>link to a JSFiddle example<\/strong><\/a>. All for getting a more tidy output than an iFrame.\u00a0I 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).<\/p>\n<p>There is a<a href=\"http:\/\/blog.pamelafox.org\/2013\/06\/exporting-google-spreadsheet-as-json.html\" target=\"_blank\" rel=\"noopener\"><strong> post here on sheets to JSON<\/strong>\u00a0<\/a> (images wont show)\u00a0 with a link to a <a href=\"https:\/\/gist.github.com\/pamelafox\/1878143\" target=\"_blank\" rel=\"noopener\"><strong>Github JS code<\/strong><\/a> for the process. I then found this video that steps through using Pamela Fox&#8217;s code here:<\/p>\n<div align=\"center\">\n<p><iframe title=\"How To Export JSON From Google Sheets\" width=\"678\" height=\"381\" data-src=\"https:\/\/www.youtube.com\/embed\/HnFwl-8aTmQ?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe><\/p>\n<\/div>\n<p>This saves to a JSON file on your Drive that you can link to, I still get a cors error:<\/p>\n<p><em>Cross-Origin Request Blocked: The Same Origin Policy disallows reading the remote resource at https:\/\/&#8230;&#8230;&#8230; (Reason: CORS header \u2018Access-Control-Allow-Origin\u2019 missing).<\/em><\/p>\n<p>Even after making the file public for sharing on the web! So not sure how to deal with this.<\/p>\n<p>I thought this article &#8220;<a href=\"https:\/\/coderwall.com\/p\/duapqq\/use-a-google-spreadsheet-as-your-json-backend\" target=\"_blank\" rel=\"noopener\"><strong>Use a Google Spreadsheet as your JSON backend<\/strong><\/a>&#8221; was interesting as it gave a couple of different approaches but on testing neither of them worked for me.<\/p>\n<p>This <a href=\"https:\/\/ctrlq.org\/code\/20004-google-spreadsheets-json\" target=\"_blank\" rel=\"noopener\"><strong>article<\/strong> <\/a>has some simple code for testing, no date on the article unfortunately, and it doesn&#8217;t work either.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":4287,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35,3,32,29],"tags":[],"class_list":["post-4263","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-api_json","category-data-extraction","category-power-bi","category-web"],"featured_image_src":"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im13.jpg","featured_image_src_square":"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im13.jpg","author_info":{"display_name":"Max Drake","author_link":"https:\/\/max-drake.cc\/?author=1"},"_links":{"self":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts\/4263","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4263"}],"version-history":[{"count":0,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts\/4263\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/media\/4287"}],"wp:attachment":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4263"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4263"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4263"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}