How to Create a Google Apps Script in Google Sheets

How to Create a Google Apps Script in Google Sheets
Reading Time: 3 minutes

Objective

Learn how to create a Google Apps Script in Google Sheets.

What is Google Apps Script in Google Sheets?

The Google Apps Script is a rapid scripting platform for application development. You can create business applications that integrate with Google Workspace applications like Gmail, Calendar, Drive, etc. 

Get started by learning how to create a Google Apps Script in Google Sheets by writing a basic code for reading and writing cell data.

How to Create a Google Apps Script in Google Sheets?

Launch Script Editor

  • Select Extensions.
  • After that, select “Apps Script.”

Select Apps Script
Figure 1: Select Apps Script

  • This will open an editor window.
  • A default function Myfunction () is created.
  • Write your code inside this function to execute your code.

Default function myFunction() created
Figure 2: Default function myFunction() created

Write your code

  • Inside the myfunction (), write a code to read data from one cell, perform an operation on it, and deliver the output to another cell.
  • You can identify the cell by mentioning the row and column number.
  • To identify the cell, use the getRange () function.
  • Use the getValue() function to retrieve the value of that cell.
  • Below is an example of extracting value from cell A2.
  • The value is stored in a variable named value.
function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var row = 2;
   var col = 1;
   var value = sheet.getRange(row, col).getValue();
}
  • Now, the value is multiplied by 5.
  • Store the new value in a variable named “results.”
  • The new value is now set in cell B2 by using the setValue function, as shown below:
   var results = data * 5;
   sheet.getRange(row, col+1).setValue(results);
  • Therefore, the final code will look something like this:
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var row = 2;
var col = 1;
var value = sheet.getRange(row, col).getValue();
var results = value * 5;
sheet.getRange(row, col+1).setValue(results);
}

Save Code and Run

  • After completing your code, select the disc icon to save your code.
  • Now select the “Run” button.

Saving and running code
Figure 3: Saving and running code

  • The first time you run the myFunction() function, you’ll have to provide authorization for the code to run on your Google Account.

Review and grant permissions
Figure 4: Review and grant permissions

  • Grant the permissions by selecting Review Permissions and selecting your Google Account.
  • Once the code runs, you can view the calculation results in the target cell B2.

value after calculation shown in cell B2 using the knowledge of how to create a Google Apps Script in Google Sheets
Figure 5: Value after calculation shown in cell B2 using the knowledge of how to create a Google Apps Script in Google Sheets

See Also

Most Commonly Used Google Script Functions for 2022: Learn about widely used advanced script functions.

Delete Empty rows in Google Sheets: Learn how to use Slicers in Google Sheets and its variations.