{"id":33361,"date":"2016-04-19T10:22:02","date_gmt":"2016-04-19T04:52:02","guid":{"rendered":"http:\/\/www.tothenew.com\/blog\/?p=33361"},"modified":"2016-04-19T10:22:02","modified_gmt":"2016-04-19T04:52:02","slug":"introduction-to-google-apps-script-with-google-spreadsheet","status":"publish","type":"post","link":"https:\/\/www.tothenew.com\/blog\/introduction-to-google-apps-script-with-google-spreadsheet\/","title":{"rendered":"Introduction to Google Apps Script with Google Spreadsheet"},"content":{"rendered":"<p><strong>What is Google Apps Script ?<\/strong><\/p>\n<p>Google Apps Script is a JavaScript based scripting language that lets you add functionality to your Google Apps. It is a cloud-based language that integrates with all other Google services include Gmail, Google Drive, Calendar, Google Forms, Spreadsheets and more. We create and edit our scripts in an online IDE and the scripts run on Google&#8217;s Cloud infrastructure.<\/p>\n<p><strong>What Apps Script do ?<\/strong><\/p>\n<p>Apps Script is versatile. You can :<\/p>\n<ul>\n<li style=\"font-family: 'open_sansregular';font-size: 14px !important\">Add custom menus, dialogs, and sidebars to Google Docs, Sheets, and Forms.<\/li>\n<li style=\"font-family: 'open_sansregular';font-size: 14px !important\">Write custom functions for Google Sheets.<\/li>\n<li style=\"font-family: 'open_sansregular';font-size: 14px !important\">Publish web apps \u2014 either standalone or embedded in Google Sites.<\/li>\n<li style=\"font-family: 'open_sansregular';font-size: 14px !important\">Interact with other Google services, including AdSense, Analytics, Calendar, Drive, Gmail, and Maps.<\/li>\n<\/ul>\n<p><em>Here we are taking example with Google Spreadsheet.<\/em><\/p>\n<p><strong>How to open Script Editor :<br \/>\n<\/strong><\/p>\n<p>In Google spreadsheet tool-bar there is an option <strong>TOOLS &gt; SCRIPT EDITOR<\/strong>.<\/p>\n<p><strong>Script Editor IDE looks like:<\/strong><br \/>\n<img decoding=\"async\" loading=\"lazy\" class=\"alignnone  wp-image-33366\" src=\"\/blog\/wp-ttn-blog\/uploads\/2016\/04\/Google-Script-Editor-IDE1.png\" alt=\"Google Script Editor IDE\" width=\"515\" height=\"243\" \/><\/p>\n<p><strong>Reading and Writing information in Google SpreadSheet using\u00a0a Google Apps Script :<\/strong><\/p>\n<p>I&#8217;ll demonstrate the use of Google Apps Script with the help of an example. Let&#8217;s say you wish to create a menu which reads data from \u201cProduct sheet\u201d and then calculates the total amount (quantity * Amount of individual) for each individual. It then transfers this information and puts it in a new sheet titled \u201cTotal Amount Sheet\u201d.<\/p>\n<p>In order to ensure that this example works as expected, we&#8217;ve created two sheets named &#8220;Product Sheet&#8221; and &#8220;Total Amount Sheet&#8221; in the same spreadsheet document.<\/p>\n<p>Here is a\u00a0sample of the &#8220;Product Sheet&#8221;<\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\"alignnone  wp-image-33370\" src=\"\/blog\/wp-ttn-blog\/uploads\/2016\/04\/Product-Sheet.png\" alt=\"Product Sheet\" width=\"356\" height=\"207\" \/><\/p>\n<p>Now open up the scripts editor and paste the code given below and save the script afterwards.<\/p>\n<p><strong>Code :<\/strong><\/p>\n<p>[js]<br \/>\n  function onOpen(e){<br \/>\n    var ui = SpreadsheetApp.getUi();<br \/>\n    ui.createMenu(&#8216;Reports&#8217;)<br \/>\n      .addItem(&#8216;Total Amount\u00a0Report&#8217;, &#8216;fetchDataAndCalculateAmountAndQuantity&#8217;)<br \/>\n      .addToUi();<br \/>\n  }<\/p>\n<p>  function fetchDataAndCalculateAmountAndQuantity(){<br \/>\n\tvar spreadsheet = SpreadsheetApp.getActiveSpreadsheet();<br \/>\n    var productSheet = spreadsheet.getSheetByName(&#8216;Product Sheet&#8217;);<br \/>\n    var productSheetValues = productSheet.getDataRange().getValues();<br \/>\n    var productSheetA1Notation = productSheet.getDataRange().getValues();<br \/>\n    for(var i=0;i&amp;lt;productSheetValues.length;i++) {<br \/>\n\t\tif(i != 0){<br \/>\n\t\t\tvar productAmtAndQty = calculateAmountAndQty(productSheetValues[i][1], productSheetValues[i][2])<br \/>\n\t\t\tproductSheetValues[i].push(productAmtAndQty)<br \/>\n\t\t} else {<br \/>\n\t\t\tproductSheetValues[i].push(&#8216;Total Amount&#8217;);<br \/>\n\t\t}<\/p>\n<p>\t\tupdateTotalAmountSheet(productSheetValues)<br \/>\n\t}<\/p>\n<p>\tfunction calculateAmountAndQty(quantity,amount) {<br \/>\n\t\tif(typeof (quantity) == &#8216;undefined&#8217; || quantity == &#8216;null&#8217; || isNaN(quantity)) {<br \/>\n\t\t\tquantity=0;<br \/>\n\t\t}<\/p>\n<p>\t\tif(typeof (amount) == &#8216;undefined&#8217; || amount == &#8216;null&#8217; || isNaN(amount)) {<br \/>\n\t\t\tamount=0;<br \/>\n\t\t}<\/p>\n<p>\t\treturn (quantity*amount);<br \/>\n\t}<\/p>\n<p>\tfunction updateTotalAmountSheet(productQuantityAmountAndTotalAmount) {<br \/>\n\t\tvar spreadsheet = SpreadsheetApp.getActiveSpreadsheet();<br \/>\n\t\tvar totalAmountSheet = spreadsheet.getSheetByName(&#8216;Total Amount Sheet&#8217;);<br \/>\n\t\ttotalAmountSheet.getRange(&quot;A1:D5&quot;).setValues(productQuantityAmountAndTotalAmount)<br \/>\n\t}<br \/>\n\t[\/js]<\/p>\n<p>Although the code is self-explanatory, there are some functions that need to be explained a bit more:<\/p>\n<ul>\n<li style=\"font-family: 'open_sansregular';font-size: 14px !important\"><strong>onOpen(e) :<\/strong> runs when a user opens a spreadsheet.<\/li>\n<li style=\"font-family: 'open_sansregular';font-size: 14px !important\"><strong>SpreadsheetApp.getUi()<\/strong> : Returns an instance of the spreadsheet&#8217;s user-interface environment that allows the script to add features like menus, dialogs, and sidebars.<\/li>\n<li style=\"font-family: 'open_sansregular';font-size: 14px !important\"><strong>SpreadsheetApp.getActiveSpreadsheet()<\/strong> : Returns the currently active spreadsheet, or null if there is none.<\/li>\n<li style=\"font-family: 'open_sansregular';font-size: 14px !important\"><strong>spreadsheet.getSheetByName(&#8220;Product Sheet&#8221;) <\/strong>: Returns a sheet with the \u201cProduct Sheet\u201d name.<\/li>\n<li style=\"font-family: 'open_sansregular';font-size: 14px !important\"><strong>productSheet.getDataRange():<\/strong> Returns a Range corresponding to the dimensions in which data is present.<\/li>\n<li style=\"font-family: 'open_sansregular';font-size: 14px !important\"><strong>productSheet.getDataRange().getValues() <\/strong>: Returns the rectangular grid of values for this range.<\/li>\n<li style=\"font-family: 'open_sansregular';font-size: 14px !important\"><strong>totalAmountSheet.getRange(&#8220;A1:D5&#8221;)<\/strong> : Returns the range of \u201cTotal Amount Sheet\u201d as specified in A1 notation.<\/li>\n<li style=\"font-family: 'open_sansregular';font-size: 14px !important\"><strong>totalAmountSheet.getRange(&#8220;A1:D5&#8221;).setValues(productQuantityAmountAndTotalAmount)<\/strong> : Sets a rectangular grid of values in \u201cTotal amount sheet\u201d.<\/li>\n<\/ul>\n<p>When you reload your Google Spreadsheet, it will show a &#8220;Reports&#8221; icon on menu bar with the \u201cCreate Total Amount Report\u201d as a sub-menu of &#8220;Reports&#8221; icon. When we click on \u201cCreate Total Amount Report\u201d then it will call function \u201cfetchDataAndCalculateAmountAndQuantity\u201d from our newly created script\u00a0and update the second sheet titled \u201cTotal amount Sheet\u201d.<\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\"alignnone  wp-image-33380\" src=\"\/blog\/wp-ttn-blog\/uploads\/2016\/04\/Total-Amout-Sheet.png\" alt=\"Total Amout Sheet\" width=\"406\" height=\"217\" \/><\/p>\n<p><strong>Note :<\/strong> When you run this script then initially your app needs authorization to run. Click Continue and then Click on Allow.<\/p>\n<p>Google Apps Script makes it easy to create and publish add-ons in an online store for Google Sheets, Docs, and Forms. It is very easy to use and can be easily used to automate any task performed, irrespective of its complexity. I&#8217;ll try to cover more such use cases in subsequent blogs and get our hands dirty in the process.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>What is Google Apps Script ? Google Apps Script is a JavaScript based scripting language that lets you add functionality to your Google Apps. It is a cloud-based language that integrates with all other Google services include Gmail, Google Drive, Calendar, Google Forms, Spreadsheets and more. We create and edit our scripts in an online [&hellip;]<\/p>\n","protected":false},"author":76,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":17},"categories":[1],"tags":[3173],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/33361"}],"collection":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/users\/76"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/comments?post=33361"}],"version-history":[{"count":0,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/33361\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/media?parent=33361"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/categories?post=33361"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/tags?post=33361"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}