Overview

Extending Google Sheets

Google Apps Script lets you do new and cool things with Google Sheets. You can use Apps Script to add custom menus, dialogs, and sidebars to Google Sheets. It also lets you write custom functions for Sheets, as well as integrate Sheets with other Google services like Calendar, Drive, and Gmail.

Most scripts designed for Google Sheets manipulate arrays to interact with the cells, rows, and columns in a spreadsheet. If you're not familiar with arrays in JavaScript, Codecademy offers a great training module for arrays. (Note that this course wasn't developed by and isn't associated with Google.)

For a quick introduction to using Apps Script with Google Sheets, see the 5-minute quickstart guide for Macros, Menus, and Custom Functions.

Get started

Apps Script includes special APIs to let you programmatically create, read, and edit Google Sheets. Apps Script can interact with Google Sheets in two broad ways: any script can create or modify a spreadsheet if the script's user has appropriate permissions for the spreadsheet, and a script can also be bound to a spreadsheet, which gives the script special abilities to alter the user interface or respond when the spreadsheet is opened. To create a bound script, select Tools > Script editor from within Google Sheets.

The Spreadsheet service treats Google Sheets as a grid, operating with two-dimensional arrays. To retrieve the data from the spreadsheet, you must get access to the spreadsheet where the data is stored, get the range in the spreadsheet that holds the data, and then get the values of the cells. Apps Script facilitates access to the data by reading structured data in the spreadsheet and creating JavaScript objects for them.

Reading data

Suppose you have a list of product names and product numbers that you store in a spreadsheet, as shown in the image below.

function logProductInfo() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for (var i = 0; i < data.length; i++) {
    Logger.log('Product name: ' + data[i][0]);
    Logger.log('Product number: ' + data[i][1]);
  }
}

Writing data

To store data, such as a new product name and number to the spreadsheet, add the following code to the end of the script.

function addProduct() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);
}

Connecting to Google Forms

Apps Script allows you to connect Google Forms with Google Sheets through Forms and Spreadsheet services. This feature can automatically create a Google Form based on data in a spreadsheet. Apps Script also enables you to use triggers, such as onFormSubmit to perform a specific action after a user responds to the form. To learn more about connecting Google Sheets to Google Forms, try the Managing Responses for Google Forms 5-minute quickstart.

Formatting

The Range class has methods like setBackground(color) to access and modify the format of a cell or range of cells. The following example shows how you can set the font style of a range:

function formatMySpreadsheet() {
  // Set the font style of the cells in the range of B2:C2 to be italic.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var cell = sheet.getRange('B2:C2');
  cell.setFontStyle('italic');
}

Data validation

Apps Script lets you access existing data-validation rules in Google Sheets or create new rules. For instance, the following sample shows how to set a data-validation rule that allows only numbers between 1 and 100 on a cell.

function validateMySpreadsheet() {
  // Set a rule for the cell B4 to be a number between 1 and 100.
  var cell = SpreadsheetApp.getActive().getRange('B4');
  var rule = SpreadsheetApp.newDataValidation()
     .requireNumberBetween(1, 100)
     .setAllowInvalid(false)
     .setHelpText('Number must be between 1 and 100.')
     .build();
  cell.setDataValidation(rule);
}

Charts

Apps Script lets you embed charts in a spreadsheet that represent the data in a specific range. The following example generates an embedded bar chart, assuming you have chartable data in cells A1:B15:

function newChart() {
  // Generate a chart representing the data in the range of A1:B15.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.BAR)
     .addRange(sheet.getRange('A1:B15'))
     .setPosition(5, 5, 0, 0)
     .build();

  sheet.insertChart(chart);
}