Introduction to Google Apps Script with Google Spreadsheet

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 IDE and the scripts run on Google’s Cloud infrastructure.

What Apps Script do ?

Apps Script is versatile. You can :

• Add custom menus, dialogs, and sidebars to Google Docs, Sheets, and Forms.
• Write custom functions for Google Sheets.
• Publish web apps — either standalone or embedded in Google Sites.
• Interact with other Google services, including AdSense, Analytics, Calendar, Drive, Gmail, and Maps.

Here we are taking example with Google Spreadsheet.

How to open Script Editor :

In Google spreadsheet tool-bar there is an option TOOLS > SCRIPT EDITOR.

Script Editor IDE looks like:

Reading and Writing information in Google SpreadSheet using a Google Apps Script :

I’ll demonstrate the use of Google Apps Script with the help of an example. Let’s say you wish to create a menu which reads data from “Product sheet” 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 “Total Amount Sheet”.

In order to ensure that this example works as expected, we’ve created two sheets named “Product Sheet” and “Total Amount Sheet” in the same spreadsheet document.

Here is a sample of the “Product Sheet”

Now open up the scripts editor and paste the code given below and save the script afterwards.

Code :

[js]
function onOpen(e){
var ui = SpreadsheetApp.getUi();
ui.createMenu(‘Reports’)
.addItem(‘Total Amount Report’, ‘fetchDataAndCalculateAmountAndQuantity’)
.addToUi();
}

function fetchDataAndCalculateAmountAndQuantity(){
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var productSheet = spreadsheet.getSheetByName(‘Product Sheet’);
var productSheetValues = productSheet.getDataRange().getValues();
var productSheetA1Notation = productSheet.getDataRange().getValues();
for(var i=0;i<productSheetValues.length;i++) {
if(i != 0){
var productAmtAndQty = calculateAmountAndQty(productSheetValues[i][1], productSheetValues[i][2])
productSheetValues[i].push(productAmtAndQty)
} else {
productSheetValues[i].push(‘Total Amount’);
}

updateTotalAmountSheet(productSheetValues)
}

function calculateAmountAndQty(quantity,amount) {
if(typeof (quantity) == ‘undefined’ || quantity == ‘null’ || isNaN(quantity)) {
quantity=0;
}

if(typeof (amount) == ‘undefined’ || amount == ‘null’ || isNaN(amount)) {
amount=0;
}

return (quantity*amount);
}

function updateTotalAmountSheet(productQuantityAmountAndTotalAmount) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var totalAmountSheet = spreadsheet.getSheetByName(‘Total Amount Sheet’);
totalAmountSheet.getRange("A1:D5").setValues(productQuantityAmountAndTotalAmount)
}
[/js]

Although the code is self-explanatory, there are some functions that need to be explained a bit more:

• onOpen(e) : runs when a user opens a spreadsheet.
• SpreadsheetApp.getUi() : Returns an instance of the spreadsheet’s user-interface environment that allows the script to add features like menus, dialogs, and sidebars.
• SpreadsheetApp.getActiveSpreadsheet() : Returns the currently active spreadsheet, or null if there is none.
• spreadsheet.getSheetByName(“Product Sheet”) : Returns a sheet with the “Product Sheet” name.
• productSheet.getDataRange(): Returns a Range corresponding to the dimensions in which data is present.
• productSheet.getDataRange().getValues() : Returns the rectangular grid of values for this range.
• totalAmountSheet.getRange(“A1:D5”) : Returns the range of “Total Amount Sheet” as specified in A1 notation.
• totalAmountSheet.getRange(“A1:D5”).setValues(productQuantityAmountAndTotalAmount) : Sets a rectangular grid of values in “Total amount sheet”.

When you reload your Google Spreadsheet, it will show a “Reports” icon on menu bar with the “Create Total Amount Report” as a sub-menu of “Reports” icon. When we click on “Create Total Amount Report” then it will call function “fetchDataAndCalculateAmountAndQuantity” from our newly created script and update the second sheet titled “Total amount Sheet”.

Note : When you run this script then initially your app needs authorization to run. Click Continue and then Click on Allow.

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’ll try to cover more such use cases in subsequent blogs and get our hands dirty in the process.