Extending Google Sheets | Application script | Google Developers – ABOUT MAG 2020

The Google Apps Script allows you to do new and interesting things with Google Sheets. You can
use Google Apps Script to add custom menus,
dialog boxes and sidebars for Google Sheets. This too
lets write custom functions for
Spreadsheets, as well as integrate Spreadsheets with other
Google services like Calendar, Drive and
Gmail.

Most of the scripts created for Google Sheets manipulate arrays to
interact with cells, rows and columns in a spreadsheet. If you are not
familiar with JavaScript arrays, Codecademy offers a
great training module for matrices.
Please note that this course was not developed and is not associated with Google.

For a quick introduction to using Apps Apps with Google Sheets, see the
5-minute quick start guide for
Macros, menus and custom functions.

start

The Google Apps Script includes special APIs to allow you to create, read and
edit Google Sheets. The Google Apps Script can interact with Google Sheets in two ways
ways: any script can create or modify a spreadsheet if the user of the script has
appropriate permissions for the spreadsheet and a script can also be
limit for a spreadsheet, which
provides the script with special skills to change the user interface or respond when
the spreadsheet is opened. To create a linked script, select
Tools> Script Editor in Google Sheets.

O Spreadsheet service treats Google
Sheets like a grid, operating with two-dimensional arrays. To recover the data
of the spreadsheet, you must have access to the spreadsheet where the data is
stored, get the range in the spreadsheet that contains the data and get the
cell values. The Google Apps Script makes it easy to access data by reading
structured data in the spreadsheet and created JavaScript objects for them.

Reading data

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

The example below shows how to retrieve and register product names and products.
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]);
  }
}

To view the recorded data, choose View> Logs of the script
editor. (If you don't remember how to create and run a script, see the
5-minute quick start guide for
Custom menus and functions)

Recording data

To store data, such as a new product name and number, in 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 code above attaches a new line at the bottom of the spreadsheet, with the
specified values. If you perform this function, you will see a new line added to the
spreadsheet.

Custom menus and user interfaces

You can customize Google Sheets by adding custom menus, dialogs, and
side bars. To learn the basics of creating menus, see the
menu guide. To learn how to customize the
contents of a dialog box, see the
guide to the HTML service.

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

If you plan to publish your custom interface as part of a
add, Follow the
Style Guide for consistency with the
Google Sheets editor style and layout.

Connecting to Google Forms

The Google Apps Script allows you to connect Google Forms to Google Sheets via
Forms and
Spreadsheet Services. This feature
you can automatically create a Google form based on spreadsheet data.
Application scripting also allows 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 to Google Forms
5 minute quick start.

Formatting

O Range class has methods like
setBackground(color)

to access and modify the format of a cell or range of cells. The following
The example shows how you can set the font style for 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');
}

Expiration date

The Google Apps Script allows you to access existing data validation rules in Google Sheets
or create new rules. For example, the following example shows how to define a
data validation rule that allows only numbers between 1 and 100 in 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 how to work with data validation rules, see
SpreadsheetApp.newDataValidation(),
DataValidationBuilder,
and Range.setDataValidation(rule)

Graphics

The Application Script allows you to embed charts in a spreadsheet that represents data in a
specific range. The following example generates an embedded bar chart, assuming
you have cartographic data in the 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 in your spreadsheet,
Watch EmbeddedChart and
specific chart builders like
EmbeddedPieChartBuilder.

Custom functions in Google Sheets

AN custom function is similar to a
spreadsheet function embedded as =SUM(A1:A5) except that you define the
function behavior with Google Apps Script. For example, you can create a custom
occupation, in2mm(), which converts a value from inches to millimeters and then use
the formula in your spreadsheet by typing =in2mm(A1) or =in2mm(10) inside of
cell.

To learn more about custom functions, try the
Custom menus and functions
5 minute quick start or see more detailed details
guide to custom functions.

Macros

Macros are another way to run the Application Script code in the Google Sheets user interface.
Unlike custom functions, you activate them with a keyboard shortcut or via
the Google Sheets menu. For more information, see
Google Sheets macros.

Add-ons for Google Sheets

Add-ons are specially packaged application scripts
projects run on Google Sheets and can be installed
from the Google Sheets companion store. If you developed a script for Google
Spreadsheets and want to share it with the world, Apps Script allows you to
publish your script as a
add-on so that other users can install it in the add-ons store.

Triggers

Scripts that are limit to a Google Sheets file
can use simple triggers as the functions
onOpen() and onEdit() to respond automatically when a user who edited
spreadsheet access opens or edits the spreadsheet.

Like simple triggers,
installable triggers let Google
The leaves perform 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, submit form and control the time (clock).

Paula Fonseca