An Introduction to Google Apps Script

Google sheet is indeed a powerful spreadsheet. The google sheet have almost all Microsoft-Excel functions and feature.

The Google Sheet early interface. Image courtesy of Techcrunch

Google Sheets originated from XL2Web, a web-based spreadsheet application developed by 2Web Technologies, which was acquired by Google in 2006. Back in 2006, the Excel was only available on offline and personally, makes some of the work quite frustrating.

What google sheet features that I personally like:

• It’s free and it’s online. With this capabilities, I can create as many files for free without worrying losing of data. It stores all histories I’ve made on the file and it has versioning function that i can create multiple versions on one file. With it’s online capabilities, I can refer, embed or get data from another sheet by using it’s sheets URL.
• It works on any devices. The google sheet can be accessed on the browser, mobile apps on Android and IOS. It super useful for me to view and edit a file on the go, whenever and wherever.
• The function as spreadsheet is enough and even I can extend it’s potential by download an add-on or create a custom script.

Introduction to Google Apps Script

Google Apps Script is a scripting platform for creating custom functions, do the automation, connect with other Google Apps (Google Drive, Gmail, Calendar and more). Google Apps Script coding language uses Javascript.

Google Apps Script Sample Use Cases:

• Google form collect data and manage in the sheets.
• Auto add Google Contact from the sheets.
• Create event calendar by adding data in the sheets.
• Create invoice template in Google Docs by data from sheets.
• Send email response from Google Sheet.
• Scraping data from website into Google Sheet.
• Create custom formula to calculate distance by latitude and longitude.
• And many more.

Basically you can create anything you can think of on the top of all Google Apps.

The types of Google Apps Script

• Standalone Script. A standalone script is any script that is not bound to a Google Sheets, Docs, Slides, or Forms file or Google Sites. These scripts appear among your files in Google Drive.
• Bound Script. A Bound scripts extend the functionality of a certain file in one of your Google apps.

Interface

Toolbar has the following options:

1. Redo/Undo button: To redo/undo changes you have done in the script
2. Save button: You can use this button to save any changes to your script. You can also use the keyboard shortcut Control + S (Windows) or Command + S (Mac).
3. Run button: Use this to run the script. In case you have multiple functions, select any line in the one you want to run and then click on the Run button.
4. Debug button: Debug helps you find errors in your code and also gives you some useful information.
5. Current Project Trigger’s button: When you click on this button, it will open the Triggers dashboard that lists all the triggers you have. A trigger is anything that triggers the running of code. For example, if you want a code to run and enter the current date and time in cell A1 whenever someone opens the Google Sheets, then you will use a trigger to do this.
6. Select Function: This is a drop-down that would list all your functions in the script file. This is useful when you have many functions in the script and want to run a specific one. You can simply select the name from here and then click on the run button (or debug it if you want).

Your First Script

Getting Started

Below example, I will explain on how to create bound google apps script.

function myFunction() {

}

List all available reference on Google Sheet you can found here.

In this guideline, I want to get the value of the sheet cell.

Here is the script I used to get the cell on the first row and the first column.

function getValue() {
var ss = SpreadsheetApp.getActiveSpreadsheet(); //to retrieve active sheet
var sheet1 = ss.getSheetByName("Sheet1");
var value = sheet1.getRange(1,1,1,1).getValue();
Logger.log(value)
}

We need to use getValue() to obtain the cell value by defining the class getRange(). To obtain coordinates by defininggetRange(row, column, numRows, numColumns), you can later get the value of the cell.

rowThe starting row index of the range; row indexing starts with 1.columnThe starting column index of the range; column indexing starts with 1.numRowsThe number of rows to return.numColumnsThe number of columns to return.

sheet1.getRange(1,1,1,1) // get the 1st row (1), 1st column (A),one row & one column. 

Herewith I will try to get value on the sheet.

After you done code your script, it is recommended to rename the project into something meaningful to ease the management and authorization.

You need to save the project by clicking the ‘floppy disk’ icon on the toolbar.

Since this the first run of the script, Google asks you to authorize the script

In our situation, since we’re the creator of the software, we know it’s secure, so we want to continue going. Click the “Advanced” button in the bottom left of the review permissions pop-up, and then click the “Go to new script (unsafe)” at the bottom of the next screen to continue.

You can use Logger class to output the logs into text messages that useful to debug your code.

After you can get the value of the cell defined, you copy to the next column by adding the following code:

sheet1.getRange(1,2,1,1).setValue(value);

After add setValue() and run the script, you will see that the value is now copied to the column B.

Further resources on Google Apps Script:

• Official Google Documentation
• G Suite Developers Blog
• Learn Apps Script fundamentals with codelabs
• Google Apps Script Code Samples by Amit Agarwal
• Apps Script Blastoff! — Introductory course by Ben Collins
• App script example by appsscript.info
• Google Apps Script Examples
• Stackoverflow