Part 2: JavaScript and Google App Script

In this blog post, we will explore the foundations of JavaScript and Google App Script, the programming language that powers automation in Google Sheets. With plenty of code examples, you’ll learn the basics of JavaScript and how to apply them in Google App Script to enhance your Google Sheets experience.

What is JavaScript?

JavaScript is a versatile, widely-used programming language that runs in web browsers, allowing developers to create dynamic and interactive web applications. JavaScript can also run outside the browser, such as in the case of Google App Script, where it is used to automate tasks and create custom tools for Google Workspace applications.

Variables

In JavaScript, variables are used to store values. You can declare a variable using the var, let, or const keyword, followed by the variable name and an optional value assignment.

// Using var (not recommended in modern JavaScript but I use it a lot still)
var myVariable = 10;

// Using let (for variables that may change)
let anotherVariable = 20;

// Using const (for constants that should not change)
const constantVariable = 30;

Data Types

JavaScript (the language of Google App Script) has several data types. These are foundational but you can muddle your way through it as you figure things out. Data types include:

• Numbers: integers or floating-point numbers
• Strings: sequences of characters, enclosed in single or double quotes
• Booleans: true or false values
• Arrays: ordered collections of values, enclosed in square brackets
• Objects: collections of key-value pairs, enclosed in curly braces
• Functions: reusable code blocks that can be called by their name

// Numbers. Age is an integer because it doesn't have the decimal.
let age = 25;
let pi = 3.14;

// Strings
let name = 'John Doe';
let message = "Hello, world!";

// Booleans
let isTrue = true;
let isFalse = false;

// Arrays
let colors = ['red', 'green', 'blue'];
let numbers = [1, 2, 3, 4, 5];

// Objects
let person = {
  name: 'Jane Smith',
  age: 30,
  city: 'New York'
};

// Functions
function add(a, b) {
  return a + b;
}

Operators

JavaScript provides a variety of operators that allow you to perform operations on values, including arithmetic, comparison, logical, and assignment operators.

// Arithmetic operators
let sum = 10 + 5;        // 15
let difference = 10 - 5; // 5
let product = 10 * 5;    // 50
let quotient = 10 / 5;   // 2
let remainder = 10 % 3;  // 1

// Comparison operators
let isEqual = 10 == '10';     // true
let isStrictEqual = 10 === '10'; // false
let isGreater = 10 > 5;       // true
let isLess = 10 < 5;          // false

// Logical operators
let andResult = true && false; // false
let orResult = true || false;  // true
let notResult = !true;         // false

// Assignment operators
let a = 10;         // Assigns 10 to a
a += 5;             // Adds 5 to a (a = a + 5)
a -= 3;             // Subtracts 3 from a (a = a - 3)
a *= 2;             // Multiplies a by 2 (a = a * 2)
a /= 4;             // Divides a by 4 (a = a / 4)
a %= 2;              // Sets a to the remainder of a divided by 2 (a = a % 2)

// Other operators
let b = 5;
let c = ++b;    // Increment b before assignment (b = 6, c = 6)
let d = b++;    // Increment b after assignment (b = 7, d = 6)

Control Structures

Control structures in JavaScript help you manage the flow of your code. These include if, else, for, while, and switch statements.

// If statement
if (age > 18) {
  console.log('You are an adult.');
} else {
  console.log('You are not an adult.');
}

// For loop
for (let i = 0; i < 5; i++) {
  console.log(i);
}

// While loop
let count = 0;
while (count < 5) {
  console.log(count);
  count++;
}

// Switch statement
let day = 'Monday';
switch (day) {
  case 'Monday':
    console.log('Today is Monday.');
    break;
  case 'Tuesday':
    console.log('Today is Tuesday.');
    break;
  // Other cases...
  default:
    console.log('Invalid day.');
}

Google App Script Basics

Now that we’ve covered the fundamentals of JavaScript, let’s see how they apply to Google App Script for Google Sheets automation.

SpreadsheetApp

SpreadsheetApp is a top-level object in Google App Script that allows you to access and interact with Google Sheets. You can use it to open a spreadsheet, get the active sheet or cell, and perform various operations.

// Accessing the active spreadsheet
let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

// Accessing the active sheet
let sheet = SpreadsheetApp.getActiveSheet();

// Accessing a specific sheet by name
let anotherSheet = spreadsheet.getSheetByName('Sheet2');

// Accessing the active cell
let cell = SpreadsheetApp.getActiveRange();

Range

Range objects represent a cell or a group of cells in Google Sheets. You can use methods on Range objects to get and set cell values, formatting, and more.

// Getting the value of a cell
let value = sheet.getRange('A1').getValue();

// Setting the value of a cell
sheet.getRange('B1').setValue('Hello, world!');

// Getting values from multiple cells
let values = sheet.getRange('A1:B2').getValues();

// Setting values for multiple cells
sheet.getRange('A1:B2').setValues([
  [1, 2],
  [3, 4]
]);

//There are more options but we will stick here for now.

Don’t worry, we will cover this more in the next section.

Custom Functions

You can create custom functions in Google App Script that can be used directly in your Google Sheets, just like built-in functions.

/**
 * Multiply two numbers.
 *
 * @param {number} a The first number.
 * @param {number} b The second number.
 * @return The product of the two numbers.
 * @customfunction
 */
function multiply(a, b) {
  return a * b;
}

Now you can use =MULTIPLY(2, 3) in your Google Sheet to get the product of 2 and 3.

Conclusion

With a solid understanding of JavaScript and its application in Google App Script, you’re ready to harness the power of automation in your Google Sheets. Keep practicing and experimenting with the code examples provided, and stay tuned for more in-depth Google App Script tutorials in our upcoming blog posts.