Fundamentals of Apps Script with Google Sheets #1: Macros & Custom Functions

1. Welcome to Apps Script

What is Apps Script?

Apps Script is a rapid application development platform that gives you the ability to automate, customize, and extend your Google Workspace experience. With Apps Script, you can save time and effort by streamlining onerous or complex work within Google Workspace.

Apps Script features include the following:

Apps Script's built-in services let you read, update, and manipulate your Google Workspace application data with scripts.
You can create scripts using Apps Script's in-browser code editor—there's no need to install or run code development software.
You can design user interfaces for Google Workspace editors that let you activate scripts directly from those editors using menu items, dialogs, and sidebars.

This Fundamentals of Apps Script with Google Sheets codelab playlist teaches the basics of Apps Script and how to use it to improve your Google Sheets experience. This codelab focuses on teaching Apps Script basics.

The Spreadsheet service

You can use Apps Script to extend Google Sheets to save time and effort. Apps Script provides the Spreadsheet service which allows scripts to interact with your Google Sheet files and the data they contain. You can use this service to automate the following common spreadsheet tasks:

Create or modify a spreadsheet.
Read and update cell data, formulas, and formatting.
Create custom buttons and menus.
Import and export data from other Google applications or third-party sources.
Share and control access to spreadsheets.

What you'll learn

This playlist covers all the topics you'll need to get started using Apps Script with Google Sheets:

The codelabs in this playlist are meant to be read in order, so start with this one and complete them in sequence for the best learning experience.

Move to the next section to learn more about this codelab's contents.

2. Introduction

Welcome to the first codelab of this playlist. In this codelab, you'll learn the basics of using Apps Script with Google Sheets. Specifically, this codelab focuses on two key concepts: macros and custom functions.

A macro is a series of recorded actions within Google Sheets. Once recorded, you can activate a macro to repeat those actions later with a menu item or shortcut key. You can both create and update your own macros in both Google Sheets and the Apps Script code editor.

In the Apps Script code editor, you can also create custom functions. Similar to the built-in functions Sheets offers (such as SUM or AVERAGE), you can use Apps Script to write your own custom functions for simple and niche operations (such as conversions or string concatenation). Once created, you can call these functions in Sheets as you would a built-in function. Custom functions can also be used in cell formulas you write, combining them with other functions as needed.

Read below to learn what concepts and requirements this codelab involves.

What you'll learn

How to create a script for Google Sheets.
How to navigate the Apps Script editor.
How to create and update macros.
How to create your first Sheets custom function.

What you'll need

Basic familiarity with JavaScript
Basic familiarity with Google Sheets
Ability to read Sheets A1 Notation

You're done with the introductions. Go to the next section to start working with macros.

3. Make a macro in Sheets

Typically, when working in spreadsheets, you can enter into a loop of repetitive actions—copying cell values, formatting, creating formulas, and so forth—which can grow tedious and lead to mistakes. To automate repeated actions, Google Sheets provides macros. Macros let you ‘record' a series of actions in a sheet. With a recorded macro, you can repeat the same actions elsewhere in a spreadsheet with a simple hotkey press.

In this section, you'll learn how to build a macro in Sheets. In the next section, you'll see how macros are built using Apps Script.

Before you begin

Before you continue, you need a spreadsheet with some data. We've provided one for you: click this link to copy the data sheet and then click Make a copy.

A copy of the example spreadsheet for you to use is placed in your Google Drive folder and named "Copy of Top 10 Highest Grossing Films (2018)."

Create a macro

Now that you have a spreadsheet to work with, you can record a macro in Google Sheets. For this example, you'll create a macro that formats a header row for your data. Just follow these steps:

Click cell A1 to place the cursor in the row. This is your header row.
In the menu, select Extensions > Macros > Record Macros.

Once you start recording, Google Sheets remembers every action you take within the spreadsheet: highlighting cells, adding data, switching to different sheets, formatting, and so forth. These actions become the ‘script' that gets repeated once you save and activate the macro later.

In the Macro dialog, select Relative reference.

Select row 1.

Recolor the top row's Fill Color from white to dark magenta 3.

Change the top row's Text Color from black to white.

To bold the text, press Ctrl+B (or Cmd+B on macOS).
To freeze the top row, select View > Freeze > 1 row.

Click Save in the macro dialog. A new dialog asks you to name the macro. Enter the name "Header" and click Save.

Using Sheets' user interface, you've made a macro specialized for formatting headers.

Activate your macro

You can apply your new macro in Sheets by following these instructions:

To create a sheet, click Add Sheet .

In the new sheet, add some text to A1:C2. Feel free to follow the example inputs below:

Highlight the first row.

To apply the macro to the selected area, click Extensions> Macros> Header.
Authorize the macro by following the on-screen instructions.

Repeat Step 4 to run the macro again (authorizing it stops the first execution).

Congrats, you've learned how to apply macros in Sheets. Your spreadsheet should look like this:

Macros allow you to create spreadsheets efficiently, and, in the next part of this codelab, you'll learn how to make your macros even more powerful. Here's the secret: when you record a macro, what you're really doing is writing Apps Script code. Behind the scenes, Sheets constructs the code that matches the macro actions. In the next section, you'll learn how to modify the code directly using Apps Script's in-browser editor.

4. Macros in the script editor

As you create a macro, Google Sheets saves your actions as an Apps Script function. When you activate the macro, Google Sheets calls the Apps Script function to apply those actions in the same order.

The script editor

Now that you've created a macro, you can look at its code. To view the macro script, click Extensions > Apps Script to open the browser code editor for Apps Script.

The script editor allows you to write code in Apps Script and to run those scripts on Google servers.

Analysis of `macros.gs`

Review the current script. Sheets created the macros.gs script file when you recorded the Header macro, filling it with a corresponding Apps Script function called Header. When you activate the Header macro, Sheets runs this function.

Look at the image below to get familiar with the structure of your macro function in Apps Script. If you recorded the steps in a different order, or clicked around the spreadsheet while recording, your code might look a little different than this.

The first line is an annotation comment that affects authorization:

/** @OnlyCurrentDoc */

Most scripts ask the user for some permissions before they can run. These permissions control what the user is allowing the script to do. When the @OnlyCurrentDoc comment is present in a script project, Apps Script only asks for permission to access and update the current spreadsheet. Without this comment, Apps Script would ask permission to access and update all of the user's spreadsheets. It's always best practice to include this annotation when you're only working with a single file. The macro recorder adds this comment automatically for you.

To understand how Apps Script represents your macro's instructions, you can look at the function:

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, sheet.getMaxColumns()).activate();
  spreadsheet.getActiveRangeList().setBackground('#4c1130')
  .setFontColor('#ffffff')
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
};

This code runs when you activate the Header macro. Following function, the label Header() defines the function's name and its parameters. Recognize that Header() requires no parameters as macro functions in Apps Script don't need inputs. The braces always enclose the body of a function in Apps Script.

Later codelabs in this playlist explain the classes and concepts involved in creating the macro. For now, you can go through the following code descriptions to get a general idea of its components and their role in constructing your macro. Consider the first line:

var spreadsheet = SpreadsheetApp.getActive();

Here, getActive() returns an object representing the current active spreadsheet file in Sheets and sets it to the new variable spreadsheet.

var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, sheet.getMaxColumns()).activate();

These lines correspond to the action of clicking the first row to highlight it. This is called activation. The first line stores the current sheet in the variable sheet, while the second line gets the entire first row using the getRange() method and then calls activate() to activate it. The first row is specified using the specific row and column numbers. The spreadsheet.getCurrentCell().getRow() call returns the number of the current row, while sheet.getMaxColumns() returns the maximum number of columns in the sheet.

spreadsheet.getActiveRangeList().setBackground('#4c1130')
.setFontColor('#ffffff')
.setFontWeight('bold');

This bit of code gets more complex. To efficiently call methods with spreadsheet, the code stacks three methods onto getActiveRangeList() to prevent the code from redundantly calling on this spreadsheet method more than once. As you code more using Apps Script, you'll get more familiar with this convention of calling multiple methods on one class (also known as method chaining). For now, you can read the following for brief explanations on each method in the code block:

getActiveRangeList() returns the current active RangeList in spreadsheet. In this case, it's simply the first row the previous line activated.
Both the setBackground(color) and setFontColor(color) methods change the color attributes of the cells in the active range.
setFontWeight(fontWeight) adjusts the weight of the font for cells in the active range.

Lastly, the final line freezes the first row of the macro:

spreadsheet.getActiveSheet().setFrozenRows(1);

And that's the script you generated when you recorded your macro. Don't worry about any unfamiliar terms or methods mentioned above. The description is meant to get you thinking about some of the ideas Apps Script focuses on in a typical macro function, and the topics future codelabs delve into.

The next section focuses on manipulating the Header() function's code to show how you can use the script editor to further personalize macros.

Customize macros with Apps Script

The Apps Script editor shows the macro you previously created in Google Sheets. By adjusting the contents of the function body, you can further customize your macro's instructions to take different or additional actions. The following exercises demonstrate various ways to manipulate macros with the script editor.

Change the affected cells

Suppose you want to modify your macro so it only affects the first 10 columns of the first row instead of the whole row. You could delete the macro and rerecord it. But, by using the Apps Script editor, you can make those changes directly. Here's one way of doing it:

In the script editor, replace sheet.getMaxColumns() with 10. This edit changes the range of cells the macro affects in the spreadsheet.

/** @OnlyCurrentDoc */

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, 10).activate();
    /* sheet.getMaxColumns() replaced with 10.*/
  spreadsheet.getActiveRangeList().setBackground('#4c1130')
  .setFontColor('#ffffff')
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
}

To save your script, click Save .
To rename your project, enter "Macros and Custom Functions" as the new project name and click Rename.
To create a sheet, in Sheets, click Add Sheet .

In the script editor, from the functions list, select Header and click Run.

In your new sheet, you should see the following result:

By modifying the active or target range, your macro now affects only part of the first row. Many Apps Script methods take a range or A1 notation as a parameter to specify which cells to act on.

Next, let's learn about customizing your macro colors.

Change the colors of your macro

To help you design the color scheme of macros or other elements in Sheets, Apps Script can modify a range's fill or text color. Work through the following instructions to learn how you can customize the colors of your macro.

These instructions focus on changing the background color of your macro:

In Sheets, switch back to the sheet that contains the original data (Sheet 1).
Click the first row to highlight it.
In the script editor, replace background color #4c1130 with #afeeee. These values represent different colors using hex triplet notation.

/** @OnlyCurrentDoc */

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, 10).activate();
  spreadsheet.getActiveRangeList().setBackground('#afeeee')
    /* #4c1130 replaced with #afeeee.*/
  .setFontColor('#ffffff')
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
}

To save your script, click Save .
From the functions list, select Header and click Run.

In Sheets, the background fill of the first 10 columns in the first row recolors to a custom turquoise color:

By switching the hex color notation in the parameters of setBackground(color) from #4c1130 (dark magenta 3) to #afeeee (pale turquoise, an option not accessible in Sheets' default color menu), you change the color attribute of your macro's background color.

You've now modified the background color set by your macro. If you want to change the text color as well, change the second color code.

In Sheets, click the first row to make sure it's still highlighted.
In the script editor, replace font color #ffffff with #191970. This causes the macro to set a font color of navy blue.

/** @OnlyCurrentDoc */

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, 10).activate();
  spreadsheet.getActiveRangeList().setBackground('#afeeee')
  .setFontColor('#191970')/* #ffffff replaced with #191970.*/
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
}

To save your script, click Save .
From the functions list, select Header and click Run.

Return to Sheets to see the text color of the header row is now navy blue.

Now you've seen how macros are actually Sheets actions recorded as Apps Script code. In the next section, you can view another way Apps Script can help you work with Google Sheets: custom functions.

5. Code your first script: Custom functions

Like most spreadsheet applications, Google Sheets has several built-in formula functions such as =SUM() that enable quick calculations on spreadsheet data. Custom functions are simply functions you specify using Apps Script. Once you've defined a custom function, you can use it anywhere in your spreadsheet, just like a built-in function.

This section shows you how to create a custom function in Apps Script that does a monetary conversion.

Create a script file

Using the same spreadsheet and script project from the Macros section, follow these instructions to learn how to make a new script (which you can eventually use to make your first custom function):

To create an Apps Script file, return to the script editor.
Beside Files, click Add a file > Script.
Name the new script file customFunctions and press Enter. (Apps Script automatically appends a .gs extension to the script file name.)

A new tab named customFunctions.gs appears within the editor.

Now that you've created a script specifically for custom functions, you can fill it with code.

Convert US dollars to Swiss francs

Suppose you wanted to modify the data for ‘Top 10 Highest Grossing Films 2018' to show not only the worldwide gross values in U.S. dollars but also in Swiss francs. With custom functions, you can do that easily. The following exercise demonstrates how to create a custom function to mathematically convert your dollar values to franc values.

Before you can write your first custom function, modify your dataset to allow the function to demonstrate a proper output. To do this:

In Sheets, right-click column H.
In the resulting menu, click Insert 1 right.

menu showing Insert 1 right

Label the column "Worldwide_Gross (Swiss francs)" in cell I1.

Now you have a column that can store the results of your conversion custom function. Next, you can use the script editor to create your first custom function.

In customFunctions.gs, replace the code for myFunction() with the following code:

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {number} swissFrancs The converted total of Swiss francs.
 * @customfunction
 */
function USDTOCHF(dollars){
  var swissFrancs = dollars * .99; 
  return swissFrancs;
}

This is the code that will convert U.S. dollars to Swiss francs. Try the instructions below, and see how you can run a custom function in sheets.

To save your script, click Save .
In Sheets, select the I2 cell.
In the function bar, enter =USDTOCHF(H2).

To apply the formula to the rest of the cells in the column:

Move your cursor to the bottom-right corner of the I2 cell and select the small blue box (your cursor should transform into when pointing to the blue box).
Drag the blue box downwards to highlight the range I3:I11.

Column I now lists the Swiss franc conversions of the US dollar values in Column H.

Congrats, you've created your first custom function. The next section explains the code that comprises USDTOCHF().

Analysis of `USDTOCHF()`

The initial comments detail the code's purpose:

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {number} swissFrancs The provided value in Swiss francs.
 * @customfunction
 */

Comment blocks like this are used frequently in programming to explain what functions do.

In this comment, you can identify two parts: the function description (to convert dollars to francs) and annotations that describe the function's parameters and return type.

With the annotations, Apps Script utilizes JSDoc to help you document and create autocomplete hints for your code. You can read below how each annotation used in USDTOCHF() helps you with your Apps Script development:

@param: You can use the @param annotation to describe each parameter passed into the function.
@return: You can utilize the @return annotation to describe what the function returns.
@customfunction: You should always add @customfunction in any custom function's doc comment. This annotation notifies Sheets to autocomplete your custom function just as Sheets autocompletes built-in functions when you enter a function name in a cell as seen below:

Notice the text that appears in the autocomplete pop-up exactly matches the description text you placed in the comment block. You can make your custom functions easier to use by making sure the descriptions you create are well-written and complete.

Next, focus on the code in the function USDTOCHF():

function USDTOCHF(dollars){
  var swissFrancs = dollars * .99; 
  return swissFrancs;
}

As mentioned previously, USDTOCHF() takes the numeric variable dollars, multiplies it by a fixed exchange rate, and returns a value converted to Swiss francs in the numeric variable swissFrancs. The input parameter is the value contained in the cell specified when adding the custom function to a cell. In this example, the input dollar amounts are coming from column H. The output value swissFrancs is placed in the function's cell (column I in this example).

Custom functions can work with numeric or string values, as you'll see in the next section.

Concatenate a string prefix

Suppose you wanted the numeric output of the function USDTOCHF() to include the Swiss franc prefix CHF. You can do that with Apps Script by using the concatenation operator (+), as shown in the following instructions:

In the script editor, update the @return annotation to return string instead of number.
Change return swissFrancs to return 'CHF' + swissFrancs.

The + operator appends the string CHF to the front of the value contained in swissFrancs. Your code should now look like this:

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {string} swissFrancs The provided value in Swiss francs.
 * @customfunction
 */
function USDTOCHF(dollars){
  var swissFrancs = dollars * .99;
  return 'CHF' + swissFrancs;
}

To save your script, click Save .

The Swiss franc string now prefixes the values of column I:

Your custom function now not only converts U.S. dollars to Swiss francs, but also outputs the currency with a string prefix.

Advanced: Fetch external data

This is a good start for a basic custom function, but this example assumes the exchange rate for dollars to Swiss francs is constant. Suppose instead you wanted to use the current exchange rate, so whenever the sheet is reloaded the values are recalculated to represent the current conversion? To do that, you'd need a means of discovering what the current exchange rate is. That's not information readily available in Google Sheets, but fortunately you can use Apps Script to get it.

You can use code like below to get the current conversion rate of Swiss francs to U.S. dollars:

function USDTOCHF(dollars){
  // Gets a cache that is common to all users of the script.
  var cache = CacheService.getScriptCache();

  // Accesses the memory location (rates.CHF) of the script cache.
  var rate = cache.get('rates.CHF');

  // If a cache miss occurs, the program fetches the current
  // CHF rate from an API and stores the rate in the cache
  // for later convenience.
  if (!rate) {
    var response =
UrlFetchApp.fetch('https://api.exchangeratesapi.io/latest?base=USD');
    var result = JSON.parse(response.getContentText());
    rate = result.rates.CHF;
    cache.put('rates.CHF', rate);
  }
  // Converts dollars to CHF according to the latest rate.
  var swissFrancs = dollars * rate;
  // Returns the CHF value.
  return 'CHF' + swissFrancs;
}

This code fetches the current exchange rate from a financial information server using a third-party exchange rate API. This is done using Apps Script services like UrlFetchApp and CacheService. Those advanced concepts are out of scope for this specific codelab, but you can start to see the versatility of Apps Script to automate complex tasks in Google Sheets.

Custom function guidelines

Congratulations on completing the exercises for custom functions. As you use custom functions in your projects, it's important to understand they have certain restrictions. The following list summarizes the limitations detailed in the Custom Functions in Google Sheets guide:

Don't create custom functions that require user authorization. Rather, build your custom functions to fulfill simpler tasks such as sample data calculations, text editing, etc. Go to Using Apps Script services.
Don't name a custom function the same as another built-in function, or end the name with an underscore. Review the Naming guidelines.
Don't pass variable arguments to custom functions. You can only pass deterministic (fixed) values to custom functions as arguments. Passing variable arguments, such as the result of =RAND(), will break the custom function. See the Arguments guidelines.
Don't create functions that take more than 30 seconds to complete. If it takes longer an error will occur, so keep the function code simple and limited in scope. It's best to keep the calculations conducted in custom functions as simple as possible. View the Return values guidelines.

Now you can improve your spreadsheets by using the script editor to work with macros and create custom functions. In the next section, you can review what you've learned, and what you can do next to improve your scripting skills.

6. Conclusion

You've completed the first codelab of Fundamentals of Apps Script with Google Sheets. By creating and editing Sheets macros and custom functions, you've learned the basic Apps Script concepts. You can expand your Apps Script knowledge further in the next codelab.

Did you find this codelab helpful?

Yes

What we've covered

Basic Apps Script concepts.
How to navigate the script editor.
How to create and update Sheets macros.
How to create custom functions for Google Sheets.

Key Terms

Absolute reference: A macro recording setting specifying recorded macro actions are applied to the exact cells used in the recording.
Active (status): Indicates the specified spreadsheet, sheet, range, or cell is the one the user of the spreadsheet is currently viewing or highlighting.
Apps Script: A rapid application development platform, based on JavaScript, which makes it fast and easy to extend Google Workspace applications and automate work.
Authorization: The process of the user granting permissions to allow Apps Script to access user data in the relevant Google services or otherwise take actions on behalf of the user.
Container-bound script: Any script that is bound to and created from a Google Workspace document, such as a Google Sheet or Google Doc.
Custom functions: User-defined formulas in Apps Script used in spreadsheets for simple operations.
JSDoc: A markup language used to write explanatory notes on JavaScript-based source files.
Macro: Short for "macroinstruction". A recorded series of Google Sheets actions you can repeat using a menu item or shortcut key.
Method chaining: A programming idiom for invoking multiple method calls on an object, relying on the fact that each method call returns the object.
Range: A group of one or more adjacent cells within a sheet.
Relative reference: A macro recording setting specifying recorded macro actions are applied to the cells relative to the current cell selection.
Script editor: The in-browser code editor for Apps Script.
Sheet: A single page of a spreadsheet (similar to Microsoft Excel's workbooks). Sometimes used to refer to a Google Sheets file within Google Drive.
SpreadsheetApp: This class serves as the parent class for the Spreadsheet service and provides a starting point for code that reads or manipulates Google Sheets data.
Spreadsheet: A Google Sheets file that resides within Google Drive.
Spreadsheet service: An Apps Script service that allows scripts to create, access, and modify Google Sheets files.

Learn more about Apps Script in the Apps Script documentation.

What's next

The next codelab in this playlist presents the core classes and terminology of the Apps Script Spreadsheet service. This service enables you to finely control the values and presentation of data in Google Sheets using Apps Script.

Find the next codelab at Spreadsheets, Sheets, and Ranges.

Fundamentals of Apps Script with Google Sheets #1: Macros & Custom Functions

1. Welcome to Apps Script

What is Apps Script?

The Spreadsheet service

What you'll learn

2. Introduction

What you'll learn

What you'll need

3. Make a macro in Sheets

Before you begin

Create a macro

Activate your macro

4. Macros in the script editor

The script editor

Analysis of macros.gs

Customize macros with Apps Script

Change the affected cells

Change the colors of your macro

5. Code your first script: Custom functions

Create a script file

Convert US dollars to Swiss francs

Analysis of USDTOCHF()

Concatenate a string prefix

Advanced: Fetch external data

Custom function guidelines

6. Conclusion

Did you find this codelab helpful?

What we've covered

What's next

Analysis of `macros.gs`

Analysis of `USDTOCHF()`