{"id":4300,"date":"2018-08-07T22:07:58","date_gmt":"2018-08-07T22:07:58","guid":{"rendered":"https:\/\/max-drake.cc\/?p=4300"},"modified":"2018-08-09T11:24:39","modified_gmt":"2018-08-09T11:24:39","slug":"web-data-4-web-page-to-google-sheets-with-api","status":"publish","type":"post","link":"https:\/\/max-drake.cc\/?p=4300","title":{"rendered":"Web Data 4 &#8211; Web Page to Google Sheets Update with API"},"content":{"rendered":"<p>Following on from the previous post\u00a0 I want to try and be able to update a Google Sheet from a web page via the link.<\/p>\n<p>I will be following this video:<\/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<p>Unfortunately there is not a link to code for this addition, unlike videos 1 &amp; 2 so I&#8217;ll need to write it from looking at what is on the screen ( look at my comments at the bottom, code there).<\/p>\n<p>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 <span class=\"com\">&#8216;https:\/\/www.googleapis.com\/auth\/spreadsheets.<strong>readonly<\/strong>&#8216;<\/span> was chosen, but in this exercise, we will be writing to the sheets so the scope should be\u00a0 :<\/p>\n<pre class=\"prettyprint\"> <span class=\"com\">\/\/ TODO: Authorize using one of the following scopes:<\/span><span class=\"pln\">\r\n\u00a0 \u00a0 \u00a0 <\/span><span class=\"com\">\/\/ \u00a0 'https:\/\/www.googleapis.com\/auth\/drive'<\/span><span class=\"pln\">\r\n\u00a0 \u00a0 \u00a0 <\/span><span class=\"com\">\/\/ \u00a0 'https:\/\/www.googleapis.com\/auth\/drive.file'<\/span><span class=\"pln\">\r\n\u00a0 \u00a0 \u00a0 <\/span><span class=\"com\">\/\/ \u00a0 'https:\/\/www.googleapis.com\/auth\/drive.readonly'<\/span><span class=\"pln\">\r\n\u00a0 \u00a0 \u00a0 <\/span><span class=\"com\">\/\/ \u00a0 'https:\/\/www.googleapis.com\/auth\/spreadsheets'<\/span><span class=\"pln\">\r\n\u00a0 \u00a0 \u00a0 <\/span><span class=\"com\">\/\/ \u00a0 'https:\/\/www.googleapis.com\/auth\/spreadsheets.readonly'<\/span><span class=\"pln\">\r\n<strong>\u00a0 \u00a0 \u00a0 <\/strong><\/span><strong><span class=\"kwd\">var<\/span><span class=\"pln\"> SCOPE <\/span><span class=\"pun\">=<\/span> <span class=\"str\"><span class=\"com\">'https:\/\/www.googleapis.com\/auth\/spreadsheets'<\/span><\/span>\r\n<\/strong>\r\n<\/pre>\n<p>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.<\/p>\n<p>You need to add another function:<\/p>\n<p>function handleSaveClick() {<br \/>\nmakeApiCall(action=&#8221;write&#8221;);<br \/>\n}<\/p>\n<p>And another button:<\/p>\n<p>&lt;button id=&#8221;save-button&#8221; onclick=&#8221;handleSaveClick()&#8221;&gt;Save&lt;\/button&gt;<\/p>\n<p>and then it all works.<\/p>\n<h2>Process<\/h2>\n<p>For this exercise I&#8217;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.<\/p>\n<p>Here is the link to the spreadsheet:<\/p>\n<p>https:\/\/docs.google.com\/spreadsheets\/d\/15tH8Tw8n-RhLsngG5ZLLgZ5CneEwBok&#8211;ugvzEictSM\/edit?usp=sharing<\/p>\n<p>Here is the link to the webpage php file (right hand click on page to get the code):<\/p>\n<p>https:\/\/max-drake.cc\/data\/sheets\/indexBackToSheets.php<\/p>\n<p><img decoding=\"async\" class=\"wp-image-4305 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im19-1024x474.jpg\" alt=\"\" width=\"1682\" height=\"779\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im19-1024x474.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im19-300x139.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im19-768x356.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im19.jpg 1453w\" data-sizes=\"(max-width: 1682px) 100vw, 1682px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1682px; --smush-placeholder-aspect-ratio: 1682\/779;\" \/><\/p>\n<p><img decoding=\"async\" class=\"wp-image-4304 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im20-1024x690.jpg\" alt=\"\" width=\"1461\" height=\"985\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im20-1024x690.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im20-300x202.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im20-768x518.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im20.jpg 1668w\" data-sizes=\"(max-width: 1461px) 100vw, 1461px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1461px; --smush-placeholder-aspect-ratio: 1461\/985;\" \/><\/p>\n<p><img decoding=\"async\" class=\"wp-image-4303 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im21-1024x683.jpg\" alt=\"\" width=\"1535\" height=\"1023\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im21-1024x683.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im21-300x200.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im21-768x512.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im21.jpg 1676w\" data-sizes=\"(max-width: 1535px) 100vw, 1535px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1535px; --smush-placeholder-aspect-ratio: 1535\/1023;\" \/><\/p>\n<p>An interesting thing in this is that\u00a0 in the <strong>ValueInputOption<\/strong> the data is &#8216;RAW&#8217;. This was mentioned in the video by Anthony.<\/p>\n<p>1. In spreadsheet column A we will make DataType &#8220;Number&#8221;, you see that it becomes Right Justified in the cell. (I tested using SUM() and it added cells together in column A).<\/p>\n<p>2. On Web it becomes Left Justified, DataType &#8220;Text&#8221;. So you cannot use the data for number formulas. When sending data back from Web to Spreadsheet it converts the spreadsheet to DataType &#8220;Text&#8221;. So you cannot do any number manipulation with it.<\/p>\n<p><img decoding=\"async\" class=\"wp-image-4306 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im22-1024x682.jpg\" alt=\"\" width=\"1473\" height=\"982\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im22-1024x682.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im22-300x200.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im22-768x512.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im22.jpg 1679w\" data-sizes=\"(max-width: 1473px) 100vw, 1473px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1473px; --smush-placeholder-aspect-ratio: 1473\/982;\" \/><\/p>\n<p>looking at the <a href=\"https:\/\/developers.google.com\/sheets\/api\/reference\/rest\/v4\/ValueInputOption\" target=\"_blank\" rel=\"noopener\">ValueInputOption<\/a> page you only seem to have 2 options, see below ( Note, why has it a default that doesn&#8217;t work???).<\/p>\n<p><img decoding=\"async\" class=\"wp-image-4307 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im23-1024x390.jpg\" alt=\"\" width=\"1600\" height=\"609\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im23-1024x390.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im23-300x114.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im23-768x293.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im23.jpg 1372w\" data-sizes=\"(max-width: 1600px) 100vw, 1600px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1600px; --smush-placeholder-aspect-ratio: 1600\/609;\" \/><\/p>\n<p>After changing ValueInputOption from &#8220;Raw&#8221; to &#8220;User_Entered&#8221;:<\/p>\n<p><img decoding=\"async\" class=\"wp-image-4310 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im24-1024x694.jpg\" alt=\"\" width=\"1534\" height=\"1041\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im24-1024x694.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im24-300x203.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im24-768x521.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im24.jpg 1661w\" data-sizes=\"(max-width: 1534px) 100vw, 1534px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1534px; --smush-placeholder-aspect-ratio: 1534\/1041;\" \/><\/p>\n<p><img decoding=\"async\" class=\"wp-image-4309 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im25-1024x695.jpg\" alt=\"\" width=\"1478\" height=\"1003\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im25-1024x695.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im25-300x203.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im25-768x521.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im25.jpg 1666w\" data-sizes=\"(max-width: 1478px) 100vw, 1478px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1478px; --smush-placeholder-aspect-ratio: 1478\/1003;\" \/><\/p>\n<p><img decoding=\"async\" class=\"wp-image-4308 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im26-1024x692.jpg\" alt=\"\" width=\"1463\" height=\"988\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im26-1024x692.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im26-300x203.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im26-768x519.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im26.jpg 1661w\" data-sizes=\"(max-width: 1463px) 100vw, 1463px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1463px; --smush-placeholder-aspect-ratio: 1463\/988;\" \/> So\u00a0 a &#8220;Number&#8221; formatted column in the spreadsheet transfers and is displayed as a &#8220;Text&#8221; formatted column on the Web Page. When it is posted back to the Spreadsheet it converts back to a &#8220;Number&#8221; 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.<\/p>\n<h2>End Comment<\/h2>\n<p>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 &amp; 2\/ being able to update the database. A CRUD process<\/p>\n<p>(Create\/Read\/Update\/Delete) but in a more toned down manner using spreadsheets and web pages.<\/p>\n<p>In a database interface you&#8217;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.\u00a0 The point I&#8217;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&#8217;d walk away from databases. They would be prepared to get their hands dirty with spreadsheets.<\/p>\n<p>An interesting part of this is the ValueInputOption = &#8220;User_Entered&#8221;. In this way, formulae will work on the pushed\/pulled data.<\/p>\n<p>This process, as its JavaScript, is temperamental. It takes a while to get it set up, but once setup its pretty robust.<\/p>\n<p>As I&#8217;m not familiar with Google Sheets, I&#8217;m not sure what the Excel VBA macro equivalent is (maybe &#8220;scripts&#8221;?) . From interwongling I get <strong><a href=\"https:\/\/developers.google.com\/apps-script\/overview\" target=\"_blank\" rel=\"noopener\">this <\/a><\/strong>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\u00a0 JS code.<\/p>\n<h3>Google Sheets API Methods<\/h3>\n<h2><img decoding=\"async\" class=\"wp-image-4311 alignleft lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im27-395x1024.jpg\" alt=\"\" width=\"147\" height=\"381\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im27-395x1024.jpg 395w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im27-116x300.jpg 116w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im27.jpg 464w\" data-sizes=\"(max-width: 147px) 100vw, 147px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 147px; --smush-placeholder-aspect-ratio: 147\/381;\" \/><\/h2>\n<p>So far, I&#8217;ve only been using the GET &amp; UPDATE requests of the Google Sheets API. There are a few more to explore (red arrows are the ones I&#8217;ve used so far):<\/p>\n<h2>security <strong>OAuth2<\/strong> issues<\/h2>\n<p>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.<\/p>\n<p>In the next post I will need to try and get my head around the authentication process. Maybe the power of the Authentication process\u00a0 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.<\/p>\n<p>I have found a couple of interesting areas to explore regarding the logging in to the sites with <strong><a href=\"https:\/\/developers.google.com\/identity\/protocols\/OAuth2\" target=\"_blank\" rel=\"noopener\">Google developers<\/a> OAuth2<\/strong> and <a href=\"https:\/\/developers.google.com\/oauthplayground\/\" target=\"_blank\" rel=\"noopener\"><strong>a playground<\/strong> <\/a>for exploring how to automate the web access to the google Sheet.<\/p>\n<h3>A daft exercise<\/h3>\n<p>I was really happy when I got this up and running,. I thought wouldn&#8217;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&#8217;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.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Following on from the previous post\u00a0 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 &amp; 2 so I&#8217;ll need to write it from looking [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":4310,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35,3,32,1,29],"tags":[],"class_list":["post-4300","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-api_json","category-data-extraction","category-power-bi","category-general","category-web"],"featured_image_src":"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im24.jpg","featured_image_src_square":"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/08\/im24.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\/4300","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=4300"}],"version-history":[{"count":0,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts\/4300\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/media\/4310"}],"wp:attachment":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4300"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4300"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4300"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}