Google Apps Script lets you do new and cool things with Google Sheets. You canuse Apps Script to add custom menus,dialogs, and sidebars to Google Sheets. It alsolets you write custom functions forSheets, as well as integrate Sheets with otherGoogle services like Calendar, Drive, andGmail.
Most scripts designed for Google Sheets manipulate arrays tointeract with the cells, rows, and columns in a spreadsheet. If you're notfamiliar 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 the5-minute quickstart guide forMacros, Menus, and Custom Functions.
Get started
Apps Script includes special APIs to let you programmatically create, read, andedit Google Sheets. Apps Script can interact with Google Sheets in two broadways: any script can create or modify a spreadsheet if the script's user hasappropriate permissions for the spreadsheet, and a script can also bebound to a spreadsheet, whichgives the script special abilities to alter the user interface or respond whenthe spreadsheet is opened. To create a bound script, selectExtensions > Apps Script from withinGoogle Sheets.
The Spreadsheet service treats GoogleSheets as a grid, operating with two-dimensional arrays. To retrieve the datafrom the spreadsheet, you must get access to the spreadsheet where the data isstored, get the range in the spreadsheet that holds the data, and then get thevalues of the cells. Apps Script facilitates access to the data by readingstructured 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 ina spreadsheet, as shown in the image below.
The example below shows how to retrieve and log the product names and productnumbers.
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, click Execution log.
Writing data
To store data, such as a new product name and number to thespreadsheet, 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 thevalues specified. If you run this function, you'll see a new row added to thespreadsheet.
Custom menus and user interfaces
You can customize Google Sheets by adding custom menus, dialog boxes, andsidebars. To learn the basics of creating menus, see theguide to menus. To learn about customizing thecontent of a dialog box, see theguide to HTML service.
You can also attach a script function to an image or drawing within aspreadsheet; the function will execute when a user clicks on the image ordrawing. 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 thestyle 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 featurecan 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 ManagingResponses 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 followingexample 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 Sheetsor create new rules. For instance, the following sample shows how to set adata-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 aspecific range. The following example generates an embedded bar chart, assumingyou 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 andspecific chart builders, such asEmbeddedPieChartBuilder.
Custom functions in Google Sheets
A custom function is similar to abuilt-in spreadsheet function like =SUM(A1:A5) except that you define thefunctions’s behavior with Apps Script. For example, you could create a customfunction, in2mm(), that converts a value from inches to millimeters, then usethe formula in your spreadsheet by typing =in2mm(A1) or =in2mm(10) into acell.
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 throughthe Google Sheets menu. For more information, seeGoogle Sheets Macros.
Add-ons for Google Sheets
Add-ons are specially packaged Apps Scriptprojects that run inside Google Sheets and can be installedfrom the Google Sheets add-on store. If you've developed a script for Google Sheetsand want to share it with the world, Apps Script lets youpublish your script as anadd-on so other users can install it from the add-on store.
Triggers
Scripts that are bound to a Google Sheets filecan use simple triggers like the functionsonOpen() and onEdit() to respond automatically when a user who has editaccess to the spreadsheet opens or edits the spreadsheet.
Like simple triggers,installable triggers let Google Sheetsrun a function automatically when a certain event occurs. Installabletriggers, however, offer more flexibility than simple triggers and supportthe following events: open, edit, change, form submit, and time-driven (clock).
