Extending Google Sheets (original) (raw)

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 otherGoogle 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 agreat 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 forMacros, 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 bebound 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, selectExtensions > Apps Script 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.

The example below shows how to retrieve and log the product names and product numbers.

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]);
  }
}

View logs

To view the data that has been logged, at the top of the script editor, clickExecution log.

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']);
}

The above code appends a new row at the bottom of the spreadsheet, with the values specified. If you run this function, you'll see a new row added to the spreadsheet.

Custom menus and user interfaces

You can customize Google Sheets by adding custom menus, dialog boxes, and sidebars. To learn the basics of creating menus, see theguide to menus. To learn about customizing the content of a dialog box, see theguide to HTML service.

You can also attach a script function to an image or drawing within a spreadsheet; the function will execute when a user clicks on the image or drawing. To learn more, see Images and Drawings in Google Sheets.

If you're planning to publish your custom interface as part of anadd-on, follow thestyle guide for consistency with the style and layout of the Google Sheets editor.

Connecting to Google Forms

Apps Script allows you to connect Google Forms with Google Sheets throughForms andSpreadsheet services. This feature can automatically create a Google Form based on data in a spreadsheet. Apps Script also enables you to use triggers, such asonFormSubmit 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 likesetBackground(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);
}

For more details on working with data-validation rules, seeSpreadsheetApp.newDataValidation(),DataValidationBuilder, and Range.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);
}

To learn more about embedding a chart into your spreadsheet, see EmbeddedChart and specific chart builders, such asEmbeddedPieChartBuilder.

Custom functions in Google Sheets

A custom function is similar to a built-in spreadsheet function like =SUM(A1:A5) except that you define the functions’s behavior with Apps Script. For example, you could create a custom function, in2mm(), that converts a value from inches to millimeters, then use the formula in your spreadsheet by typing =in2mm(A1) or =in2mm(10) into a cell.

To learn more about custom functions, try theMenus and Custom Functions5-minute quickstart, or take a look at the more in-depthguide to custom functions.

Macros

Macros are another way of executing Apps Script code from the Google Sheets UI. Unlike custom functions, you activate them with a keyboard shortcut or through the Google Sheets menu. For more information, seeGoogle Sheets Macros.

Add-ons for Google Sheets

Add-ons are specially packaged Apps Script projects that run inside Google Sheets and can be installed from the Google Sheets add-on store. If you've developed a script for Google Sheets and want to share it with the world, Apps Script lets youpublish your script as an add-on so other users can install it from the add-on store.

Triggers

Scripts that are bound to a Google Sheets file can use simple triggers like the functionsonOpen() and onEdit() to respond automatically when a user who has edit access to the spreadsheet opens or edits the spreadsheet.

Like simple triggers,installable triggers let Google Sheets run a function automatically when a certain event occurs. Installable triggers, however, offer more flexibility than simple triggers and support the following events: open, edit, change, form submit, and time-driven (clock).