Data Analysis 1
Lecture 16
Apps Script in GS (Part 2)
Apps Script: FUNCTIONS
Functions are blocks of code designed to perform a specific task. A function is run (executed) when
something calls it (invokes it).
Functions can be declared (created) with the word function followed by the function name.
// traditional function declaration syntax
function functionName(argument1, argument2, argument3) {
//code to be executed when function is called (or invoked)
// argument1, arguemnt2, argument3 are available
// to do stuff with inside the function
// return clause tells function what "answer" to return
return someAnswer;
}
Apps Script: VALUES
There are six types of values in Apps Script. A value's type tells you the type of data it is. The six types are:
Number: Any numerical value (e.g., 3.15, 27, -50, 1e4)
String: A piece of text within quotation marks (e.g., "apple", "school", "Hi there!", "1234")
Boolean: A value that is true or false. (Note: there are no quotes surrounding boolean values, i.e.,
they are not strings)
Object: A value that contains other values.
Function: A value that is reusable code.
Undefined: A value that is empty so its type is unknown.
Apps Script: OPERATORS
Operators are used to perform operations on one or more values. For example, the addition operator (+) is
used to add multiple values together.
There are three main types of operators: Numeric, Comparison and Logical.
Apps Script: OPERATORS
1. Numeric operators
Numeric operators are used to perform mathematical operations on numeric values. There main ones are:
o Addition: +
o Subtraction: -
o Multiplication: *
o Division: /
o Remainder: %
Apps Script: OPERATORS
2. Comparison operators
Comparison operators are used to compare two values. For example, the > (greater than) operator is used to check if a value is greater
than another value.
When you compare two values the result is either true or false, i.e., a boolean value. For example, 5 > 1 results in true but 1 > 20 will
result in false.
Apps Script: OPERATORS
There are six comparison operators. They are:
o Greater than (>): a > b results in true if the value a is greater than value b and false if a is less than or equal to b.
o Greater than or equal to (>=): a >= b results in true if the value a is greater than or equal to value b and false if a is less than b.
o Less than (<): a < b results in true if the value a is lesser than value b and false if a is greater than or equal to b.
o Less than or equal to (<=): a <= b results in true if the value a is lesser than or equal to value b and false if a is greater than b.
o Equality (==): a == b results in true if the value a is equal to value b.
o Strict equality (===): a === b results in true if the value a is equal to value b and their types are also the same.
o Inequality (!=): a != b results in true if both values are of the same type but value a is not equal to value b.
o Strict inequality (!==): a !== b results in true if the value a is not equal to value b OR if their types are different.
Apps Script: VARIABLES and
CONSTANTS
Variables are used to store values.
To assign a value to a variable, you need to first declare the variable and then assign a value to it.
var name = [Link]();
Constants are just like variables except you cannot change its value after assigning one to it. To declare a
constant, use the keyword const instead of var.
const a = 5;
Apps Script: ARRAYS
An array is a special type of object that is used to store a list of values. You will use arrays extensively
while working with Google Sheets using Apps Script.
function testArrays (){
var colors = ["red", "blue", "green", "black", "orange", "purple"];
[Link](colors);
}
Arrays have an indexing system to help you access values stored at specific positions. The indexing
system starts at 0 and not 1. The color at index 0 is "red", 1 is "blue" and so on. Since the indexing
system starts at 0, the last value in the array will have the index N - 1, where N is the number of values in
the array.
function testArrays (){
var colors = ["red", "blue", "green", "black", "orange", "purple"];
[Link](colors[0]); //red
[Link](colors[1]); //blue
[Link](colors[2]); //green
[Link](colors[3]); //black
[Link](colors[4]); //orange
[Link](colors[5]); //purple
}
//push new color
Apps Script: LOOPS
A loop is a piece of code that runs a set of statements multiple times. Each run is called an iteration. There
are two common types of loops in Apps Script.
• For loop: it is used to run a set of statements a certain number of times. A For loop is usually used
when you know the number of iterations in advance.
• While loop: it is used to keep running a set of statements as long as some condition is met. A While
loop is used in situations where you don't know the number of iterations in advance.
for (Initialization, Condition, Increment) for (var i = 0; i <= 5; i++ ) {
{ [Link](colors[i]);
//set of instructions }
}
Apps Script: For Each Method
The Array method forEach() is used to execute a function for every element in an array. It is also
referred to as the forEach() loop because it is a common way to loop through every element of the
array to take some action on each element.
function testArrays (){
Syntax of forEach() method: var colors = ["red", "blue", "g
reen", "black", "orange", "purp
// Arrow function le"];
forEach((element) => { /* ... */ }) [Link](function(element
forEach((element, index) => { / ,index)
* ... */ }) {
[Link](element + " " + inde
// Inline callback function x);
forEach(function(element) { /* ... */ }) })
forEach(function(element, index) { / }
* ... */ })
Practice Example
1. The Spreadsheet service and getting data
out of sheets;
2. Working with data from sheets;
3. Inserting data back into sheets;
4. Adding custom menus to run programs;
5. Adding formulas with Apps Script;
6. Formatting data in GS with Apps Script;
Practice Example: 1. The Spreadsheet service
and getting data out of sheets
// get name of our active spreadsheet // get name of our active sheet inside of ss // get name of our active sheet inside
sheet of ss
function getNameOfSheet(){ function getNameOfSheet(){
function getNameOfSheet(){ var ss = [Link](); var ss = [Link]
var ss = [Link](); name = [Link](); heet();
name = [Link](); [Link](name); name = [Link]();
[Link](name); } [Link](name);
} }
// get data out of active sheet inside of spreadshe // get data out of active sheet inside of spreadsheetsheet (more direct me
etsheet thod)
function getDataOutOfSheet(){ function getDataOutOfSheet(){
var ss = [Link](); var ss = [Link]();
sheet = [Link](); [Link]([Link]().getValues());
data = [Link](); }
values = [Link](); // get data out of active sheet by name of sheet
[Link](values); function getDataOutOfSheet(){
} var ss = [Link]();
// get active range data in sheet sheet = [Link]('Fruits');
function getDataOutOfSheet(){ [Link]([Link]().getValues());
var ss = [Link](); }
sheet = [Link]('Fruits');
[Link]([Link]().getValues());
}
Practice Example: 1. The Spreadsheet service
and getting data out of sheets
// get data by A1 notation in sheet
function getDataOutOfSheet(){
var ss = [Link]();
sheet = [Link]('Fruits');
[Link]([Link]('A1:C8').getValues());
}
// get data by range coordinates in a sheet
function getDataOutOfSheet(){
var ss = [Link]();
sheet = [Link]('Fruits');
data1 = [Link](1,1).getValue();
[Link](data1);
data2 = [Link](1,1,4,3).getValues();
[Link](data2);
data3 = [Link](1,1,7,2).getValues();
[Link](data3);
}
Practice Example: 2. Working with data from
sheets
// calculation of row totals
function calculationOfRowTotals(){
// get spreadsheet, sheet, range, data
var ss=[Link]();
sheet = [Link]('Fruits');
data = [Link](2,1,6,3).getValues();
//[Link](data);
//loop over the data and calculate
[Link](row =>{
[Link](row);
var name = row[0];
cost = row[1];
quantity = row[2];
total = cost*quantity;
[Link](name + ': ' + total);
});
}
Practice Example: 3. Inserting data back into
sheets
// insert values back into sheet
function calculationOfRowTotals(){
// get spreadsheet, sheet, range, data
var ss=[Link]();
sheet = [Link]('Fruits');
lastRow = [Link]();
//[Link](lastRow);
data = [Link](1,1,lastRow,3).getValues();
//[Link](data);
[Link]();
[Link](data);
var totalsArray = [];
[Link](row =>{
[Link](row);
var name = row[0];
cost = row[1];
quantity = row[2];
total = cost*quantity;
[Link]([total]);
});
[Link](totalsArray);
// insert new array values into sheet
[Link](2,4,lastRow-1,1).setValues(totalsArray);
}
Practice Example: 4. Adding custom menus to
run programs
// add new with function
// onOpen is a special function that runs when your
sheet opens
function onOpen(){
ui = [Link]();
[Link]('Custom Menu')
.addItem('Calculate Total','calculationOfRowTotals'
)
.addToUi();
}
Practice Example: 5. Adding formulas with Apps
Script
// add grand total custom function
function addGrandTotal(){
// get spreadsheet, sheet, range, data
var ss=[Link]();
sheet = [Link]('Fruits');
lastRow = [Link]();
lastColumn = [Link]();
// add grand total row with value
[Link](lastRow+1,1).setValue('Grand Tota
l');
[Link](lastRow+1,lastColumn).setFormul
a('=SUM(D2:D7)');
}
Practice Example: 6. Formatting data in GS with
Apps Script
// format the table
function formatTable(){
var ss = [Link]();
sheet = [Link]('Fruits');
dataRange = [Link]();
lastRow = [Link]();
lastColumn = [Link]();
[Link]([Link],tru
e,true);
[Link](2,lastColumn,lastRow,1).setNumberFormat('$0.00');
[Link](lastRow,lastColumn, 1,1).setFontWeight('bold');
}
Library: SpreadsheetApp
Method Return type Brief description
Returns the currently active spreadsheet, or null if there
getActive() Spreadsheet
is none.
Returns the selected range in the active sheet, or null if
getActiveRange() Range
there is no active range.
getActiveSheet() Sheet Gets the active sheet in a spreadsheet.
Returns the currently active spreadsheet, or null if there
getActiveSpreadsheet() Spreadsheet
is none.
getCurrentCell() Range Returns the current (highlighted) cell that is selected in
one of the active ranges in the active sheet or null if
there is no current cell.
getUi() Ui Returns an instance of the spreadsheet's user-interface
environment that allows the script to add features like
menus, dialogs, and sidebars.
getRange() Range Returns the range for this banding.
Full list is here
Library: Sheet and Range
Method Return type Brief description
getLastColumn() Integer Returns the position of the last column that has
content.
getLastRow() Integer Returns the position of the last row that has
content.
getName() String Returns the name of the sheet.
getRange(row, column, num Range Returns the range with the top left cell at the
Rows, numColumns) given coordinates with the given number of rows
and columns.
getSheetName() String Returns the sheet name.
setValue(value) Range Sets the value of the range.
setValues(values) Range Sets a rectangular grid of values (must match
dimensions of this range).
Full list is here
Library: Sheet and Range
Method Return type Brief description
setNumberFormat(numberForm Range Sets the number or date format to the given formatting
at) string.
setBackground(color) Range Sets the background color of all cells in the range in
CSS notation (such as '#ffffff' or 'white').
setBorder(top, left, bottom, righ Range Sets the border property with color and/or style.
t, vertical, horizontal, color, styl
e)
setFontWeight(fontWeight) Range Set the font weight for the given range (normal/bold).
setFormula(formula) Range Updates the formula for this range.
setNumberFormat(numberForm Range Sets the number or date format to the given formatting
at) string.
setValues(values) Range Sets a rectangular grid of values (must match
dimensions of this range).
Full list is here
Library: Browser
Method Return type Brief description
inputBox(prompt) String Pops up a dialog box with a text input box
in the user's browser.
msgBox(prompt) String Pops up a dialog box with the given
message and an OK button in the user's
browser.
Full list is here
Library: Arrays
Method Return type Brief description
length Number Returns the number of elements in that array
pop String Removes the last element from an array and returns that
element. This method changes the length of the array.
push String Adds one or more elements to the end of an array and
returns the new length of the array.
shift String removes the first element from an array and returns that
removed element. This method changes the length of the
array.
unshift String Adds one or more elements to the beginning of an array
and returns the new length of the array.
splice String Changes the contents of an array by removing or
replacing existing elements and/or adding new elements
in place.
typeof String operator returns a string indicating the type of the
unevaluated operand.
