tag:blogger.com,1999:blog-14956461176075165822024-03-13T08:05:27.828-07:00kutil.orgGoogle Developer Expert writes about Google Apps Script, Google Cloud Platform, machine learning, internet of things, ..Ivan Kutilhttp://www.blogger.com/profile/10820026974853553039noreply@blogger.comBlogger17125tag:blogger.com,1999:blog-1495646117607516582.post-18837319672354421552022-08-23T12:18:00.002-07:002022-08-23T12:18:38.050-07:00List all GCP regions with Google (unofficial) API endpoint<p>I have several scenarios where I needed to list all GCP regions. (e.g. Cloud Billing API <a href="https://cloud.google.com/billing/docs/reference/rest">https://cloud.google.com/billing/docs/reference/rest</a>).</p><p></p><div class="separator" style="clear: both; text-align: center;"><br /></div><br /><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqpVIcQqZxaZPk1XsM24SzReg1S0GNlAxQKY8RHPzTfFalN49vtIUo8IYKzy8NrLLGCjAArug8NVmly8Ms3_FHbyRsOy8XMyfOFqDCJQlxpEIxelnfKRyS7hVZXwk-9w_Iomt46lT3Im6andFmH_M7APf9TgshK27zcJ87PWqTsV1TBEV1npKxSg/s1438/regions.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="464" data-original-width="1438" height="169" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqpVIcQqZxaZPk1XsM24SzReg1S0GNlAxQKY8RHPzTfFalN49vtIUo8IYKzy8NrLLGCjAArug8NVmly8Ms3_FHbyRsOy8XMyfOFqDCJQlxpEIxelnfKRyS7hVZXwk-9w_Iomt46lT3Im6andFmH_M7APf9TgshK27zcJ87PWqTsV1TBEV1npKxSg/w525-h169/regions.png" width="525" /></a></div><br /><p>I was surprised that there is no API for that. </p><p>When you try to search "list of GCP regions" you will end with one of the top results to documentation e.g. <a href="https://cloud.google.com/compute/docs/regions-zones ">https://cloud.google.com/compute/docs/regions-zones </a>or <a href="https://cloud.google.com/about/locations">https://cloud.google.com/about/locations</a>. It is not suitable for programmatic access.</p><p>I have found recently an endpoint (not API!) with the list of IP ranges for each GCP regions</p><p><b>https://www.gstatic.com/ipranges/cloud.json</b></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgG6g-JsLSooSeqwp_gK6iv-Q56VxAalPXg6a6BVt43AdJUzftbQGAT9RwianrzTJd0rbUGwzhtz6dNETPKSv8uZ2sPX953sG3pfxbcFOkRxudm8E8YrmURyAmJBvNrbV3-LdGw4w9DXKR9hex3C2u95LVfEWVwAzxrd4qLl12Zipq9Ha98W6GymQ/s1150/Screenshot%202022-08-23%20at%2021.12.57.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1150" data-original-width="872" height="370" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgG6g-JsLSooSeqwp_gK6iv-Q56VxAalPXg6a6BVt43AdJUzftbQGAT9RwianrzTJd0rbUGwzhtz6dNETPKSv8uZ2sPX953sG3pfxbcFOkRxudm8E8YrmURyAmJBvNrbV3-LdGw4w9DXKR9hex3C2u95LVfEWVwAzxrd4qLl12Zipq9Ha98W6GymQ/w281-h370/Screenshot%202022-08-23%20at%2021.12.57.png" width="281" /></a></div><div class="separator" style="clear: both; text-align: center;"><span style="text-align: left;"><br /></span></div><div class="separator" style="clear: both; text-align: center;"><span style="text-align: left;">That was the last piece of the puzzle to create my desired function.</span></div><div class="separator" style="clear: both; text-align: center;"><span style="text-align: left;"><br /></span></div><div class="separator" style="clear: both; text-align: center;"><span style="text-align: left;">Here is a snippet for Google Apps Script:</span></div><p></p><div><br /></div>
<script src="https://gist.github.com/codeas/e8f11b062659fe7758f970b664b2498b.js"></script>Ivan Kutilhttp://www.blogger.com/profile/10820026974853553039noreply@blogger.comtag:blogger.com,1999:blog-1495646117607516582.post-34383019106326029992022-02-25T08:40:00.005-08:002022-02-25T08:47:00.600-08:00Get filtered rows in Google Sheets with Google Apps Script<p>Google Sheets allows you to filter data in grid. <br /><br />Sometimes you need to filter and use data with API.<br />https://developers.google.com/sheets/api<br /><br /> Google Sheets API has endpoint with array <b>rowMetadata. <br /></b><br />You can iterate over the all rows and check if property hiddenByFilter is setup.</p><p></p><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgpVc8FGGcFNv3MoTbKSej16LTFNhqVESKzBvPNimecKMXjqvlC5YE7p8f8wu7y6Scc78F5FGeq_fFNFkdEVcMiThEj2BltAedC0wJ07XTw5MB78xpXi2aqMNayfbLGLg1ErBeeAuIxDaiZu3JJbjcJl-bAUjv96GtFUpoSmpZFmn30zgEdrNJ-SQ" style="margin-left: 1em; margin-right: 1em;"><img data-original-height="1126" data-original-width="2490" height="290" src="https://blogger.googleusercontent.com/img/a/AVvXsEgpVc8FGGcFNv3MoTbKSej16LTFNhqVESKzBvPNimecKMXjqvlC5YE7p8f8wu7y6Scc78F5FGeq_fFNFkdEVcMiThEj2BltAedC0wJ07XTw5MB78xpXi2aqMNayfbLGLg1ErBeeAuIxDaiZu3JJbjcJl-bAUjv96GtFUpoSmpZFmn30zgEdrNJ-SQ=w640-h290" width="640" /></a></div><br /><p></p><p>Here is a snippet how to get filtered rows with Google Apps Script</p>
<script src="https://gist.github.com/codeas/dc0c3ac492b3ab24e243cb301e82fba2.js"></script>
<p><br /></p>Ivan Kutilhttp://www.blogger.com/profile/10820026974853553039noreply@blogger.comtag:blogger.com,1999:blog-1495646117607516582.post-38042576607204737232020-12-07T09:12:00.001-08:002020-12-07T09:12:41.412-08:0016 secrets tips, tricks and features for new Google Apps Script Editor (v2020)Google Apps Script has a new editor, which is better, nicer, and completed ready for future new features.<br />Today, I would like to introduce you to several <i>dirty</i> & secret tricks, what you can do.<br />(All shortcuts have been tested on MacOS)<br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsIYxoA46HoBtoAZXiSpjZRNukGhR8jHzq8nD5APIxZ2CCbE-NZ7uY5TY103nupvQYK3Cfom1uwHUrx80hAopHXbnqAdPf_XsI8CwQe9c3isUVSo3j-DyzEU8syH6vKg_000nPVOXyOw/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="2048" data-original-width="2048" height="219" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsIYxoA46HoBtoAZXiSpjZRNukGhR8jHzq8nD5APIxZ2CCbE-NZ7uY5TY103nupvQYK3Cfom1uwHUrx80hAopHXbnqAdPf_XsI8CwQe9c3isUVSo3j-DyzEU8syH6vKg_000nPVOXyOw/w219-h219/image.png" width="219" /></a></div><br /><h3 style="text-align: left;">#1 Change order of your files in Apps Script project</h3><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDGGgq7SfXiL5ce56vWcJ6nFwa1sm_UCPdWOL4b62gEHdKUYXR2TW1tKyIoKwR9XlMGVfZP3kKDxm-j3XSplZ89QeOK2U0IHKn8sFWnhzNf0RTMWdLW1E32jHgi36lzWPQ6zttSchLqQ/s431/move+up.gif" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="431" data-original-width="421" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDGGgq7SfXiL5ce56vWcJ6nFwa1sm_UCPdWOL4b62gEHdKUYXR2TW1tKyIoKwR9XlMGVfZP3kKDxm-j3XSplZ89QeOK2U0IHKn8sFWnhzNf0RTMWdLW1E32jHgi36lzWPQ6zttSchLqQ/w391-h400/move+up.gif" width="391" /></a></div><br /><h3>#2 When you click save, all project's files are saved</h3><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsO38ovD1xoD2hKl5ZO32hkjgsFhJK_iVhfeT9G19dVu4N9hG821I1w5tvPnqn2678NiVjiQQ8reNf0842rvWb3uHGadfz31RBP9FnqEX8HOnDhtdrgb_eVF0CG5bnzlaP0b8z_orMFQ/s301/gas+all+save.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="229" data-original-width="301" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsO38ovD1xoD2hKl5ZO32hkjgsFhJK_iVhfeT9G19dVu4N9hG821I1w5tvPnqn2678NiVjiQQ8reNf0842rvWb3uHGadfz31RBP9FnqEX8HOnDhtdrgb_eVF0CG5bnzlaP0b8z_orMFQ/s0/gas+all+save.png" /></a></div><br /></div><h3 style="text-align: left;">#3 Show / hide not used code in function(s) with small arrow</h3><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh5g2LfUoTA4U08aowFsLcGVPrmADWCX2r_ivDh0zC3QipwRX8utIRgnv_GXeU8vAFxDU_VIFQJuw6xG-Gsd1BqWlsRu_TVw4Wv9D2zdkaLVT8t56Cs2GDUyRyVa9cHWJkO3NNqtg8dpQ/s884/gas+show+hide.gif" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="534" data-original-width="884" height="241" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh5g2LfUoTA4U08aowFsLcGVPrmADWCX2r_ivDh0zC3QipwRX8utIRgnv_GXeU8vAFxDU_VIFQJuw6xG-Gsd1BqWlsRu_TVw4Wv9D2zdkaLVT8t56Cs2GDUyRyVa9cHWJkO3NNqtg8dpQ/w400-h241/gas+show+hide.gif" width="400" /></a></div><br /><div><h3>#4 Exchange two lines with <span style="background-color: #d0e0e3;">[ALT]</span> + [UP] or [ALT] + [DOWN] </h3></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjzREWj5MBFxC7VzThGm24G4f21L3sl6QuHhfhhSqMW-I7EbCGHVi_CHzU6vQh9qwwMsvy6CyXOGoNj1PCS9FBOgp_F0SaSoQ_oVKD4AzLKHJDXfVebOw2laZxFjHO9-sHFKAfcpuuaGw/s906/gas+change+two+lines.gif" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="278" data-original-width="906" height="122" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjzREWj5MBFxC7VzThGm24G4f21L3sl6QuHhfhhSqMW-I7EbCGHVi_CHzU6vQh9qwwMsvy6CyXOGoNj1PCS9FBOgp_F0SaSoQ_oVKD4AzLKHJDXfVebOw2laZxFjHO9-sHFKAfcpuuaGw/w400-h122/gas+change+two+lines.gif" width="400" /></a></div><h3>#5 Copy line below or above with [ALT] + [SHIFT]+ [UP] or [ALT] + [SHIFT] + [DOWN] </h3><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgu5uUVQTN7IU67t5cY4IR5wXg2CtQkVJTcfKXXRnJr9evIdjoOl2HXJ0An4dGEpo-uwMymB6GowsHKenjxwILVtFXwucn07f8fEqQqMpW8cD58H2mOywWWBrBzkGKB94Ch0oSMHAdUKA/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="338" data-original-width="1682" height="128" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgu5uUVQTN7IU67t5cY4IR5wXg2CtQkVJTcfKXXRnJr9evIdjoOl2HXJ0An4dGEpo-uwMymB6GowsHKenjxwILVtFXwucn07f8fEqQqMpW8cD58H2mOywWWBrBzkGKB94Ch0oSMHAdUKA/w640-h128/gas+copy+lines.gif" width="640" /></a></div><br /><br /></div><div><h3>#6 Expand or shrink block selection with [CTRL] + [SHIFT]+[CMD]+ [LEFT] or [CTRL] + [SHIFT]+[CMD]+ [RIGHT] </h3></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhP_Z4lWdURQBTFEbX4azFrMXli9bf1wjBwnL8sT4iEy9ksLNW-tT1FoH8HmMkR30yBUPq7ToFip35Mv6VNSw7dvE-ljaXjIXcxUHhlrDVdL9GL1ZxS7LxB1FDg6pPoOrroggyhb02anQ/s1682/gas+expand+selection.gif" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="338" data-original-width="1682" height="128" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhP_Z4lWdURQBTFEbX4azFrMXli9bf1wjBwnL8sT4iEy9ksLNW-tT1FoH8HmMkR30yBUPq7ToFip35Mv6VNSw7dvE-ljaXjIXcxUHhlrDVdL9GL1ZxS7LxB1FDg6pPoOrroggyhb02anQ/w640-h128/gas+expand+selection.gif" width="640" /></a></div><br /><div><h3>#7 Multi-cursor for editing more lines together [ALT] + click to position</h3></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJpPlH61dk8w3VpYLJYCHRKHOVJj8_vwXG6X-CME1shWNCiAqi1Lbqj2bjZDne5PccI16IAHZPeZ4VVx6MST4ItYtWvuOo-K31OKsySH0CFsFnOTLh-Cp3POBPCA2hsheYfB-pd7aljg/s800/gas+multiline.gif" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="316" data-original-width="800" height="158" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJpPlH61dk8w3VpYLJYCHRKHOVJj8_vwXG6X-CME1shWNCiAqi1Lbqj2bjZDne5PccI16IAHZPeZ4VVx6MST4ItYtWvuOo-K31OKsySH0CFsFnOTLh-Cp3POBPCA2hsheYfB-pd7aljg/w400-h158/gas+multiline.gif" width="400" /></a></div><br /><div><h3>#8 Better code refactoring with Rename symbol, which replaces all findings</h3></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijr5FeEFLQWNhYG7dguIWCwv7V47V4eH90ot8Anx9K_ygdOkBFHT9t4f8anTggNKx_sMVy9Ve_6QQx3TbJ1ta-zdUmWQLbNDSuaaDBD2QgPTOAlkETCP1ARN31NwBSq-T5JuKmTHHwAA/s938/gas+rename+symbol.gif" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="666" data-original-width="938" height="284" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijr5FeEFLQWNhYG7dguIWCwv7V47V4eH90ot8Anx9K_ygdOkBFHT9t4f8anTggNKx_sMVy9Ve_6QQx3TbJ1ta-zdUmWQLbNDSuaaDBD2QgPTOAlkETCP1ARN31NwBSq-T5JuKmTHHwAA/w400-h284/gas+rename+symbol.gif" width="400" /></a></div><br /><div><h3>#9 Peek definition is best quick insight about function definition</h3></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgoh0a_qjHv-v2PJsQqfX6JPdVoPqO3rBvT06PeLIhbBKeaROqebKqA0Sd6fZl4g-P8buPgGBXSvc9S8lWv3dCpfKQSRvswOiglmdZLVkk6eAdbYbu0mWoVjQGHfdBTBP92HCzEDqcGqw/s1550/gas+peek.gif" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1032" data-original-width="1550" height="266" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgoh0a_qjHv-v2PJsQqfX6JPdVoPqO3rBvT06PeLIhbBKeaROqebKqA0Sd6fZl4g-P8buPgGBXSvc9S8lWv3dCpfKQSRvswOiglmdZLVkk6eAdbYbu0mWoVjQGHfdBTBP92HCzEDqcGqw/w400-h266/gas+peek.gif" width="400" /></a></div><br /><div><h3>#10 Code suggestion for native JavaScript objects (e.g. Date) is available</h3></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvm9Sj5ZKjKPoPdqWeyTDjntkE0vaDUYHDmMwUOvgVFOY96vThH8d5VL34NuaEF8TJZfhDAzvBoeQOLqOTba8CUmhz9F7cGRQow7IlfNZO98q__IKaPu_eouVApeSGbKPn8fhp1ToQFA/s796/gas+suggest.gif" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="404" data-original-width="796" height="203" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvm9Sj5ZKjKPoPdqWeyTDjntkE0vaDUYHDmMwUOvgVFOY96vThH8d5VL34NuaEF8TJZfhDAzvBoeQOLqOTba8CUmhz9F7cGRQow7IlfNZO98q__IKaPu_eouVApeSGbKPn8fhp1ToQFA/w400-h203/gas+suggest.gif" width="400" /></a></div><br /><div><h3>#11 Always visible panel with execution logs</h3></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQGJMMi9Vtfdw7W2hhfJgU3ZKtu4IWudDBsa2UL1tHeuxEztGya8ws-OmxYomS1wbB_VawQaQIVZbTEVuciVPgdK-l57bFvPDmI8_HkdMxmIAx1RhtdDLmktT91xBwGVlQVvpapuo7Wg/s1286/gas+panel.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="640" data-original-width="1286" height="318" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQGJMMi9Vtfdw7W2hhfJgU3ZKtu4IWudDBsa2UL1tHeuxEztGya8ws-OmxYomS1wbB_VawQaQIVZbTEVuciVPgdK-l57bFvPDmI8_HkdMxmIAx1RhtdDLmktT91xBwGVlQVvpapuo7Wg/w640-h318/gas+panel.png" width="640" /></a></div><br /><div><br /></div><div><h3 style="text-align: left;">#12 Different colors console.warn() and console.error()</h3><a href="https://draft.blogger.com/#"><img height="493" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6HvD3UkwOe00kkqG6ClAUq85KIz-AZIy4pFZ1re-dFo5DArzBCh8hIytgGT5-wGpytFXB-1FSQa9GVtSm-gi5fyvmsv9M0MMzgMfawsCf_udLrNJR6jdRCCKQMcA2cv1eiLz2jrcVpA/w640-h493/image.png" width="640" /></a><br /><br /><br /><h3>#13 Hyperlink in logging console leads to code line </h3><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiMpajuKHBrrCPkeshxFC_6NIsakFyO5CtyQXtRNaFoMCEHMpYvPTibwfGtP5yL-s1roYmIOrSRR8swqCLoOfu5KUztGym4Hm36LlALg6ZvqFPq0NxbnCqoYB6_S-2RFVibjbcJnZcvAA/s517/gas+bug.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="169" data-original-width="517" height="131" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiMpajuKHBrrCPkeshxFC_6NIsakFyO5CtyQXtRNaFoMCEHMpYvPTibwfGtP5yL-s1roYmIOrSRR8swqCLoOfu5KUztGym4Hm36LlALg6ZvqFPq0NxbnCqoYB6_S-2RFVibjbcJnZcvAA/w400-h131/gas+bug.png" width="400" /></a></div><div><h3>#14 Better debugging to deeper inspect of function callstack and variables (local and global scope)</h3></div></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNwWHVojwgnMv6tl4htHvrXwqriyzInHz5q5AAJ4nI4Eb9bPHDkRUBaYnSZIpIwhZjj5zJicnMPgstL3cyJa5TooteVO9Yk-PkCHst7c_XAiMzLPZhicWqFPCrU7KILTpucBW5azgoUQ/s1498/gas+debugging.gif" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1144" data-original-width="1498" height="488" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNwWHVojwgnMv6tl4htHvrXwqriyzInHz5q5AAJ4nI4Eb9bPHDkRUBaYnSZIpIwhZjj5zJicnMPgstL3cyJa5TooteVO9Yk-PkCHst7c_XAiMzLPZhicWqFPCrU7KILTpucBW5azgoUQ/w640-h488/gas+debugging.gif" width="640" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;"><div class="separator" style="clear: both; text-align: left;"><h3>#15 Change editor into dark-mode with command </h3></div></blockquote><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUeS2vO3L6PvxgL-T8EZ2bpWeYH39MvgS_ijsq7nnZPfixlFxhp1DniOxhbjpjMgKjiJ8cvtcXmczInErHmuPcngEaK_PXHKw5lCsWfztVgKMv2a2FMpLiTQroEzbn7oO7zessmlKkUA/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="928" data-original-width="1304" height="456" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUeS2vO3L6PvxgL-T8EZ2bpWeYH39MvgS_ijsq7nnZPfixlFxhp1DniOxhbjpjMgKjiJ8cvtcXmczInErHmuPcngEaK_PXHKw5lCsWfztVgKMv2a2FMpLiTQroEzbn7oO7zessmlKkUA/w640-h456/gas+ide+contrast.gif" width="640" /></a></div><div class="separator" style="clear: both; text-align: center;"> </div><p></p></div><h3>#16 Inline documentation with example in code suggestion</h3><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj44wGU_xv-2z-DJpbRS_ZEkFPc2g0AKNaAPAsOzpOh5qWl0YezybFYx8Ra5P52rFuyzrXDIYzRJm9Zga3Gw4lhzoDAPlUFTj8IvYd7Z30LdXe3mwzWFkM0xgLRmQW2f6JMkabCV334Ng/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="832" data-original-width="2012" height="264" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj44wGU_xv-2z-DJpbRS_ZEkFPc2g0AKNaAPAsOzpOh5qWl0YezybFYx8Ra5P52rFuyzrXDIYzRJm9Zga3Gw4lhzoDAPlUFTj8IvYd7Z30LdXe3mwzWFkM0xgLRmQW2f6JMkabCV334Ng/w640-h264/gas+documentation.gif" width="640" /></a></div><div><br /></div><div><br /></div><div>If you have your own favourite tip for new Apps Script editor, ping me on Twitter <a href="https://twitter.com/ivankutil">@ivankutil</a></div><div><br /></div><div><br /></div><br /><br /></div>Ivan Kutilhttp://www.blogger.com/profile/10820026974853553039noreply@blogger.comtag:blogger.com,1999:blog-1495646117607516582.post-70844141265554645932020-05-28T12:34:00.000-07:002020-05-29T06:47:47.711-07:00Machine learning in Google Sheet with Tensorflow.js and Google Apps Script<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
This article will show you how you can setup, train, and predict spreadsheet data with deep-learning framework Tensorflow.js. You don't need to call REST APIs or use other 3rd parties storage and algorithm. All your data stay in your secure Google Sheet. <br /><br /><b><span style="color: #38761d;">There is a Google Spreadsheet with demo dataset + full Apps Script inside at the end of article.</span></b></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<img height="480" src="https://docs.google.com/drawings/d/e/2PACX-1vRhknvLbCq4N0VytBnW_zmcHmG3v_O1VGQmA4pPf_LgopO2t0Bmyr1iWR3l-D35N8aLL4cZvJIHBXra/pub?w=960&h=720" width="640" />
</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<br /></div>
<h3 style="text-align: justify;">
Intro</h3>
<div style="text-align: justify;">
Google has recently introduced a new JavaScript runtime<b> (<a href="https://developers.google.com/apps-script/guides/v8-runtime">V8 engine)</a> </b>into Google Apps Script. It enhances G Suite platform for new use-cases of automation. It replaces the old Mozilla's Rhino JavaScript interpreter and allows you to include modern JavaScript libraries.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b><a href="https://www.tensorflow.org/">TensorFlow</a> </b>was originally for Python, but Google added support for more programming languages later. (nodejs. JavaScript, Swift,..). <a href="https://keras.io/">Keras</a> is high-level neural networks API that is on top of TensorFlow. It is appropriate for beginners and helps you to build neural networks. Tensorflow.js is JavaScript-based framework for building neural networks and syntax is similar to Keras.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
The whole machine learning topic is very complex. It contains a lot of use-cases, design of architectures, setups, and tiny tweaking. My aim is not to show you step-by-step tutorial which would cover machine learning, but inspire you and show you another point-of-view about the power of Google Sheets with Google Apps Script.<br />
<span style="color: orange;"><br />Disclaimer: I have used to<i> small hack,</i> to included Tensorflow.js library. I cannot guarantee, that you get 100% accuracy of result.</span></div>
<h3>
</h3>
<h3 style="text-align: justify;">
Use case</h3>
<div style="text-align: justify;">
I guess that you have plenty of data in your Google Sheets. Imagine the scenario, that based on some multiple columns (with numbers) you want to predict the value in the last column. It is useful if you want to forecast future values from past values or some values are missing and you can fill the gaps. The scenario is named <b>Multivariate Regression.</b></div>
<h3>
<div style="text-align: justify;">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDSiOPS1RtTUW4SKu22A6_G96AvEexM5F7nsQNKAlBC1fSNpC9WID5FS0hps2xUIUobSz1lpvcb6pt9x_BZyhLWwdJLDdtx5g9jFgCUcBcran5m4XFtgKIKJa2sGF5MjNrwqF-IxNxCA/s1600/Nulab-Gradient-descent-for-linear-regression-using-Golang-Blog.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="450" data-original-width="680" height="211" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDSiOPS1RtTUW4SKu22A6_G96AvEexM5F7nsQNKAlBC1fSNpC9WID5FS0hps2xUIUobSz1lpvcb6pt9x_BZyhLWwdJLDdtx5g9jFgCUcBcran5m4XFtgKIKJa2sGF5MjNrwqF-IxNxCA/s320/Nulab-Gradient-descent-for-linear-regression-using-Golang-Blog.png" width="320" /></a></div>
<br /></div>
<b><div style="text-align: justify;">
<b>Deploy Tensorflow.js in Google Apps Script</b></div>
</b></h3>
<div style="text-align: justify;">
I copied the whole Tensorflow.js library into one-file code into Google Apps Script project as file<b> tf-js.gs.</b> </div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUH5wRW3gvCCL4qchEXfwBTqaDHbnhyphenhyphenFc-VszPoADXtY5CoomLopwlFz6MMQWaAwrs_FsyGDNTCKFq1qyh5iPE899fkCjCCpxEddjq7hz8Y-ilOYaGm3VMHWCN2aVXv_DqQ_jack_04w/s1600/gas-tf.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="506" data-original-width="912" height="353" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUH5wRW3gvCCL4qchEXfwBTqaDHbnhyphenhyphenFc-VszPoADXtY5CoomLopwlFz6MMQWaAwrs_FsyGDNTCKFq1qyh5iPE899fkCjCCpxEddjq7hz8Y-ilOYaGm3VMHWCN2aVXv_DqQ_jack_04w/s640/gas-tf.png" width="640" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
I had to prepare Tensorflow.js library before training and predicting. First, the library uses the name <b>global</b> for the global variable. It was an easier part because I only defined a new variable and added a new line of code:</div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRLiWaVDCVyoWPVi4G2H8AJ5daA-BpQLNigbj832XoFjA76a5zvJXp_F3mwFBIkX0MCpIAThPUf7kEu2b7FgQwfRU3A_YNw-rZSE62UbFK598Ok8B4uwS_PwZdax7qUS0sCTrPX6P5vA/s1600/tf1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="115" data-original-width="622" height="118" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRLiWaVDCVyoWPVi4G2H8AJ5daA-BpQLNigbj832XoFjA76a5zvJXp_F3mwFBIkX0MCpIAThPUf7kEu2b7FgQwfRU3A_YNw-rZSE62UbFK598Ok8B4uwS_PwZdax7qUS0sCTrPX6P5vA/s640/tf1.png" width="640" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Second, the Tensorflow.js library uses native APIs "measuring time" - specifically Performance.now() or process.hrtime() </div>
<div style="text-align: justify;">
<b><br /></b></div>
<div style="text-align: justify;">
<b>Performance.now() i</b>s available only in browser API (Chrome) and<b> process.hrtime() i</b>s available only in backend language API (node.js). I got an error "<b><span style="color: #cc0000;">Cannot measure time in this environment. You should run tf.js in the browser or in Node.js</span></b>" in Google Apps Script, because I could not use first and second method.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
I did not fully reverse engineered library, but I think measuring time is used to for yielding main thread for other tasks. For this reason I setup yieldEvery as "never" during model compilation. (https://js.tensorflow.org/api/latest/)<br />
<br /></div>
<div style="text-align: justify;">
If you have more elegant solutions, ping me on Twitter or email.</div>
<h3 style="text-align: justify;">
Data</h3>
<div style="text-align: justify;">
<b><a href="https://www.kaggle.com/c/boston-housing">Boston Housing Prices dataset</a> </b>is "<i>hello world</i>" entry task in the machine learning world. It is a collection of 500 simple real-estate records collected in Boston (Massachusetts) in the late 1970s. Each row includes numeric measurements of a Boston neighborhood (e.g. crime rate, the typical size of homes, how far the region is from the closest highway, whether the area has waterfront property..). </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
These columns are named as a <b>features</b> (=inputs into the machine learning model).</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<img src="https://docs.google.com/drawings/d/e/2PACX-1vRCht370vyf4aLpVqV1EuqdIcIZxYjTR6L9Iu33YO464tPFxgCjrnN_mJwO10I5jmF8xPllt7MYYlkx/pub?w=425&h=344" />
</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
We want to predict the price of the home according to this dataset. This column is one and its name is a <b>target</b> (=output from machine learning model).</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
This prepared function download dataset from Google Cloud Storage into Google Sheet directly.</div>
<script src="https://gist.github.com/codeas/7bedb263fffb6d9bccd394897c7d19af.js"></script>
<br />
<h3 style="text-align: justify;">
Data preparation</h3>
<div style="text-align: justify;">
We have to divide data into <b>training</b> and <b>testing</b> dataset. A variable <b>rowSplit</b> defines row number for this splitting. In our case rows from 2 to 336 will be used as training dataset. The remain rows (from 337 to 507) as a testing dataset. The variables FEATURE_COLUMN_FROM and FEATURE_COLUMN_TO define features columns for training, testing and predicting. In our case features data are loaded from 1 - 12 columns.</div>
<div style="text-align: justify;">
<br /></div>
<script src="https://gist.github.com/codeas/3702f43adbfc37d7b865f949522c5150.js"></script>
<br />
<div style="text-align: justify;">
As a last step, select range in Google Sheet. We want to estimates values in column M according to values A- L in selected rows 7-9.</div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6xOvYtcMdb1rhIZ18rnqQHgsJwweK8g9OQCyK5k0HFw7AK9uvyrLGa2jSxTG29CGr4Xn5HmQIXYqJVvWrXe6GIORjRjOxy8hGUn1_t4JQulrF2gSgdZNqsam7UNoFpLtQWOGrjOAv1A/s1600/tf2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="526" data-original-width="1600" height="210" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6xOvYtcMdb1rhIZ18rnqQHgsJwweK8g9OQCyK5k0HFw7AK9uvyrLGa2jSxTG29CGr4Xn5HmQIXYqJVvWrXe6GIORjRjOxy8hGUn1_t4JQulrF2gSgdZNqsam7UNoFpLtQWOGrjOAv1A/s640/tf2.png" width="640" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Tensorflow does not work with Array data structure, but with <i>Tensors</i>. <b>Tensors</b> are multi-dimensional data structures. Function createTensor() creates 2D tensor for us.</div>
<script src="https://gist.github.com/codeas/3a790a5dea7a8257520b9014cbc2f422.js"></script>
<br />
<div style="text-align: justify;">
Several features (columns) contain values in different scale (e.g. tax values 187 - 711) than others (e.g crime rate 0.01 - 88.98). We have to normalize and transform values that improve the performance and training stability of model.</div>
<script src="https://gist.github.com/codeas/a78c17a8829a8b934c7fb5b9ccc30c84.js"></script>
<br />
<div style="text-align: justify;">
<br /></div>
<h3 style="text-align: justify;">
Building the model and training</h3>
<div style="text-align: justify;">
As you have already known, that deep-learning networks contains more layers with neurons. We need to define the architecture of neural network layer by layer. The syntax is similar metioned Keras. We have an architecture with two layers and each of them contains 50 neurons.</div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjb1A2GvDZgGVWZGilnF8AIxwQJZZJtV1CyeWf1C8m7T0RL0CZlsQmG7Pbj0nTjbN-7zWXKqho0-COepanAWQO0N0GIPLa05QWUADYJYOUzgyrX38GCasVibgW0MzHdO0ZEEv2TLIrMfw/s1600/unnamed.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="309" data-original-width="500" height="246" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjb1A2GvDZgGVWZGilnF8AIxwQJZZJtV1CyeWf1C8m7T0RL0CZlsQmG7Pbj0nTjbN-7zWXKqho0-COepanAWQO0N0GIPLa05QWUADYJYOUzgyrX38GCasVibgW0MzHdO0ZEEv2TLIrMfw/s400/unnamed.png" width="400" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
There are activation functions (Sigmoid) in every hidden layer.</div>
<div style="text-align: justify;">
<br /></div>
<div class="separator" style="clear: both; text-align: justify;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgwmtWSM8yXBlwluwOswM_hJF2efYMwEEI2EAas12GFOGo_pTq3n6mM1m7Voa1Aez0Vq5RmlOf1oBp4KYyDlPHFJctt-ToEl1ZImOm-W3yNqcAC29M1l4GiAePGF4DYj2ZsrgG8VbuyeA/s1600/sigmoid.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="455" data-original-width="998" height="145" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgwmtWSM8yXBlwluwOswM_hJF2efYMwEEI2EAas12GFOGo_pTq3n6mM1m7Voa1Aez0Vq5RmlOf1oBp4KYyDlPHFJctt-ToEl1ZImOm-W3yNqcAC29M1l4GiAePGF4DYj2ZsrgG8VbuyeA/s320/sigmoid.png" width="320" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
The last layer contains only one neuron with default (<b>linear</b>) activation function. It is linear, because our example is regression use-case.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Next step is compilation. In this step, we need to setup</div>
<ul>
<li style="text-align: justify;"><b>optimizer</b> (Stochastic gradient descent), how to find the best solution and neuron's weights</li>
<li style="text-align: justify;"><b>loss function </b>(meanSquaredError), how measure optimal solution</li>
</ul>
<div style="text-align: justify;">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdO73inidSbXSQ9jbrto5ZMoMT7rIuATXV6qaHs8kpzc1E9WTts55J1vyou7HVrMC_tmt94Kvhg3a7KZ_GZYL7CK5G5W-xukP3lZTVC2Whfjc-FXFj8OxuxSlIxfEdz3DA7NjU384Alg/s1600/w7aro.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="505" data-original-width="955" height="338" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdO73inidSbXSQ9jbrto5ZMoMT7rIuATXV6qaHs8kpzc1E9WTts55J1vyou7HVrMC_tmt94Kvhg3a7KZ_GZYL7CK5G5W-xukP3lZTVC2Whfjc-FXFj8OxuxSlIxfEdz3DA7NjU384Alg/s640/w7aro.png" width="640" /></a></div>
<br />
Now it is time for training. Method <b>.fit(</b>) trains model from data over several iterations (=EPOCHS)</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
These values like number of epochs, number of layers, number of neurons, type of activation function are <b>hyperparameters</b>. Data scientists around the world tune these values and compare it with previous settings. More info about settings in Tensorflow.JS API <a href="https://js.tensorflow.org/api/latest/">https://js.tensorflow.org/api/latest/</a></div>
<script src="https://gist.github.com/codeas/13b411cb4a5d1254ef3cece00d6eee1f.js"></script>
<br />
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Evaluation allows you to check the accuracy of your model. Less loss value is better. You should compare Train loss vs. Test loss. Bigger train loss means Overfitting and it is not ideal.</div>
<div style="text-align: justify;">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3SFM0Mz1VpD8CaLYOTFlMi4dQFXQT6YTpHGkyl5oMqxjuuxUqgAtoLUtuJvxVciSQY4FvIyJvLKskTNshiGpk_JX70woAHZpZdWeprepA63R8d5jfpaRwjrgZwHRfG96m_NPpPHKagA/s1600/1_cdvfzvpkJkUudDEryFtCnA.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="288" data-original-width="771" height="119" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3SFM0Mz1VpD8CaLYOTFlMi4dQFXQT6YTpHGkyl5oMqxjuuxUqgAtoLUtuJvxVciSQY4FvIyJvLKskTNshiGpk_JX70woAHZpZdWeprepA63R8d5jfpaRwjrgZwHRfG96m_NPpPHKagA/s320/1_cdvfzvpkJkUudDEryFtCnA.png" width="320" /></a></div>
</div>
<h3 style="text-align: justify;">
Prediction</h3>
<div style="text-align: justify;">
When we are satisfied with quality of our model and loss value is optimal. Now you can predict futures values. We also need to convert Array prediction values into Tensors and normalized it as well.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
In our code snippet predicted values are saved into cell Notes and you can compare it with original values.</div>
<div style="text-align: justify;">
<br />
<br /></div>
<div style="text-align: justify;">
Here is a main function, which load, prepare, train the data.</div>
<script src="https://gist.github.com/codeas/1ab83f594c478b4d81aeab1414679f32.js"></script>
<br />
<div style="text-align: justify;">
<br />
<br />
<h3>
Try it yourself!</h3>
If you want to test and play with it, full dataset + Google Apps Script code is available in this <a href="https://docs.google.com/spreadsheets/d/1VAZmpjPB8xTXI-KmvK3ig8OXgX3YzOn953g4mqVpJTc/edit#gid=1492638636" style="font-weight: bold;">Google Sheet</a><br />
<br />
1. Create a copy of spreadsheet<br />
2. Select any of rows (last value will be skipped during training)<br />
3. Open menu Tools --> Script editor<br />
4. Select menu Run --> Run function and choose <b>Main</b><br />
5. The predicted values will be saved as a notes </div>
<div style="text-align: justify;">
<br />
<br />
If you like<b> Google Apps Script, </b>folks like you are in this Google Groups community<br />
<a href="https://developers.google.com/apps-script/community">https://developers.google.com/apps-script/community</a><br />
<br /></div>
<div style="text-align: justify;">
<br /></div>
Ivan Kutilhttp://www.blogger.com/profile/10820026974853553039noreply@blogger.comtag:blogger.com,1999:blog-1495646117607516582.post-62551830165454016982020-02-24T14:41:00.000-08:002020-06-08T12:52:02.151-07:00Extract archived files directly from Google Drive with serverless tool Google Colab<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<span data-preserver-spaces="true" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; margin-bottom: 0pt; margin-top: 0pt;">Today I will show you how you can extract files from zip archives directly in Google Drive without any external 3rd tools. Everything is completed <i>serverless</i> and in da cloud :-)</span></div>
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<br /></div>
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<span data-preserver-spaces="true" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; margin-bottom: 0pt; margin-top: 0pt;"><b>Google Colab</b> is a pretty neat tool for data scientists, who operate and manage Jupyter notebooks in the cloud. I prefer Google Colab because supports Python 3 runtime with CPU, GPU or TPU processors. You can also mount your Google Drive as an external drive.</span></div>
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgu2LPe5doMTC_ejs8hMYL0bSprBXXgSonakvfNkydGFtZIeqNmlRsP0y6oz6Ex6IrwPYrGhi6D7fVhx7fD1zUQds2qgWOw15GLJYxlUvnnZKnnmVQDoK6Tt1HqLCTFVOegc4ZJE3a1aA/s1600/Google+Colab+a+zip.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="720" data-original-width="960" height="480" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgu2LPe5doMTC_ejs8hMYL0bSprBXXgSonakvfNkydGFtZIeqNmlRsP0y6oz6Ex6IrwPYrGhi6D7fVhx7fD1zUQds2qgWOw15GLJYxlUvnnZKnnmVQDoK6Tt1HqLCTFVOegc4ZJE3a1aA/s640/Google+Colab+a+zip.png" width="640" /></a></div>
<br />
<br /></div>
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<span data-preserver-spaces="true" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; margin-bottom: 0pt; margin-top: 0pt;">Colab prepares the virtual machine for you with Jupyter notebook. Notebook contains several cells, where you can write Python code and run it with "Play" button. <br /><br />There is also the magic behind "magic syntax sugar", which allows you to use more advanced commands. One of them is %%bash, which converts the rest of the commands into bash commands.</span></div>
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg4rkXfGnauIK2XhYJMux92hAXnvL6gFFYzosnITByeQ-c2dsFjrdRnulMx8tRisVtEXLRCe4OhfxWPT9tAIsCCUfJSyqmWPpE1MSx9crzS1rzaeFPlLHxlawF6JYbr82v3ran704-Ugw/s1600/screenshot-colab.research.google.com-2020.02.24-23_31_31.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="132" data-original-width="535" height="97" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg4rkXfGnauIK2XhYJMux92hAXnvL6gFFYzosnITByeQ-c2dsFjrdRnulMx8tRisVtEXLRCe4OhfxWPT9tAIsCCUfJSyqmWPpE1MSx9crzS1rzaeFPlLHxlawF6JYbr82v3ran704-Ugw/s400/screenshot-colab.research.google.com-2020.02.24-23_31_31.png" width="400" /></a></div>
<br /></div>
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<br /></div>
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<span data-preserver-spaces="true" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; margin-bottom: 0pt; margin-top: 0pt;">When we combine the above knowledge, we will get a powerful tool like an army knife:</span><br />
<br /></div>
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<span data-preserver-spaces="true" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; margin-bottom: 0pt; margin-top: 0pt;">1. Open Google Colab </span><span data-preserver-spaces="true" style="color: #4a6ee0; margin-bottom: 0pt; margin-top: 0pt;"><a class="_e75a791d-denali-editor-page-rtfLink" href="https://colab.research.google.com/notebooks/intro.ipynb#recent=true" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #4a6ee0; margin-bottom: 0pt; margin-top: 0pt;" target="_blank">https://colab.research.google.com/notebooks/intro.ipynb#recent=true</a> </span>and create a new notebook.</div>
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<span data-preserver-spaces="true" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; margin-bottom: 0pt; margin-top: 0pt;"><br /></span>
<span data-preserver-spaces="true" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; margin-bottom: 0pt; margin-top: 0pt;">2. Create a new notebook and rename it. At the right top click to button <b>Connect</b>, which starts your virtual machine. </span></div>
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<br /></div>
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<span data-preserver-spaces="true" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; margin-bottom: 0pt; margin-top: 0pt;">3.Now it is time to mount your Google Drive. Click to button <b>Mount Drive</b>. You have to authorize Colab application to your account. The path to your files is drive/My Drive/</span></div>
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXq9IZYz0Nv02hEoRU5NvM69jPIa6_gcDS80s_hkRLzLpw7F_fNg0xo-wMd7U4MgrcstzzvQTlBYZqmmmHQzMkA_ZDap1MWzv0hWUKw6GDn_P7DlcJXyXmvh_RHdLPET90fdizxnwDuA/s1600/screenshot-colab.research.google.com-2020.02.20-20_30_23.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="1260" data-original-width="1600" height="499" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXq9IZYz0Nv02hEoRU5NvM69jPIa6_gcDS80s_hkRLzLpw7F_fNg0xo-wMd7U4MgrcstzzvQTlBYZqmmmHQzMkA_ZDap1MWzv0hWUKw6GDn_P7DlcJXyXmvh_RHdLPET90fdizxnwDuA/s640/screenshot-colab.research.google.com-2020.02.20-20_30_23.png" width="640" /></a><br />
<br /></div>
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<span data-preserver-spaces="true" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; margin-bottom: 0pt; margin-top: 0pt;"><br /></span></div>
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<br /></div>
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
Copy this snippet of code int your Colab cell<br />
<script src="https://gist.github.com/codeas/06a6e216394cb8106c196c39f1099dd9.js"></script>
<br />
<br />
There are two - change directory and unzip desired files.<br />
<br />
Click to "play" button to run the selected cell (=snippet of code). Files will be extracted into same locations.<br />
<br />
In my example, I did Google Takeout to my Google Drive and backup zip files were saved into Takeout folder to my root folder. </div>
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEimVuw_OujiIRWqeJ_NoeVyxVJHDromu5PM5juMF7IxzRdns3DFC7SwYZIqDrWER2qNWHkhp9LaneABPot4T9WyJ_M2rYzyxZJk58MKlAd0mqeAQEhIXCRGXYltqlLNC4OheRlS6Bd7XQ/s1600/download+%25283%2529.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="825" data-original-width="1600" height="328" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEimVuw_OujiIRWqeJ_NoeVyxVJHDromu5PM5juMF7IxzRdns3DFC7SwYZIqDrWER2qNWHkhp9LaneABPot4T9WyJ_M2rYzyxZJk58MKlAd0mqeAQEhIXCRGXYltqlLNC4OheRlS6Bd7XQ/s640/download+%25283%2529.png" width="640" /></a></div>
<br /></div>
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<br />
<br /></div>
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<span data-preserver-spaces="true" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; margin-bottom: 0pt; margin-top: 0pt;">Note:</span></div>
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<span data-preserver-spaces="true" style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; margin-bottom: 0pt; margin-top: 0pt;">Bash runs cell in a subprocess, so it could take some time to unzip a lot of files</span></div>
<br />
<div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
</div>
<br />
<div style="background: transparent; color: #0e101a; margin-bottom: 0pt; margin-top: 0pt;">
<br /></div>
Ivan Kutilhttp://www.blogger.com/profile/10820026974853553039noreply@blogger.comtag:blogger.com,1999:blog-1495646117607516582.post-20260293741466984502019-06-03T13:13:00.004-07:002019-06-03T13:14:58.050-07:00How to measure latency between Google Apps Script project and Google Cloud Platform regions<a href="https://draft.blogger.com/blogger.g?blogID=1495646117607516582" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"></a>If you are debugging your <b>Google Apps Script </b>application, you find useful to measure time spent on requests. There are several regions (now 20) and zones (now 61) in Google Cloud Platform. (<a href="https://cloud.google.com/about/locations/?hl=cs#regions-tab">source</a>)<br />
<br />
<a href="https://draft.blogger.com/blogger.g?blogID=1495646117607516582" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"></a>G Suite and their smart-and-powerfull tool App Script are running on the same infrastructure. Integration between Apps Script and some GCP service (e.g. Vision API, Natural Language API, Cloud Functions) is not through Google private network, but through public internet (in other words: you are calling REST APIs with UrlFetchApp). That means, it is good to care about your region.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjr5pZ9nsQz1VDEtA8VMSsfcNGbRILYaliBBXf1RGADBb3yH_0QERqwT1O-JwkSWVo0P71NSkYfj9JepDfOvAzvwfXrkd334AYcwGkKX57-SyUoUttCkTxJmnh7zuZtMRlk66ZmrV4pQ/s1600/zones.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="864" data-original-width="1600" height="344" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjr5pZ9nsQz1VDEtA8VMSsfcNGbRILYaliBBXf1RGADBb3yH_0QERqwT1O-JwkSWVo0P71NSkYfj9JepDfOvAzvwfXrkd334AYcwGkKX57-SyUoUttCkTxJmnh7zuZtMRlk66ZmrV4pQ/s640/zones.png" width="640" /></a><a href="https://draft.blogger.com/blogger.g?blogID=1495646117607516582" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"></a></div>
<br />
<br />
If you would like to get more insights and find out where your application is "hosted", you can use code snippet.<br />
<br />
<script src="https://gist.github.com/codeas/965ebb85113a1b217f3102dff69bfb54.js"></script>
<br>
It measures latency between your Apps Script application and several GCP regions around the world. UrlFetchApp makes HTTP requests to VMs running in each region.<br />
<br />
Here is my result:<br />
(Apps Script Project Timezone: Paris, Europe, computer located: Prague, Czech republic)<br />
<br />
<blockquote class="tr_bq">
<a href="https://draft.blogger.com/blogger.g?blogID=1495646117607516582" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"></a>[19-06-03 13:09:05:115 PDT] us-east1: 8.0 ms<br />
[19-06-03 13:09:05:116 PDT] global: 10.0 ms<br />
[19-06-03 13:09:05:116 PDT] us-east4: 33.0 ms<br />
[19-06-03 13:09:05:117 PDT] northamerica-northeast1: 60.0 ms<br />
[19-06-03 13:09:05:118 PDT] us-central1: 86.0 ms<br />
[19-06-03 13:09:05:118 PDT] us-west1: 142.0 ms<br />
[19-06-03 13:09:05:119 PDT] us-west2: 144.0 ms<br />
[19-06-03 13:09:05:119 PDT] europe-west2: 183.0 ms<br />
[19-06-03 13:09:05:120 PDT] europe-west1: 195.0 ms<br />
[19-06-03 13:09:05:120 PDT] europe-west4: 197.0 ms<br />
[19-06-03 13:09:05:121 PDT] europe-west3: 204.0 ms<br />
[19-06-03 13:09:05:121 PDT] southamerica-east1: 246.0 ms<br />
[19-06-03 13:09:05:122 PDT] europe-north1: 258.0 ms<br />
[19-06-03 13:09:05:122 PDT] asia-northeast1: 319.0 ms<br />
[19-06-03 13:09:05:123 PDT] europe-west6: 343.0 ms<br />
[19-06-03 13:09:05:124 PDT] asia-east1: 389.0 ms<br />
[19-06-03 13:09:05:124 PDT] australia-southeast1: 421.0 ms<br />
[19-06-03 13:09:05:125 PDT] asia-east2: 433.0 ms<br />
[19-06-03 13:09:05:125 PDT] asia-southeast1: 463.0 ms<br />
[19-06-03 13:09:05:125 PDT] asia-south1: 563.0 ms</blockquote>
<div>
<br />
Inspiration and browser version is located here:<a href="http://www.gcping.com/"> http://www.gcping.com/</a></div>
Ivan Kutilhttp://www.blogger.com/profile/10820026974853553039noreply@blogger.comtag:blogger.com,1999:blog-1495646117607516582.post-11907035098628891312019-01-22T13:41:00.002-08:002019-01-22T13:43:07.775-08:00Deploy Google Apps Script web app as an Android applicationThe main advantage of <i>serverless</i> Google Apps Script against of <i>serverless</i> Cloud Functions is online editor (IDE) not only for writing code for server-side, but even for client-side. You can create HTML web application with <a href="https://developers.google.com/apps-script/guides/html/">HTMLService</a><br />
<div>
<br />
<div style="text-align: center;">
<img height="125" src="https://docs.google.com/drawings/d/e/2PACX-1vSQUr9_8wFH_68Bzold5YWNCHjnJzY8Rb9AARVNim5sU9uNw6xUVm3Qw8wsbvKRCa8OPE50tUc_teUH/pub?w=978&h=307" width="400" />
</div>
<br /></div>
<div>
Google has recently introduced (<a href="https://gsuiteupdates.googleblog.com/2018/12/create-manage-web-apps-in-g-suite.html">G Suite update 12/2018</a>) option to deploy web application as a <i>native</i> Android application for G Suite users. If you are reading this, you are probably power-user so you can imagine what you are able to do now! Yes, deploy native Android application with Google Apps Script.<br />
<br />
<span style="background-color: #f3f3f3;">Notes:</span><br />
<span style="background-color: #f3f3f3;">-To use web apps, your user’s devices must have Google Chrome installed</span><br />
<br />
1) Create a new Script at <a href="https://script.google.com/"><b>https://script.google.com</b></a><br />
<br />
2) Insert code snippet into code.gs<br />
<script src="https://gist.github.com/codeas/6e922e9e7ec98de350718595d858ef02.js"></script>
<br />
<br />
3) Create a new HTML file and rename it to "<b>view.html</b>"<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5ObOvumrO0N9N9ty-jIZbEix57YagmujFjLFPJj9D0K5II7070zmkPRgg0r3bKbxTrvzZwPdAeuexM2ZSWOpuB33LamrukzftuC3uIboV9dU79qY7LrvuWcq_aLXJB0b4_GlxuyarvA/s1600/android.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="660" data-original-width="1068" height="246" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5ObOvumrO0N9N9ty-jIZbEix57YagmujFjLFPJj9D0K5II7070zmkPRgg0r3bKbxTrvzZwPdAeuexM2ZSWOpuB33LamrukzftuC3uIboV9dU79qY7LrvuWcq_aLXJB0b4_GlxuyarvA/s400/android.png" width="400" /></a></div>
<br />
<br />
4) Edit HTML for your future über-cool-application (just for now, insert Hello World!)<br />
<br />
5) Open menu <b>Publish --> Deploy as web app </b>and copy <b>URL address</b><br />
<b><br /></b>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqNZIarfA7sNOlRe8C9hYzIZCMmhFSWYP8H-xrx23KXif1UCuhSTEA3ml2ngqmbf-K_YSYixQtfuF4m2YWGPOVBdFgyroV3c4MC9c3VAPMwfgHHSWeOYkxttaGAgHkoXqQxpvnjoolfg/s1600/deploy.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1000" data-original-width="952" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqNZIarfA7sNOlRe8C9hYzIZCMmhFSWYP8H-xrx23KXif1UCuhSTEA3ml2ngqmbf-K_YSYixQtfuF4m2YWGPOVBdFgyroV3c4MC9c3VAPMwfgHHSWeOYkxttaGAgHkoXqQxpvnjoolfg/s320/deploy.png" width="304" /></a></div>
<br />
6) It is time setup in G Suite. Open Google Admin console (<b><a href="https://admin.google.com/">https://admin.google.com</a>)</b> and pick category <b>Device management</b><br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiw2HXJGz8ozIl4tqDtC4NKDTZot72nTnBlYT8VQ0nsYsm75RiChap_ztk3U8YTUQ69TWjMq-SHBhTatIXFKX6bA7Ryujeid3RC3-4lAcxK4Tu6XRmGU_zcFhSnBk47XvWdPkBnf86rHQ/s1600/admin_console.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="749" data-original-width="1409" height="339" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiw2HXJGz8ozIl4tqDtC4NKDTZot72nTnBlYT8VQ0nsYsm75RiChap_ztk3U8YTUQ69TWjMq-SHBhTatIXFKX6bA7Ryujeid3RC3-4lAcxK4Tu6XRmGU_zcFhSnBk47XvWdPkBnf86rHQ/s640/admin_console.png" width="640" /></a><span style="text-align: left;">7) Then choose </span><b style="text-align: left;">App Management</b><span style="text-align: left;"> from left panel.</span></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEielNVQ6CnYvna6Fgr2Ccj0olcc8aDdU5JzNN2q_9X-80xNgVTKn0pxysg_CgW32S1i0WwSaQYHgjfGZz7FpVFak8MtNTf4dPXPp6n4RIEf_b5qBoJDJEHbHvUR9srTpJpJY0TMjZSD_w/s1600/app_mgmt.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="719" data-original-width="352" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEielNVQ6CnYvna6Fgr2Ccj0olcc8aDdU5JzNN2q_9X-80xNgVTKn0pxysg_CgW32S1i0WwSaQYHgjfGZz7FpVFak8MtNTf4dPXPp6n4RIEf_b5qBoJDJEHbHvUR9srTpJpJY0TMjZSD_w/s400/app_mgmt.png" width="195" /></a></div>
8) In App Management you will see section in the middle of page -<b> Manage apps for Android devices. </b>This section contains button <b>Manage whitelisted apps. </b>Click it.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijHhkqjQWtI7N_5oewNNi4LEWzj0pZyeDPYv8AR_3g25DVArm6K5LKKGHVN0v40i03I4hgksYBM-s3rztspgpNVJHj7qbIhLe2jxJ8JyO2ZKW-2oLZtXZKPcF9dapGVaH1Dv1e1rZaWw/s1600/whitelisted.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="840" data-original-width="1466" height="366" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijHhkqjQWtI7N_5oewNNi4LEWzj0pZyeDPYv8AR_3g25DVArm6K5LKKGHVN0v40i03I4hgksYBM-s3rztspgpNVJHj7qbIhLe2jxJ8JyO2ZKW-2oLZtXZKPcF9dapGVaH1Dv1e1rZaWw/s640/whitelisted.png" width="640" /></a></div>
<br />
<br />
9) Now you are in mobile application management. First, you have to Add a new whitelisted application (with yellow circle button)<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdQcLg6Gn8AXL5chhUx7DoGamPqvQiTebczz9RgId4Qot6udJzqp6XMIFxzANjHChc5S11s6A9VZnh5_HyTN8lAUBh1E-ObZQCq8UWvp22qOnDFSbaX1X_6v-RgPGx9ACfca_lmfjEKQ/s1600/whitelisted2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="764" data-original-width="1600" height="304" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdQcLg6Gn8AXL5chhUx7DoGamPqvQiTebczz9RgId4Qot6udJzqp6XMIFxzANjHChc5S11s6A9VZnh5_HyTN8lAUBh1E-ObZQCq8UWvp22qOnDFSbaX1X_6v-RgPGx9ACfca_lmfjEKQ/s640/whitelisted2.png" width="640" /></a></div>
<br />
10) You will see Google Play directory. Change category (left panel) to <b>Web Apps.</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfWSbT9yIaNZ_KREXXrvAskjURgNSa1gyAtYRea3mvS1dPx38AFQfx5pl-2HEDwkxqkSgwhtyvhLmLXKvUvcW9Jp7Yl4kixmbhQ7bo7nAZR61UNu-zcek3S7TDByddG_QVJiaHr_dl3g/s1600/web_apps.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="840" data-original-width="1466" height="364" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfWSbT9yIaNZ_KREXXrvAskjURgNSa1gyAtYRea3mvS1dPx38AFQfx5pl-2HEDwkxqkSgwhtyvhLmLXKvUvcW9Jp7Yl4kixmbhQ7bo7nAZR61UNu-zcek3S7TDByddG_QVJiaHr_dl3g/s640/web_apps.png" width="640" /></a></div>
<br />
<br />
11) For now, you have to again click at <b>Add</b> button (circle button at right bottom of page.<br />
<br /></div>
<div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjoq03Yql_Z8lWzNKhG9wb-huYTZ6fQFk-Z-FClH85zsoQsQloAhmxkuEy8jKmfBjjnHic25sr_dnZrOGWjlwyoF_S4dVhL-wzTBSyTY7rrQ0y14HdS_lQVlgco_GXQl7YslK30YAXg0Q/s1600/circle.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="679" data-original-width="994" height="272" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjoq03Yql_Z8lWzNKhG9wb-huYTZ6fQFk-Z-FClH85zsoQsQloAhmxkuEy8jKmfBjjnHic25sr_dnZrOGWjlwyoF_S4dVhL-wzTBSyTY7rrQ0y14HdS_lQVlgco_GXQl7YslK30YAXg0Q/s400/circle.png" width="400" /></a></div>
<br />
<br />
12) Now you can define behaviour of your Android application.<br />
- Title is name of application on home screen<br />
- URL of your Google Apps Script (from step 5)<br />
- I recommend you for display choose <b>Full screen </b>or <b>Standalone</b><br />
- Icon of your Android application<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBeTrGg20O-eBxhXAX4IIeBdyuiJ2uobOeERodqjk-GKITfoYRUqwpIMmCMNAtMrFRtgiul-5rI3l_U3Xu9L3SP-DG68h1fcr5lwZqaSzEqE0_gEvQxCiyZ9X8-LjRTZZyDNayCpNqsA/s1600/new-app.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="762" data-original-width="1037" height="468" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBeTrGg20O-eBxhXAX4IIeBdyuiJ2uobOeERodqjk-GKITfoYRUqwpIMmCMNAtMrFRtgiul-5rI3l_U3Xu9L3SP-DG68h1fcr5lwZqaSzEqE0_gEvQxCiyZ9X8-LjRTZZyDNayCpNqsA/s640/new-app.png" width="640" /></a></div>
<br />
13) After you click at Create button it will take about 10 minutes to prepare your application.<br />
(There is a small text Not available yet below during preparation )<br />
<br />
14) When your application is successfully prepared, then you can click at desired icon<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0-NeYqUcnE-zjHPmqv-_fJW0ZNP-h_gigF1BR6lvNlFjGcdNRV-h-ckKeAe8Lc1HDC6OsIr2RtJ-4IkfT7vavjZerzq9PRbDqeknBb5UCiEabb-5eYxa3gb3a1To3kDtrM8qUHeTtbw/s1600/ready.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="572" data-original-width="703" height="325" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0-NeYqUcnE-zjHPmqv-_fJW0ZNP-h_gigF1BR6lvNlFjGcdNRV-h-ckKeAe8Lc1HDC6OsIr2RtJ-4IkfT7vavjZerzq9PRbDqeknBb5UCiEabb-5eYxa3gb3a1To3kDtrM8qUHeTtbw/s400/ready.png" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigZ9KywK5zvEMnAMm-xakepx4XEC6wGm6P9GP4wuhW1ZS_TNtLfVfQ3ubKYxKvxg7sLBWKa5pDbIV3cS2T-s2bH-m-7eAZXPlwrhfJITyBmEtowMkV6WDzC8IvdqBgKHtC-_Cs8XK0Ow/s1600/select.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="836" data-original-width="1085" height="492" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigZ9KywK5zvEMnAMm-xakepx4XEC6wGm6P9GP4wuhW1ZS_TNtLfVfQ3ubKYxKvxg7sLBWKa5pDbIV3cS2T-s2bH-m-7eAZXPlwrhfJITyBmEtowMkV6WDzC8IvdqBgKHtC-_Cs8XK0Ow/s640/select.png" width="640" /></a></div>
<br />
15) During distribution strategy, you can distribute application for all G Suite users or subset according to Organization unit.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghPIa02Zs9E7ohxawfNpjUSn1oPvhTjhfuc133UuX3bvHGjLNOtQBXDXh7r8Wm9PcZzmx1BwHDOuVKv4mFDMaVUmBR0imzRHtrgAK3Xpi6Iwoyka777BXI-FucoTrFkDnbErw90tyzcg/s1600/distribution.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="917" data-original-width="1600" height="366" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghPIa02Zs9E7ohxawfNpjUSn1oPvhTjhfuc133UuX3bvHGjLNOtQBXDXh7r8Wm9PcZzmx1BwHDOuVKv4mFDMaVUmBR0imzRHtrgAK3Xpi6Iwoyka777BXI-FucoTrFkDnbErw90tyzcg/s640/distribution.png" width="640" /></a></div>
<br />
<br />
<br /></div>
<div>
16) Last step contains option to automatically install application on all devices (This option doesn't work for me)<br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtOhLD0w3aFOokCVn5JyO9QhB-ZJuH-biAgUJ3OQPQ0NHD_2yTa-kvMs3Webxbco9JPm248ykFKM9upIksdvXEJqauMKirxyRKva4SRlVO27WBUxGlMkfNh-w9N4jySqgxodaPWGffVw/s1600/distribution+2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="917" data-original-width="1600" height="366" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtOhLD0w3aFOokCVn5JyO9QhB-ZJuH-biAgUJ3OQPQ0NHD_2yTa-kvMs3Webxbco9JPm248ykFKM9upIksdvXEJqauMKirxyRKva4SRlVO27WBUxGlMkfNh-w9N4jySqgxodaPWGffVw/s640/distribution+2.png" width="640" /></a></div>
<br /></div>
<div>
<br />
17) When you visit Google Play at your phone, then navigate to section <b>Work Apps. </b>Click to application and install it.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiEVcAlxuWh_kkz208bSK1kHhgxVywFEeGQqyMfUrUKSQiHrzykJvT8dDXc9fHr_tp8YCJrFQaZiHw7FLGRRqfQZJbKijvKsbJDNHXA3_RVeh5crYhrDKB5q3ZQzNR8izyg7Ha5sCdSEQ/s1600/play.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1600" data-original-width="900" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiEVcAlxuWh_kkz208bSK1kHhgxVywFEeGQqyMfUrUKSQiHrzykJvT8dDXc9fHr_tp8YCJrFQaZiHw7FLGRRqfQZJbKijvKsbJDNHXA3_RVeh5crYhrDKB5q3ZQzNR8izyg7Ha5sCdSEQ/s320/play.jpg" width="180" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
The was a final step. Your Google Apps Script application is on mobile's homescreen</div>
<br />
<br /></div>
<div>
<br /></div>
Ivan Kutilhttp://www.blogger.com/profile/10820026974853553039noreply@blogger.comtag:blogger.com,1999:blog-1495646117607516582.post-43639149073354249502018-03-09T14:07:00.000-08:002018-03-09T14:07:47.033-08:00Analyse emotion in OSCAR movies 2018 with Google Apps Script and Google Natural Language APIAnd OSCAR goes to...<br />
<br />
There are a lot of indicators, who predict the winner's movie. One of them, no doubt, is emotions. When the screenplay is written in a way to change emotion from positive to negative and vice versa, it is creating a big impact on peoples mind.<br />
<br />
Nowadays you can you Natural Language Processing without deep knowledge of all things like syntax analysis, grammar, external libraries. Especially big companies like Google, Amazon or Microsoft invest a huge amount of money to improve their algorithm and provide as service/API.<br />
<br />
I am always interested in easily and fast proof-of-concept, so today I will show you how I put these things together.<br />
<br />
<img src="https://docs.google.com/drawings/d/e/2PACX-1vRcp55zu4qdEu3druXvP9J6GEH0fIkTPAs0YbrGlD6XtQmOXgfF6SdsxBNPRoqxKkaFpO8W4OpV_56d/pub?w=500&h=500" />
<br />
<br />
<br />
I have chosen a category for <a href="https://en.wikipedia.org/wiki/90th_Academy_Awards">Best picture (90th Academy Awards)</a> and these movies<br />
<ul>
<li>Call Me by Your Name – Peter Spears, Luca Guadagnino, Emilie Georges, and Marco Morabito</li>
<li>Darkest Hour – Tim Bevan, Eric Fellner, Lisa Bruce, Anthony McCarten, and Douglas Urbanski</li>
<li>Dunkirk – Emma Thomas and Christopher Nolan</li>
<li>Get Out – Sean McKittrick, Jason Blum, Edward H. Hamm Jr., and Jordan Peele</li>
<li>Lady Bird – Scott Rudin, Eli Bush, and Evelyn O'Neill</li>
<li>Phantom Thread – JoAnne Sellar, Paul Thomas Anderson, Megan Ellison and Daniel Lupi</li>
<li>The Post – Amy Pascal, Steven Spielberg, and Kristie Macosko Krieger</li>
<li>The Shape of Water – Guillermo del Toro and J. Miles Dale</li>
<li>Three Billboards Outside Ebbing, Missouri – Graham Broadbent, Pete Czernin, and Martin McDonagh</li>
</ul>
<br />
<b>1.</b> I downloaded subtitles from server <a href="https://www.opensubtitles.org/">Opensubtitles</a> and saved these files (.srt) to my Google Drive.<br />
<br />
<b>2. </b>I created a new project in <a href="https://script.google.com/home">Google Apps Script</a> and setup Google Natural Language API endpoint in menu Resources -> Cloud Platform -> left menu API & Services -> Library -> Cloud Natural Language API.<br />
<i>Note: Google Apps Script creates a new Google Cloud project for you, so you don't have to create it yourself.</i><br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCOH-IAQN0zSKTRrMBRUYUP-es-7U4sNTv-NKAQfUQtcs5CipWX9mSmm3bwSslRVUD5THvFwJX4kjV10oQqbFCjbHe817ciHRcSMXG5vbKgyY4oIZ_ykpRwXiPwMgaxM8dy1JLFGMm4A/s1600/nlp01.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="924" data-original-width="1600" height="368" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCOH-IAQN0zSKTRrMBRUYUP-es-7U4sNTv-NKAQfUQtcs5CipWX9mSmm3bwSslRVUD5THvFwJX4kjV10oQqbFCjbHe817ciHRcSMXG5vbKgyY4oIZ_ykpRwXiPwMgaxM8dy1JLFGMm4A/s640/nlp01.png" width="640" /></a></div>
<br />
In Google Cloud platform dashboard I also got API key, which identifies applications.<br />
<br />
API & Services -> Credentials > Create credentials -> API key<br />
(You should setup Application restrictions for HTTP referrer as a minimum)<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRhY5QqOOKfOhMybSsjyCwamb5WgDHcxPr0TKXyH8ah2ahqddVNSx1pnF-lFMKLcBqZJlp-1IizEekyVyZAgWAMdTsZ2fYXPbPu9vJXW-jA_8fekPRYdFOXGNikOM3DYntshB5ataEFA/s1600/nlp02.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="941" data-original-width="1600" height="376" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRhY5QqOOKfOhMybSsjyCwamb5WgDHcxPr0TKXyH8ah2ahqddVNSx1pnF-lFMKLcBqZJlp-1IizEekyVyZAgWAMdTsZ2fYXPbPu9vJXW-jA_8fekPRYdFOXGNikOM3DYntshB5ataEFA/s640/nlp02.png" width="640" /></a></div>
<br />
<br />
<br />
<b>2.</b> First I did preprocess. I borrow a term from machine learning - <b>bucketing</b>. I aggregated multiple lines of subtitles into time-framed text "window" of length 2 minutes. Here is a code:<br />
<br />
<br />
<br />
<br />
<script src="https://gist.github.com/codeas/8a0edf18c8be1c02eef9e8592410a862.js"></script>
<br>
<b> 3. </b>The rest is simple - iteration over all "buckets" and send each grouped text to Google Natural Language. This API response with two numbers - sentiment and magnitude. In my case, I used sentiment number in the range <-1;1>. Everything is saved in Google Spreadsheet and charts are rendered directly from there.<br />
<script src="https://gist.github.com/codeas/3a5feae448ed59cc0f9cbdb8f2e0853e.js"></script>
4. I put together two functions from above:
<script src="https://gist.github.com/codeas/5c853ff0424cc6d76e273df561cf1fde.js"></script>
<br />
You can see how sentiment changes during the movie (time is on the x-axis) for the best OSCAR movie of the year 2018:<br />
<iframe width="500" height="250" seamless frameborder="0" scrolling="no" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vR64n0N2Q36imYFF3STd7L_mvOJY-f5sPh8VpCcs6PBDPiYg1nrpWDs43tj_KdLHwhyyH9n55KosCav/pubchart?oid=332523944&format=image"></iframe>
<br>
Rest nominated movies:<br>
<iframe width="500" height="250" seamless frameborder="0" scrolling="no" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vR64n0N2Q36imYFF3STd7L_mvOJY-f5sPh8VpCcs6PBDPiYg1nrpWDs43tj_KdLHwhyyH9n55KosCav/pubchart?oid=1243990718&format=image"></iframe>
<iframe width="500" height="250" seamless frameborder="0" scrolling="no" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vR64n0N2Q36imYFF3STd7L_mvOJY-f5sPh8VpCcs6PBDPiYg1nrpWDs43tj_KdLHwhyyH9n55KosCav/pubchart?oid=920022374&format=image"></iframe>
<iframe width="500" height="250" seamless frameborder="0" scrolling="no" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vR64n0N2Q36imYFF3STd7L_mvOJY-f5sPh8VpCcs6PBDPiYg1nrpWDs43tj_KdLHwhyyH9n55KosCav/pubchart?oid=1405706836&format=image"></iframe>
<iframe width="500" height="250" seamless frameborder="0" scrolling="no" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vR64n0N2Q36imYFF3STd7L_mvOJY-f5sPh8VpCcs6PBDPiYg1nrpWDs43tj_KdLHwhyyH9n55KosCav/pubchart?oid=772253854&format=image"></iframe>
<iframe width="500" height="250" seamless frameborder="0" scrolling="no" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vR64n0N2Q36imYFF3STd7L_mvOJY-f5sPh8VpCcs6PBDPiYg1nrpWDs43tj_KdLHwhyyH9n55KosCav/pubchart?oid=694153527&format=image"></iframe>
<iframe width="500" height="250" seamless frameborder="0" scrolling="no" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vR64n0N2Q36imYFF3STd7L_mvOJY-f5sPh8VpCcs6PBDPiYg1nrpWDs43tj_KdLHwhyyH9n55KosCav/pubchart?oid=1444696610&format=image"></iframe>
<iframe width="500" height="250" seamless frameborder="0" scrolling="no" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vR64n0N2Q36imYFF3STd7L_mvOJY-f5sPh8VpCcs6PBDPiYg1nrpWDs43tj_KdLHwhyyH9n55KosCav/pubchart?oid=293732232&format=image"></iframe>
<iframe width="500" height="250" seamless frameborder="0" scrolling="no" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vR64n0N2Q36imYFF3STd7L_mvOJY-f5sPh8VpCcs6PBDPiYg1nrpWDs43tj_KdLHwhyyH9n55KosCav/pubchart?oid=107817334&format=image"></iframe>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />Ivan Kutilhttp://www.blogger.com/profile/10820026974853553039noreply@blogger.comtag:blogger.com,1999:blog-1495646117607516582.post-29232398800608636932016-10-06T14:20:00.000-07:002017-02-18T12:11:33.816-08:00Find the best location for your next shop in Google Spreadsheet (Voronoi Algorithm)<img height="299" src="https://docs.google.com/drawings/d/17Ymk-3BSOgKkEpWsNA5lSK614oe4KcEg9DXbYTIFmn8/pub?w=1043&h=488" width="640" />
<br />
<br />
About 14 months ago I wondered how a Czech retail market is divided into regions according to their locations. Where is the best place to build next shop? I remembered to a <a href="https://en.wikipedia.org/wiki/Voronoi_diagram">Voronoi algoritm</a> from university that could divide desire an area into small parts along the nearest spot.<br />
<br />
Let's check a definition from Wikipedia<br />
<blockquote class="tr_bq">
<a href="https://en.wikipedia.org/wiki/Voronoi_diagram">Voronoi diagram</a> is<i> a partitioning of a plane into regions based on distance to points in a specific subset of the plane. That set of points (called seeds, sites, or generators) is specified beforehand, and for each seed there is a corresponding region consisting of all points closer to that seed than to any other.</i></blockquote>
<br />
<div style="text-align: center;">
<img height="336" src="https://docs.google.com/drawings/d/1IGImehU93BK8xoEYawCNlxoLaDBbF_6RjtC9TPO8ycc/pub?w=546&h=459" width="400" />
</div>
<div>
<br />
Why should you upload your a dataset into an unknown online web application when you want to visualize data? There were a lot of examples on the internet, but I wanted to create as easy as possible for everyone. Finally I found <b><a href="https://chriszetter.com/blog/2014/06/15/building-a-voronoi-map-with-d3-and-leaflet/">article by Chris Zettter</a></b>, where code was written over <b>Leaflet and Open Street</b> map layer. It inspired me to rewrite as Google Add-on to Google Spreadsheet with Google Maps underlay (I love Google Maps SDK)</div>
<div>
<br /></div>
<div>
I will introduce you shortly how to use <b>Voronoi Map from</b> scratch in lesss than 5 minutes.</div>
<div>
<b><br /></b></div>
<div>
1) Create a new Google Spreadsheet. Then insert data into Sheet </div>
<div>
<div>
<br /></div>
<div>
For this demo I've downloaded data of California retailers from this <a href="https://fusiontables.google.com/DataSource?dsrcid=956738">Fusion Tables</a> and filtered only <b>Walgreens </b>stores in <b>San Francisco</b> (my favorite place :-). </div>
<div>
<br /></div>
<div>
You need to prepare data as separate columns for <b>Name, Latitude, Longitude nad Type </b>of point of interest. I've added a new column "<b>brand"</b> (green color) as a <b>Type </b>and filled it with text "Walgreens"</div>
</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1gkk0XjSgABMc0bd-fbUahq4rKMwMTGmVnIf9m_fPEW7tT6m-GJx6MiyKfzbyKQhaTrLXBqyZ464a9noNn0jJB-BQXIw5gHNuuLGH3TVFQojytII0SfFpF8V_V7mdyGcJcyFUptEfnw/s1600/voronoi3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="345" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1gkk0XjSgABMc0bd-fbUahq4rKMwMTGmVnIf9m_fPEW7tT6m-GJx6MiyKfzbyKQhaTrLXBqyZ464a9noNn0jJB-BQXIw5gHNuuLGH3TVFQojytII0SfFpF8V_V7mdyGcJcyFUptEfnw/s640/voronoi3.png" width="640" /></a></div>
<div>
<br /></div>
<div>
2) Open menu <b>Add-ons </b>and select <b>Get add-ons. </b>Find <b>voronoi map </b>and than click at <b>+ FREE</b> button for install.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj23Qez5vGgKzslmYtLezdxrhv3e-5msl3KkGkzex9u6bM-J5uuMLuE_DpgZwWFNsOMzDSLhXY2I5owiA7L0iqRksM2zJjEM2LltH85PxjTyrAAP9J_65EQZWjKtwgHf0ot_Cbe7ryMQQ/s1600/voronoi1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="146" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj23Qez5vGgKzslmYtLezdxrhv3e-5msl3KkGkzex9u6bM-J5uuMLuE_DpgZwWFNsOMzDSLhXY2I5owiA7L0iqRksM2zJjEM2LltH85PxjTyrAAP9J_65EQZWjKtwgHf0ot_Cbe7ryMQQ/s640/voronoi1.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
3) After installation open menu <b>Add-ons -> Voroni Map </b>and select <b>Create Voronoi Map</b></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
4) Select appropriate colums and click <b>Create map</b></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1yX7DidPpAeZkrp_VcopBQ5JnhmdSUOL9A1buw75Qzju0g4W3GNAzbRTsCamkXsk65ImLkIJ2wnDt1P2IJwshxUk1R1ogp8B-s6mPBndDr0prcBKBtP_nngB5lMBDFoRn3b-4kV5s-Q/s1600/voronoi5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="183" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1yX7DidPpAeZkrp_VcopBQ5JnhmdSUOL9A1buw75Qzju0g4W3GNAzbRTsCamkXsk65ImLkIJ2wnDt1P2IJwshxUk1R1ogp8B-s6mPBndDr0prcBKBtP_nngB5lMBDFoRn3b-4kV5s-Q/s320/voronoi5.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Now you see a result. The blue points are point-of-interest (Walgreens stores) and regions along the nearest point. If you have more type of points-of-interest, you can filter them in right top panel.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-6ZtSZ_l5RLdqDiseY-4VjNu1uGWKCEY5MSbljo0B1B-3TOw5DXWHJZssCZv2zFD8k20zuzFxio3QLzBZYYEyeIc3yyMbEA_dReNIar5fhPu9qoVlmY3AcRoOXVCVRA20D-vU_p3KGQ/s1600/voronoi4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" height="548" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-6ZtSZ_l5RLdqDiseY-4VjNu1uGWKCEY5MSbljo0B1B-3TOw5DXWHJZssCZv2zFD8k20zuzFxio3QLzBZYYEyeIc3yyMbEA_dReNIar5fhPu9qoVlmY3AcRoOXVCVRA20D-vU_p3KGQ/s640/voronoi4.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Probably you are thinking where you should built you next shop? I would recommend you find intersection of region and think about this location :-)</div>
<div>
<br /></div>
Ivan Kutilhttp://www.blogger.com/profile/10820026974853553039noreply@blogger.comtag:blogger.com,1999:blog-1495646117607516582.post-3333419454752148202016-02-23T13:35:00.002-08:002016-02-23T13:35:34.098-08:00Analyse city traffic from webcams through Google Cloud Vision API <div class="separator" style="clear: both; text-align: center;">
</div>
<div style="margin-left: 1em; margin-right: 1em;">
<br /></div>
The Google has recently introduced <a href="https://cloud.google.com/vision/">Cloud Vision API</a>, which allows you to analyse image data through API instead of creating custom algorithms.<br />
<br />
As <a href="https://developers.google.com/experts/people/ivan-kutil">Google Developer Expert</a> I was involed in tester program of this API during alpha stage. My GDE's friend <a class="g-profile" href="https://plus.google.com/106333172328928589411" target="_blank">+Riël Notermans</a> inspired me to connect Vision API into my favorite <b><a href="https://developers.google.com/apps-script/">Google Apps Script</a>.</b> I was thinking how to use it and suddenly I found out useful something-like <i>smart city</i> solution.<br />
<br />
<div style="margin-left: 1em; margin-right: 1em;">
<img height="563" src="https://docs.google.com/drawings/d/1IR60zT1JS4wG0voVie-H32xnIVnqzeNrk11_OBnO18o/pub?w=816&h=721" style="text-align: center;" width="640" /></div>
<h4>
Intro</h4>
I'm living in Prague. The municipal transportation provides almost real-time records of traffic from webcams The images are available on web (<a href="http://www.dpp.cz/en/webcams/">http://www.dpp.cz/en/webcams/</a>). Anyone could check traffic before heading out to work. I am able to connect, download and send these images to any API, because each image has own public URL<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgiyAmqJtqJfyXMc9KrX0iPaXZJ5xD7jTZPUS_Tu88uhur4KTZZwLkeXjczbA7ZQ2aKkN_Llii_DcV-olbtkWiO69r1rMowmq6S0V4Fjfb1PZ9JMyZn7V2kBij07iW8OxIx7bz361ay3Q/s1600/mapa_prahy_webcam.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="190" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgiyAmqJtqJfyXMc9KrX0iPaXZJ5xD7jTZPUS_Tu88uhur4KTZZwLkeXjczbA7ZQ2aKkN_Llii_DcV-olbtkWiO69r1rMowmq6S0V4Fjfb1PZ9JMyZn7V2kBij07iW8OxIx7bz361ay3Q/s400/mapa_prahy_webcam.png" width="400" /></a></div>
<div style="text-align: center;">
<span style="color: #666666;">The maps of webcams in Prague</span></div>
<br />
I have created Google Spreadsheet as a database with new a Google Apps Script project.<br />
Next step was activated required API in Google Developer Console. <i>OAuth 2.0 dance </i>was managed by great <a href="https://github.com/brucemcpherson/cGoa">cGoa </a>library by <a class="g-profile" href="https://plus.google.com/112066118406610145134" target="_blank">+Bruce Mcpherson</a>. Cloud Vision "client" library was generated from Cloud Endpoints by <a class="g-profile" href="https://plus.google.com/115383627259502138986" target="_blank">+Spencer Easton</a>'s code.<br />
<br />
My script fetched the image from URL as a Blob, converted into base64 and sent to Cloud Vision API with parameter for LABEL_DETECTION (<a href="https://cloud.google.com/vision/docs/label-tutorial">Label detection tutorial available here</a>)<br />
<br />
<script src="https://gist.github.com/codeas/8f4e4a0579d547e86d77.js"></script>
<br />
As a JSON response I received description of image as labels with confidence score (min 0 - max 1).<br />
The last step was setup Trigger for automatic run Apps Script every 5 minutes.<br />
<h3>
The result</h3>
I have run my script on webcam at Argentinska street at February 17th between 12am-12pm. The all collected data is available as interactive visualization. <b><a href="http://data.kutil.org/visualization/analysis-of-traffic-by-cloud-vision-api">http://data.kutil.org/visualization/analysis-of-traffic-by-cloud-vision-api</a></b><br />
<br />
The label "road" means, that higher value looks like only street without street, so traffic during lunch was low.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjaGj54LTm7JFfdpI09Edr3nCVTO80S9dD14RT6yQkYFnjkA7VsqBnLqhWK0WRtmpY8evMGNyhCLKaPj52uHHiTIEK7Cx_BYD1Ng-Kejidw8aAxfaEOh_zWSeQtMiGspg5AvN_D86io8Q/s1600/viz_road.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="452" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjaGj54LTm7JFfdpI09Edr3nCVTO80S9dD14RT6yQkYFnjkA7VsqBnLqhWK0WRtmpY8evMGNyhCLKaPj52uHHiTIEK7Cx_BYD1Ng-Kejidw8aAxfaEOh_zWSeQtMiGspg5AvN_D86io8Q/s640/viz_road.png" width="640" /></a></div>
The chart for label <b>traffic</b> looks like inverse function of <i>road label. </i>The higher value means, that Google's recognizes traffic on the image with greater confidence.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj6dxVkMbu1FssGdzAvmtsNdUV_bK2ecxyLnBe1TzNdqC33b_Lv7fUOvUTb9bDbIYr5VnynWSYrHUSjoZsMmv56uhCEefNAE0zQlZbjfcFuLyogqsZDOcJXXfxACi_ZGgjEsvZtZKvtJg/s1600/viz_traffic.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="448" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj6dxVkMbu1FssGdzAvmtsNdUV_bK2ecxyLnBe1TzNdqC33b_Lv7fUOvUTb9bDbIYr5VnynWSYrHUSjoZsMmv56uhCEefNAE0zQlZbjfcFuLyogqsZDOcJXXfxACi_ZGgjEsvZtZKvtJg/s640/viz_traffic.png" width="640" /></a></div>
<br />
It absolutely not 100% accurate, but it could inspire you how to use Google Cloud Vision API.<br />
<br />Ivan Kutilhttp://www.blogger.com/profile/10820026974853553039noreply@blogger.comtag:blogger.com,1999:blog-1495646117607516582.post-85205338322670612022016-02-14T07:53:00.002-08:002016-02-16T08:33:55.289-08:00Integrate Google Apps and Slack with Google Apps Scripts (Incoming Webhooks)<b>Google Apps</b> and <b>Slack </b>are two the most popular productivity tools for unicorn startups (=early stage companies with valuation $1B+). Almost 80% unicorn startups are using Google Apps as primary platform for email, calendar or storage and 50% of them are using Slack for internal communication [<a href="https://siftery.com/groups/unicorns">source</a>]<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyELBG5woz3V7bwO_ehXArQy4NYSazgKD7Zm8uqe3kbRjX4LzRrv053Rnk5FzoFjxX2RCUah0fD3QK28KnrrIBBkn25lRORY3HGOP3eLKScKpkExeiawYG1vF2HwyUeUs65PFTj7IMoA/s1600/productivity.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="192" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyELBG5woz3V7bwO_ehXArQy4NYSazgKD7Zm8uqe3kbRjX4LzRrv053Rnk5FzoFjxX2RCUah0fD3QK28KnrrIBBkn25lRORY3HGOP3eLKScKpkExeiawYG1vF2HwyUeUs65PFTj7IMoA/s640/productivity.png" width="640" /></a></div>
It means that nearly half of these <i>cool </i>startups could integrate their internal process with <b><a href="https://developers.google.com/apps-script/">Google Apps Scripts</a>.</b><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0tS5cVm06cIIK-FrcjG2kgwNLa9Y6-fThX4bTRVQBDPasimldgw0909advEvewV7Kcelb9wrQMyl2OWhW3t7ruFXbQT_jlbluuzJUoonREvCYHiRBELBXedubv9ajXKgNivwRpmFv6w/s1600/overcrowded-unicorns-v2-cover.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="241" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0tS5cVm06cIIK-FrcjG2kgwNLa9Y6-fThX4bTRVQBDPasimldgw0909advEvewV7Kcelb9wrQMyl2OWhW3t7ruFXbQT_jlbluuzJUoonREvCYHiRBELBXedubv9ajXKgNivwRpmFv6w/s640/overcrowded-unicorns-v2-cover.png" width="640" /></a></div>
<br />
<br />
<b>Slack API</b> has several possible ways how to integrate your application/script into their platform. Today we will start the simplest way - through <b>Incoming Webhooks. </b>Webhooks are solid and unique published URL address, which allows you send any data, which will be saved into channels.<br />
<br />
<img height="566" src="https://docs.google.com/drawings/d/1pntSXeme5_NBkjmgj1aSy6Cvbh5Xnt1vNF4eNuYZQXo/pub?w=816&h=721" width="640" />
<br />
<br />
<br />
<h3>
The small guide how to integrate Google Apps with Slack in 10 minutes</h3>
1) Visit link <a href="https://my.slack.com/services/new/incoming-webhook"><b>https://my.slack.com/services/new/incoming-webhook</b></a>/. You will be redirected into settings page of your board.<br />
<br />
2) Select the target channel (e.g #tech) - don't worry you can change afterwards. Next click at green button <b>Add Incoming WebHooks integration. </b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhkmbQzEhit17FkHvUoLsnHvGsTrZgpoYBb03Q9Bp1N0OGjNN4g5qbNkXOUG5vJPlcQpE-yNo8osi1aLxEdm1JdF1Wje8JuFvN4HNLNESMAZJZSPaay0qVCUoxEIGmuhatgvrzZ98a2Pg/s1600/slack1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="443" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhkmbQzEhit17FkHvUoLsnHvGsTrZgpoYBb03Q9Bp1N0OGjNN4g5qbNkXOUG5vJPlcQpE-yNo8osi1aLxEdm1JdF1Wje8JuFvN4HNLNESMAZJZSPaay0qVCUoxEIGmuhatgvrzZ98a2Pg/s640/slack1.png" width="640" /></a></div>
<br />
3) The remain Slack webpage contains settings like name of the robot (=it's not actually pseudo-robot), icon, default channel and so on. We only need to copy <b>webhook url. </b>(e.g.<b> </b><span style="background-color: white; color: #183691; font-family: Consolas, 'Liberation Mono', Menlo, Courier, monospace; font-size: 12px; line-height: 16.8px; white-space: pre;">https://hooks.slack.com/services/xxxxxx/xxxxxxx/xxxxxxxxxxxxxxxxxxxxxxxxxx</span><br />
<br />
4) Create a new Google Apps Script project and copy code snippet. Insert your webhook url from previous<br />
<script src="https://gist.github.com/codeas/1fd5697037bc5ebb2b31.js"></script>
<br />
<br />
5) Now run<b> START() </b>function in Apps Script. If you insert right url, you will see a new message is relevant channel.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj6R558UULfPRt-XU3R2OB53VrZAMCRX6LwvixUSEBTNLR-5mn6gQsJK0Ubms8-jhfpDIAMvxMn9TLIxagXN1BqIVDks-ci-ZVR9PcGGumHr43UMWbwUfBAhmRQHbOAtQbKe5YmMF-FKA/s1600/robot.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="95" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj6R558UULfPRt-XU3R2OB53VrZAMCRX6LwvixUSEBTNLR-5mn6gQsJK0Ubms8-jhfpDIAMvxMn9TLIxagXN1BqIVDks-ci-ZVR9PcGGumHr43UMWbwUfBAhmRQHbOAtQbKe5YmMF-FKA/s400/robot.png" width="400" /></a></div>
<br />
Now you have connect your Google Apps Script with Slack. You can simply load data from Calendar, Gmail, Drive or Sites and create alert on events. I will show you in some next article.<br />
<br />
I recommend check tips how to format text, add links or attachments: <b><a href="https://api.slack.com/docs/formatting">https://api.slack.com/docs/formatting</a></b><br />
<br />
Are planning to integrate your Google Apps with Slack? Let your ideas in comments sectionIvan Kutilhttp://www.blogger.com/profile/10820026974853553039noreply@blogger.comtag:blogger.com,1999:blog-1495646117607516582.post-21482744531618958762016-01-24T11:13:00.000-08:002016-01-30T09:49:04.462-08:00Easy data scraping with Google Apps Script in 5 minutesI'm using <a href="https://developers.google.com/apps-script/">Google Apps Script </a>for a lot of things - from automate tasks to data analysis. I have discovered, that there was repetitive use-case: scrape data from web and parse exact value from HTML <i>source code</i>. If you are novice in programming, you probably know, that's difficult to write and use <a href="http://www.w3schools.com/jsref/jsref_obj_regexp.asp">regular expresion</a>. For me too :) I have written <b>Google Apps Script library</b>, which helps you to <b>parse data in 5 minutes.</b><br />
<br />
Let's create a small example. <a href="http://appsatori.eu/en/">In our company</a> we have created a Google Apps application <b><a href="http://signaturesatori.com/">SignatureSatori</a> </b>to create and setup email signatures for all users in domain. Like a good <b><a href="https://en.wikipedia.org/wiki/Growth_hacking">growth hacker</a> </b>I benchmark the competitors how quickly get new users. There is a Google Apps Marketplace, which estimate number of users. I need save that numbers each day.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDhKI5FiqaDk3csAAVDRlbf2Oa6KODuR03f-qsiCHxoTy1aUja8eglU29Ef62vvZlUbKEMUBvKX8uAyzlJ5bzNYgQK_RG2LlLZMjyqIRTJkvSE1b3Hc2njGIjj3hLh5iGPMnn-LichhA/s1600/screen.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="187" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDhKI5FiqaDk3csAAVDRlbf2Oa6KODuR03f-qsiCHxoTy1aUja8eglU29Ef62vvZlUbKEMUBvKX8uAyzlJ5bzNYgQK_RG2LlLZMjyqIRTJkvSE1b3Hc2njGIjj3hLh5iGPMnn-LichhA/s400/screen.png" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
1) Create a new Google Apps Script and insert a new library (Resources -> Library)</div>
<div class="separator" style="clear: both; text-align: center;">
<span style="background-color: #f3f3f3; font-family: "arial" , sans-serif; line-height: 18.2px; white-space: pre-wrap;"><b>M1lugvAXKKtUxn_vdAG9JZleS6DrsjUUV</b></span></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Parser library takes three parameters - input text and pattern which bounds desired text.</div>
<div class="separator" style="clear: both; text-align: left;">
Parser // name of library</div>
<pre class="brush:js"> .data(content) // input text
.from(fromText) // from text pattern
.to(toText) // to text pattern
.build(); // run parser and return value
</pre>
<div class="separator" style="clear: both; text-align: center;">
<span style="background-color: #f3f3f3; font-family: "arial" , sans-serif; line-height: 18.2px; white-space: pre-wrap;"><b><br /></b></span></div>
2) Now open desired web-page (e.g <a href="https://chrome.google.com/webstore/detail/signaturesatori-central-s/fejomcfhljndadjlojamaklegghjnjfn?hl=en">Chrome Webstore</a> in our case). Click on specify HTML element by right mouse button and select Inspect element.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOhqr0DTOXbE_f75ias299u24WZbTuMQ0be1Qy-TN7_RE9O6M3aS6VYVtiVsg3JlJay7fCC06_p7R4xN6Qagn-ePePFqSckAHQ0fmQk_DwaYgZ8-j_ec_uGc1dggbE8adS9v9E0n59oQ/s1600/inspect.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="164" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOhqr0DTOXbE_f75ias299u24WZbTuMQ0be1Qy-TN7_RE9O6M3aS6VYVtiVsg3JlJay7fCC06_p7R4xN6Qagn-ePePFqSckAHQ0fmQk_DwaYgZ8-j_ec_uGc1dggbE8adS9v9E0n59oQ/s640/inspect.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="background-color: white; font-family: "arial" , sans-serif; font-size: 13px; line-height: 18.2px; white-space: pre-wrap;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="background-color: white; font-family: "arial" , sans-serif; font-size: 13px; line-height: 18.2px; white-space: pre-wrap;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="background-color: white; font-family: "arial" , sans-serif; font-size: 13px; line-height: 18.2px; white-space: pre-wrap;">3) </span><span style="background-color: white; font-family: "arial" , sans-serif; font-size: 13px; line-height: 18.2px; white-space: pre-wrap;"> Find the right part of HTML and copy <b>fromText </b>and <b>toText</b></span></div>
<div class="separator" style="clear: both; text-align: left;">
<img height="195" src="https://docs.google.com/drawings/d/1QIbvDBHytElCaaIYrwSdhz8Ds5bvaviCHHzwt8MOD-o/pub?w=967&h=295" width="640" />
</div>
<br />
4) Now we have all required information to complete script
<br />
<pre class="brush:js">function getData() {
var url = "https://chrome.google.com/webstore/detail/signaturesatori-central-s/fejomcfhljndadjlojamaklegghjnjfn?hl=en";
var fromText = '<span class="e-f-ih" title="';
var toText = '">';
var content = UrlFetchApp.fetch(url).getContentText();
var scraped = Parser
.data(content)
.from(fromText)
.to(toText)
.build();
Logger.log(scraped);
return scraped;
}
</pre>
<br />
5) The last and the easiest step is copy parsed data into Spreadsheet
<br />
<pre class="brush:js">function SAVE_DATA() {
var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName); // insert Spreadsheet Id and Sheet name
sheet.appendRow([ new Date(), getData() ]);
}
</pre>
<br />
6) If you want to log during scraping (e.g. if you want to debug wrong value), call .setLog() function before final .build() function:
<br />
<pre class="brush:js">
Parser
.data(content)
.setLog()
.from(fromText)
.to(toText)
.build();
</pre>
<a href="https://script.google.com/d/1Mc8BthYthXx6CoIz90-JiSzSafVnT6U3t0z_W3hLTAX5ek4w0G_EIrNw/edit?usp=drive_web">
<img height="28px" src="https://upload.wikimedia.org/wikipedia/en/f/f2/Google_Apps_Script.png" />Completed code of Parser library</a>
Enjoy!Ivan Kutilhttp://www.blogger.com/profile/10820026974853553039noreply@blogger.comtag:blogger.com,1999:blog-1495646117607516582.post-64786233530529283532015-10-11T11:18:00.001-07:002016-08-15T09:31:28.286-07:00Hosting for your files instead of Google Drive #2 (Firebase)In <a href="http://www.kutil.org/2015/10/hosting-for-your-files-instead-of.html">my previous blogpost</a> I have described first alternative for hosting files instead of Google Drive - Google Storage.<br />
<br />
Today I would like to introduce another alternative - Firebase. It is a real-time database for saving JSON objects through REST API. The Firebase also offers hosting for your files, which are served under HTTPS. Moreover, a free plan is available. You can upload for hosting 5 GB with max. traffic 30 GB. There is also a paid plan, where you can get more storage/transfer and setup custom domain. (<a href="https://firebase.google.com/pricing/">Firebase pricing</a>)<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHBl1zZXjyaYk018bmR4kTqyskuiBrN1iUXhAFTcu5vlWOdfbWh76K4LlwdQkrXuafq5n3qBMijFoUrUNPomX3kv4Nk68wGB_T9DaT4g8QHJ-yxO6Wzj6ZZAqOWhJ2ccnVFWdWsDddNA/s1600/firebase-logo.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHBl1zZXjyaYk018bmR4kTqyskuiBrN1iUXhAFTcu5vlWOdfbWh76K4LlwdQkrXuafq5n3qBMijFoUrUNPomX3kv4Nk68wGB_T9DaT4g8QHJ-yxO6Wzj6ZZAqOWhJ2ccnVFWdWsDddNA/s1600/firebase-logo.png" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
Let's go!<br />
<br />
1) First open console <a href="https://console.firebase.google.com/?pli=1">https://console.firebase.google.com/?pli=1</a> with your Google Account<br />
<br />
2) <b>Create new project</b> (or import your Google Cloud project and connect to it)<br />
<br />
3) Choose project name (I recommend to choose more memorable URL address lik<i> apps-script-project.firebaseapp.com. </i>Dont forget that must be unique)<br />
<br />
4) Now you have to install <b>firebase tools</b> on your computer via npm.<br />
<blockquote class="tr_bq">
<i>Note. If you have never used npm to install app, please download and install nodejs </i><i><a href="https://nodejs.org/">https://nodejs.org/</a> </i><i>(npm - node package manager is included)</i></blockquote>
<br />
If you have already installed nodejs, you can type into shell/command line<br />
<div style="background-color: black; color: white;">
<b>npm install -g firebase-tools</b></div>
<br />
5) Login into your Google Account. Type<br />
<pre style="background-color: black; color: white;"><b>firebase login</b></pre>
<div>
and authorize access in opened browser</div>
<div>
<br /></div>
6) Type first command to run code initialization.<br />
<pre style="background-color: black; color: white;"><b>firebase init</b></pre>
<br />
7) Then select Database row with arrows keys UP/DOWN and press SPACE key.<br />
We want to confgure and deploy only Hosting options.<br />
<pre style="background-color: black; color: white;"><b>( ) Database</b></pre>
<pre style="background-color: black; color: white;"><b>(*) Hosting</b></pre>
<div>
Press ENTER</div>
<br />
8) Select Firebase project<br />
<br />
9) Choose which directory will be setup as public (=contains files hosted on Firebase). <b>Public </b>is as a default opt<br />
<br />
10) Don't configure as single-page app, because all assets will be redirect to index.html (press <b>N</b>)<br />
<br />
11) The last step is to deploy files into Firebase by code (Rememeber, that you should be in your directory)<br />
<pre style="background-color: black; color: white;"><b>firebase deploy</b></pre>
<br />
12) Now you can access your project at URL name<br />
<br />
<div>
<div style="text-align: center;">
<span style="font-size: large;">http://apps-script-project.<b>firebaseapp.com</b></span></div>
<span style="font-size: large;"><b><br /></b></span>
Every deployment is logged in dashboard, where you can revert into any previous version/state.</div>
<div style="text-align: center;">
</div>
Ivan Kutilhttp://www.blogger.com/profile/10820026974853553039noreply@blogger.comtag:blogger.com,1999:blog-1495646117607516582.post-45205037795104105992015-10-04T11:05:00.004-07:002015-10-21T07:29:45.588-07:00Hosting for your files instead of Google Drive (Google Storage)A lot of not only Google Apps Script developers and users have used Google Drive feature to host any file as webhosting. It was useful for CSS files, images, JavaScript libraries, which can be used in code. Deployment was very easy - you have just copied files into specific folder and change sharing options.<br />
<br />
Google recently announced, that hosting feature will be deprecated (<a href="https://support.google.com/drive/answer/2881970?hl=en">more information here</a>). Users have, according to deprecation policy, one year to find out a new place where to store files.<br />
<br />
I was thinking about several options, so I would like to bring some alternatives.<br />
<br />
The first one is Google Storage, which is a part of Google Cloud platform<br />
<b><br /></b>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyQGPJ6B87_xCCjsMbz-lZIxxdS4ru5LLjhgTMYlA4JJPmVQejkU1BeBsn-4K_v5ayDM-iTaq3Q3xbSv3mdQxvZZUl88e0Jeb5VE8S2Daoe8W4n6KZq3bwzqadK-1vlFntylyPm4wIkQ/s1600/google+cloud+platorm.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="152" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyQGPJ6B87_xCCjsMbz-lZIxxdS4ru5LLjhgTMYlA4JJPmVQejkU1BeBsn-4K_v5ayDM-iTaq3Q3xbSv3mdQxvZZUl88e0Jeb5VE8S2Daoe8W4n6KZq3bwzqadK-1vlFntylyPm4wIkQ/s400/google+cloud+platorm.jpg" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
Google Storage allows you to host any file with advanced possibilities like control who can read/write, monitor of traffic etc.<br />
<br />
The main advantage is connection with App Script. Every Apps Script project has also Cloud project in Developers console (<a href="http://console.developers.google.com/">http://console.developers.google.com</a>)<br />
<br />
From Google Apps Script editor choose <b>Resources --> Advanced Google services</b><br />
<b><br /></b>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjHJnU67OlRBnc3Ed9X9hoxu-lndecMdTxoJXbo4W4aV0oXW5ynhDki44NRUNli5wqaCk42n_YyiPF8GcomXlmgh4izO0iSvkX6nQ4TzP_bVye0cNQvQkVV-antUrTRT99D6reTnL6Mzw/s1600/cloud.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="216" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjHJnU67OlRBnc3Ed9X9hoxu-lndecMdTxoJXbo4W4aV0oXW5ynhDki44NRUNli5wqaCk42n_YyiPF8GcomXlmgh4izO0iSvkX6nQ4TzP_bVye0cNQvQkVV-antUrTRT99D6reTnL6Mzw/s320/cloud.png" width="320" /></a></div>
<br />
In the new window, click at link "These services must also be enabled in the <b>Google Developers Console."</b><br />
<br />
In Developer console, select Storage - Cloud storage. If you open console for the first time, you have to click button Enable billing (In this section you will fill in credentials like your name or number of credit card)<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtluWNQWmOzjMXwfvoiL5OMCMn-I_bsxgg7uECBxL7yWUAnvuGKfYVSpUJ1votx7gRHDZU5qxzf-H9e-pbC2ICC14rQBOGsKYlcRSFO6_b8zdIeyNqHrSnyJ1ZG9RnbZzMt4Cs1mxeqQ/s1600/stor1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="308" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtluWNQWmOzjMXwfvoiL5OMCMn-I_bsxgg7uECBxL7yWUAnvuGKfYVSpUJ1votx7gRHDZU5qxzf-H9e-pbC2ICC14rQBOGsKYlcRSFO6_b8zdIeyNqHrSnyJ1ZG9RnbZzMt4Cs1mxeqQ/s320/stor1.png" width="320" /></a></div>
<br />
Now it is time to Create a bucket. Have you ever heard about it? <b>The bucket </b>is <i>virtual space, </i>where your <b>objects </b>(files) are stored. You can have serveral buckets for one Cloud project<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcfQwc9dSl8inlLaKvIuCQxifCxTIXV2vtIZaSBsTWYl0P4yiqrnI3xN9tnDodKCvKs8RdQmGIc-4Uiacf_qLS4XIEM-b80xlLe-_AZ57R0exUXqlJd-MOCSJJ7nfdb5FVzx8dJxuKRA/s1600/stor2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="165" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcfQwc9dSl8inlLaKvIuCQxifCxTIXV2vtIZaSBsTWYl0P4yiqrnI3xN9tnDodKCvKs8RdQmGIc-4Uiacf_qLS4XIEM-b80xlLe-_AZ57R0exUXqlJd-MOCSJJ7nfdb5FVzx8dJxuKRA/s320/stor2.png" width="320" /></a></div>
<br />
Select name of the bucket. Important - this name must be unique in all <i>Google Cloud Storage platform</i><br />
<i><br /></i>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhf7wxdtkdb-Pymchg8aErsK8BnZgVxyxD_qK6zmWzfR4d6CbXRV1_0CK4YpGKMJ6lgP0RwEt1IJbjlW3sMjQjV3SJtNZrTulm1QzaK0gYfMe1mbaHUgDUpTm08EQAVbHbRMzmvri1k7g/s1600/stor3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="204" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhf7wxdtkdb-Pymchg8aErsK8BnZgVxyxD_qK6zmWzfR4d6CbXRV1_0CK4YpGKMJ6lgP0RwEt1IJbjlW3sMjQjV3SJtNZrTulm1QzaK0gYfMe1mbaHUgDUpTm08EQAVbHbRMzmvri1k7g/s320/stor3.png" width="320" /></a></div>
<br />
Now you can create a new folder inside the bucket and upload files - by clicking button or drag'n'drop like in Google Drives. It is similar, isn't it ?<br />
<br />
<br />
The last thing is publish on the web by click on checkbox<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgecam4UHmM3G-HwDPL2dtPAU2ub3WPg3uXSWVNMhWP63-abDpmVoDua40_wYp-AMbkPwwWvNztS5PNXP8naMSN-20glw9ydWjEnPZngyUgX6BTZvKWOCeFH-m7OH6m-FALXMqZphIBDA/s1600/stor5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="76" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgecam4UHmM3G-HwDPL2dtPAU2ub3WPg3uXSWVNMhWP63-abDpmVoDua40_wYp-AMbkPwwWvNztS5PNXP8naMSN-20glw9ydWjEnPZngyUgX6BTZvKWOCeFH-m7OH6m-FALXMqZphIBDA/s640/stor5.png" width="640" /></a></div>
<br />
The final URI will like:<br />
<br />
<div>
<span style="font-size: large;"><b>https://storage.googleapis.com/BUCKET/FOLDER/load.gif</b></span><br />
<div style="text-align: left;">
Now you can insert your file in your Apps Script HTMLService code or send link to someone else.</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
Google Storage is a paid service, but the price is low and according to using of service (=cloud).There are three main questions for you</div>
<div style="text-align: left;">
</div>
<ol>
<li>How much data do you need to store ? (1GB per month = $0.026)</li>
<li>How many requests do you do? (10 000 requests for $0.01)</li>
<li>What is total traffic through your bucket ( 1GB of traffic in EMEA/AMERICA= $0.12)</li>
</ol>
<br />
So if you are planning to host 10 MB ($0.01) with total loading 100 000x ($0.01) and traffic 1GB ($0.12), you will finally get receipt<b> $0.14 </b>for month!<br />
<div>
<br /></div>
<div>
Google prepared online tool to get information about price: https://cloud.google.com/products/calculator/</div>
<div style="text-align: left;">
<b><br /></b></div>
<div style="text-align: left;">
<b>Conclusion</b></div>
<div style="text-align: left;">
I am just starting use this way to host files, but I have already recognize "best pattern". Its looks like, that is better to create one main project in Developer Console, which wont be connected to any GAS project. Then for every GAS project create a new bucket.. </div>
</div>
<br />
<br />
<br />Ivan Kutilhttp://www.blogger.com/profile/10820026974853553039noreply@blogger.comtag:blogger.com,1999:blog-1495646117607516582.post-71715038346340253652015-08-28T07:03:00.000-07:002016-01-24T08:29:54.645-08:00Material Design into your Google Apps Script applicationGoogle recently introduced <a href="http://getmdl.io/">Material Design Lite</a>, which is library of components and templates in pure CSS, HTML and JavaScript without any Polymer implementation.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<img border="0" height="160" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhAoHXURruc7Oo9aO-uEmde5UY3VRf9OdaBtTbwUgPGIMzQnAvP-3eXp3J_8e0WjujjS2zYkqyoQvGPZtsVVVmAw1Mf3Z1labV7skMfyUIb5yX7HwcOTbq8r2hN9DyNguQ0q28aQnHRzA/s320/material.png" width="320" /></div>
<br />
<br />
This is useful for your Google Apps Script web-apps, because you just insert CSS into your rendered HTMLSevice. You don't have to vulcanize html files<br />
<br />
At top of your code insert this snippet<br />
<br />
<pre class="brush:html">
<link rel="stylesheet" href="https://storage.googleapis.com/code.getmdl.io/1.0.4/material.indigo-pink.min.css">
<script src="https://storage.googleapis.com/code.getmdl.io/1.0.4/material.min.js"></script>
<link rel="stylesheet" href="https://fonts.googleapis.com/icon?family=Material+Icons"></pre>
<br />
Now you can add class name into your already written HTML elements. For example if you have created<br />
<br />
<pre class="brush: js"><button class="mdl-button mdl-js-button mdl-button--raised mdl-js-ripple-effect mdl-button--accent">Send form</button>
</pre>
<br />
<div class="separator" style="clear: both; text-align: center;">
<img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlOTXIujmZbPCW4ysw0MiNRhpf13i2B-Tt1jRpXYNUj2yTVEQE22qmfDmn2-ajIUH9IHo1Cf5FhhOdxevEVLypweWMNqh2emBc7OPCDC-ztmujcxKhpIyoPDqwWCe-6mH5fQqRbQUjTg/s1600/icon.png" /></div>
<b><br /></b>
<b>Modularity first! </b>Google offers customize your color scheme. There is<a href="http://www.getmdl.io/customize/index.html"> online tool</a>, where you can choose primary and secondary color. The generated unique URL defined your setup.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<img border="0" height="263" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4hEKbVUATuQVPJiuIA6A2iKE5fe3YzSlm4mKNupT-tEhuqTt8Fi_KJFLJLvTGaZ0L6GNUA2EOSXTUYNXFuRdWLuKL61PnrTaJigISU1pkRY2_0MkQ40wFz3sf5U3ojTg-H_JDT9htfA/s400/materil2.png" width="400" /></div>
<br />
<br />
I have already implemented Material Design in public.<br />
<br />
At <a href="http://appsatori.eu/">AppSatori</a> we launched free tool <b>Google Apps Finder. </b>If you typed domain, you get response whether company using Google Apps.<br />
<br />
I needed create easy and fast first prototype. Instead of creating design and graphic elements, I have put together all material elements.<br />
<br />
Live demo is available at <a href="http://link.appsatori.eu/gappsfinder">https://script.google.com/macros/s/AKfycbxAGnsK0FcGIPB0Kfl38Bx4PbLa9hXZd4nqJ6-cqrcwr4alFllD/exec</a><a href="http://link.appsatori.eu/finder"></a><br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<img border="0" height="241" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidTFmcoAAMES2l_jRTf3ah5nerb9sFPoDifgXu4RnK_dTcJLs7u0m6HARE2NlquYczHDdgw1VmWfPgzf7H0i9S7W1EaaWoqygT4lnJNSVpbygiV-twW7gBYBWX0nWQRW9mfpDm8f7apQ/s320/finder.png" width="320" /></div>
<br />
<br />Ivan Kutilhttp://www.blogger.com/profile/10820026974853553039noreply@blogger.comtag:blogger.com,1999:blog-1495646117607516582.post-62852327842702412602015-04-05T08:33:00.001-07:002016-01-24T08:26:09.518-08:00Recursion in Google Apps ScriptThe recursion is very usefull and probably first <i>hard-to-understand-knowledge</i> for new programmers. It is usefull when you want to to call the same function again and again, but with different values as a parameter.<br />
<br />
Well know example is calculating factorial. Factorial of 5, contains factorial 4 and its contains factorial of 3 etc.<br />
<br />
...<br />
<b>5!</b> = 5 * 4! = 5 * 4 * 3! = 5 * 4 * 3 * 1 = 120<br />
<b>4!</b> = 4 * 3! = 4 * 3 * 2! = 4 * 3 * 2 * 1 = 24<br />
...<br />
<br />
So you can write function like
<pre class="brush:js">
function factorial(n) {
if (n == 0 || n == 1) return 1;
if (f[n] > 0) return f[n];
return f[n] = n * factorial(n-1);
} <br />
</pre>
If you run factorial(5), it will call function 5 time. Every new calling will inserted into another.<br />
<br />
There is small limitation in Google Apps Script during recursion<b>. You can call only 1000 inherited functions. </b>
<br />
If your script call multiple times, you will get error:<br />
<b style="background-color: #cc0000;"><span style="color: white;">Exceeded maximum stack depth </span></b><br />Ivan Kutilhttp://www.blogger.com/profile/10820026974853553039noreply@blogger.comtag:blogger.com,1999:blog-1495646117607516582.post-77844583375320321222013-04-21T13:55:00.000-07:002016-01-24T08:23:21.883-08:00How to get data from Jawbone UP and insert to Spreadsheet by Google Apps Script<div class="separator" style="clear: both; text-align: center;">
</div>
A two days ago I've purchased Jawbone UP. It can track my life (sleeping, running walking, and eating habits). I'm data addict, so I was looking for way to get data from this incredible (<i>internet</i>) thing. I found out unofficial Jawbone UP API (<a href="http://eric-blue.com/projects/up-api/">http://eric-blue.com/projects/up-api/</a>). Its look like better way instead of "ofiicial CSV" which can be downloaded from website. So I've created <b><i>Google Apps Script</i></b> library to download data from Jawbone and insert to Spreadsheet.<br />
<br />
In short tutorial I will describe how to install script and get data for specify day. You can create your own script for automatization e.g. post to Twitter, create charts or reports.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-tcFvnLliZqY/UXRKuFA3nNI/AAAAAAAAGzg/2Z2hT0R_LRg/s1600/jup0.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="142" src="http://3.bp.blogspot.com/-tcFvnLliZqY/UXRKuFA3nNI/AAAAAAAAGzg/2Z2hT0R_LRg/s320/jup0.jpg" width="320" /></a></div>
<br />
<h2>
For GAS novice</h2>
1) Create a new Google Spreadsheet. Open <b>Script Editor </b>(from menu Tools > Script editor)<br />
<b><br /></b>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-8yiZ40NLfpk/UXRDvZJtV0I/AAAAAAAAGy4/tk-GxBjNpmc/s1600/jup1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="260" src="http://1.bp.blogspot.com/-8yiZ40NLfpk/UXRDvZJtV0I/AAAAAAAAGy4/tk-GxBjNpmc/s400/jup1.jpg" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
2) In a new window select <b>Blank project</b><br />
<br />
<b><br /></b>
3) In Script Editor select <b>Resources > Manage libraries</b><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-vODcCW00cJk/UXRDveUjppI/AAAAAAAAGzE/6posvDYvwR4/s1600/jup2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="191" src="http://4.bp.blogspot.com/-vODcCW00cJk/UXRDveUjppI/AAAAAAAAGzE/6posvDYvwR4/s400/jup2.jpg" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
4) To input box on the bottom insert <b>M6ZtEUVi85vR5n2ozP3eYNFeS6DrsjUUV</b> </div>
<div class="separator" style="clear: both; text-align: left;">
(it is unique id of Google Apps Script library). If it is succesfull loaded, select last version. Click to button Save.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-RmigqwOXyl4/UXRDvfBXICI/AAAAAAAAGy8/jMGb_8jQa8E/s1600/jup3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="394" src="http://4.bp.blogspot.com/-RmigqwOXyl4/UXRDvfBXICI/AAAAAAAAGy8/jMGb_8jQa8E/s640/jup3.jpg" width="640" /></a></div>
<br />
<br />
5) Copy below code and insert to Google Apps Script editor. You have to replace your email and password<br />
<br />
<pre class="brush: js">
var EMAIL = "your@email.com";
var PASSWORD = "*****";
function getStatistics() {
var token = JawboneUp.getToken(EMAIL,PASSWORD);
var from = new Date(2013,03,21,8,00,00,000); // get data from date 21.4.2013 8:00
var to = new Date(2013,03,21,23,00,00,000); // to date 21.4.2013 23:00
var data = JawboneUp.getActivity(from,to, token);
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var header = [ "time", "distance", "active_time", "calories", "steps", "aerobic", "speed" ];
var rows = []
rows.push(header);
for (i in data) {
var row = []
for( j in header) {
var val = data[i].value[header[j]];
if (header[j] == "time") val = new Date( parseInt(val,10) *1000)
row.push( val )
}
rows.push(row);
}
sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows)
}
</pre>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div>
<br /></div>
<div>
<br /></div>
6) Run copied code (<b>Run > getStatistics </b>) First you should approve data access and then run again.<br />
<div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-pe8JatOWGPA/UXRDwDKstLI/AAAAAAAAGzQ/rCeLEtvRkek/s1600/jup4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="168" src="http://3.bp.blogspot.com/-pe8JatOWGPA/UXRDwDKstLI/AAAAAAAAGzQ/rCeLEtvRkek/s400/jup4.jpg" width="400" /></a></div>
<br />
Thats all - now you can see result in Spreadsheet.<br />
<br />
<br />
<br />
My report is showed below. I've add chart (Insert > Chart) and change row color.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-UKCoGQ5NCns/UXRQ7UyxV1I/AAAAAAAAGzo/KhcQv_KrTWs/s1600/jup7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="492" src="http://2.bp.blogspot.com/-UKCoGQ5NCns/UXRQ7UyxV1I/AAAAAAAAGzo/KhcQv_KrTWs/s640/jup7.png" width="640" /></a></div>
<br />
<br />
<h2>
For GAS ninjas</h2>
<div>
<ul>
<li>Library has two method</li>
<ul>
<li>getToken(EMAIL, PASSWORD)</li>
<li>getActivity(from,to, token) - from and to are Date() objects</li>
</ul>
<li>Token can be store in UserProperites / Spreadsheet or ScriptDB, and you save one request each time</li>
<li>I'm planning to add more methods in the future. Stay tunned and follow me <a href="http://gplus.to/codeas">Google+</a></li>
</ul>
</div>
</div>Ivan Kutilhttp://www.blogger.com/profile/10820026974853553039noreply@blogger.com