Spotted a bug? Have a great idea? Help us make google.dev great!

What is Apps Script?

Apps Script is a rapid application development platform that gives you the ability to automate, customize, and extend your G Suite experience. With Apps Script, you can automate and streamline onerous or complex work in G Suite, saving you and your team time and effort. Some of its notable features include the following:

  • Built-in services that let you read, update, and manipulate your G Suite application data with scripts.
  • An in-browser code editor for creating scripts, which eliminates the need to install or run code development software.
  • The ability to design user interfaces for G Suite editors that let you directly activate scripts from those editors with menu items, dialogs, and sidebars.

For more information, see the overview of Apps Script.

The Spreadsheet Service

You can use Apps Script to extend Sheets to save time and effort. Apps Script provides the Spreadsheet Service, which allows scripts to interact with your Sheets and their data. You can use the Spreadsheet Service to automate the following common spreadsheet tasks:

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

In this codelab, you will learn the basics of using Apps Script with Sheets, specifically for creating macros and custom functions.

A macro is a series of recorded actions in Sheets. Once recorded, you can activate a macro to repeat those actions later with a menu item or shortcut key. You can create and update macros in either Apps Script or Sheets.

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

What you'll learn

  • How to create a script in Sheets
  • How to navigate Apps Script
  • How to create and update macros
  • How to create a custom function in Sheets

What you'll need

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 give you a way to automate repetitive actions, Sheets provides macros, which allow you to record a series of actions that you can repeat 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. Click this link to copy the data sheet, then click Make a copy.

Check your Drive for a file named, "Copy of Top 10 Highest Grossing Films (2018).''

Creating a macro

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

  1. Click cell A1 to focus the cursor in that row. It's your header row.
  2. In the menu, select Tools > Macros > Record macros.

After you start recording, Sheets remembers every action that you take in the spreadsheet, such as highlighting cells, adding data, switching to different sheets, and formatting. Those actions become the script that repeats after you save and activate the macro.

  1. In the macro box, select Use relative references.

  1. Select the first row.

  1. Change the top row's fill color from white to dark magenta 3.

  1. Change the top row's text color from black to white.

  1. Bold the text by clicking Control+B (or Command+B on Macintosh).
  1. Select View > Freeze > 1 row to freeze the top row.

  1. Click the Save button in the macro dialog box at the bottom of the screen. A new dialog box then asks you to name the macro. Give it the name "Header" and then click Save.

You made a macro for formatting headers!

Activating your macro

Apply your macro in Sheets by following these instructions:

  1. Click or Add Sheet to create a new sheet.

  1. In the new sheet, add the following text to cells A1 through C1 and A2 through C2.

  1. Highlight the first row.

  1. Select Tools > Macros > Header to apply the macro to the selected area.

  1. Authorize the macro by following the on-screen instructions.
  1. Repeat Step 4 to run the macro again (authorizing it stops the first execution).

Your spreadsheet should look like this:

Macros allow you to efficiently create spreadsheets. Here's a secret—when you record a macro, what you are really doing is writing Apps Script code. Behind the scenes, Sheets constructs the code that matches the macro actions for you. In the next section, you'll learn how to directly modify that macro code with Apps Script.

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

Looking at the script editor

Now that you created a macro, you can look at its code. Select Tools > Script editor to open a browser code editor for Apps Script.

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

The script editor shown above includes the following components:

  • A project title or the name of the script project containing your scripts
  • A menu or list of operations related to your project
  • A taskbar or list of operations related to your script, such as Save
  • A script sidebar depicting the project's scripts
  • The current script, which shows the selected script from the script sidebar

Understanding macro.gs

Next, look at the current script. Sheets created the macros.gs script file when you recorded the Header macro, filling it with a corresponding Apps Script function named Header. When you activate the macro, Sheets runs that function.

Look at the image below to get familiar with the structure of your macro function in Apps Script.

The first line is an annotation comment that affects authorization:

/** @OnlyCurrentDoc */

Most scripts ask the user for some set of permissions before they can run. 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 that comment, Apps Script would ask permission to access and update all your spreadsheets. It's always a good idea to include that annotation when you're expecting to work only with a single file. The macro recorder automatically adds that comment for you.

To start understanding 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);
};

That 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 do not need inputs.The brackets always enclose the body of a function in Apps Script.

You can go through the following code descriptions to get a general idea of its components and their roles 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();

Those lines correspond to the action of clicking on the first row to highlight it. That's 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');

That bit of code is more complex. To efficiently call methods with spreadsheet, the code stacks three methods onto getActiveRangeList() to prevent the code from redundantly calling getActiveRangeList() on spreadsheet more than once. As you code more and more using Apps Script, you can get more familiar with the 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:

spreadsheet.getActiveSheet().setFrozenRows(1);

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

Customizing macros with Apps Script

The Apps Script editor shows the macro that you previously created in 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 that you can manipulate macros with the script editor.

Changing the affected cells

Suppose you want to adjust your macro so that it only affects the first 10 columns of the first row instead of the whole row. You could delete the macro and rerecord it or use the Apps Script editor to directly make those changes. Here's one way of doing with Apps Script:

  1. In the script editor, replace sheet.getMaxColumns() with 10 on line 6. That edit changes the range of cells that 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);
}
  1. Select , File > Save, or Control+S (or Command+S on Macintosh) to save your script. Next, you will test the edited macro.
  2. A prompt may appear to request a project name. If so, then enter "Macros and Custom Functions" as the new project name, and click OK.

  1. In Sheets, click or Add Sheet to create a new sheet.

  1. In the script editor, select or Run > Run function > Header to execute the macro function.

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

By adjusting 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.

Changing the colors of your macro

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

The first set of instructions focuses on changing the background color of your macro:

  1. In Sheets, navigate to the original sheet (Sheet1) that contains the data.
  2. Click on the first row to highlight it.
  3. In the script editor, replace #4c1130 with #afeeee on line 6. Those 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);
}
  1. Select , File > Save, or Control+S (or Command+S on Macintosh) to save your script.
  2. Select or Run > Run function > Header to compile and execute the script.

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

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 the default color menu), you changed the color attribute of your macro's background color.

Now, suppose that you also want to change the text color to make the header labels more visible. You can do that by changing the second color code with the following instructions:

  1. In Sheets, click the first row to make sure it is still highlighted.
  2. In the script editor, replace #afffff with #191970 on line 7. That 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);
}
  1. Select , File > Save, or Control+S (or Command+S on Macintosh) to save your script.
  2. Select or Run > Run function > Header to compile and execute the script.

Return to Sheets. See that the text color of the header row is now navy blue!

Now you see how macros are actually Sheets actions recorded as Apps Script code. In the next section, you can see another way that Apps Script can help you work with Sheets—custom functions.

Like most spreadsheet applications, Sheets offers numerous built-in formula functions, such as =SUM(), that enable quick calculations. Custom functions are simply functions that you define yourself with Apps Script. After you define a custom function, you can use it anywhere in your spreadsheet, similar to a built-in function.

The following section shows you how to create a custom function in Apps Script that performs a monetary conversion.

Creating a new script file

With the same spreadsheet and script project that you used in the section about macros, you can learn to make a new script (which you can eventually use to make your first custom function) with the following instructions:

  1. In the script editor, select File > New > Script file.
  2. Name the new script file customFunctions (Apps Script automatically appends a .gs extension to the script file's name).

A new tab named customFunctions.gs should appear in the editor.

Whenever you create a new script, the editor automatically generates a default myFunction() for your convenience.

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

Converting US dollars to Swiss francs

Suppose you want to adjust the data for "Top Ten Highest Grossing Films 2018" to show the gross values in US dollars, as well as that in Swiss francs. The following exercise demonstrates how you can easily do that by creating a custom function that mathematically converts the dollar values to franc values.

Before you can write your first custom function, you need to adjust your dataset to allow the function to demonstrate a proper output by doing the following:

  1. In Sheets, right-click column H.
  2. In the pop-up menu, click Insert 1 right.

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

That creates a column that can store the results of your conversion custom function. Now, you can proceed with the instructions and use the script editor to create your first custom function:

  1. In customFunction.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;
}

That code will convert US dollars to Swiss francs. Now you can proceed with the instructions to run your custom function in Sheets:

  1. Select , File > Save, or Control+S (or Command+S on Macintosh) to save your script.
  2. Select the I2 cell.
  3. Enter =USDTOCHF(H2)in the function bar.

To apply the formula to the rest of the cells in the column, you need to do the following:

  1. Move your cursor to the bottom right corner of the I2 cell and select the small blue box (your cursor should display as when hovering over the blue box).
  2. Click and drag the blue box downward to highlight I3:I11.

Now, column I lists the Swiss franc conversions of column G's US dollar values!

Congrats! You created your first custom function. The next section explains the code that comprises USDTOCHF().

Analyzing the function USDTOCHF()

At the beginning of the code, you can recognize the comments detailing 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
 */

Such comment blocks are frequently used in programming to explain what functions do.

In the comment block for this example, you notice two parts—the function description (to convert US dollars to Swiss francs), and annotations that describe the function's parameters and return type.

With the annotations, Apps Script uses JSDoc to help you document and create autocomplete hints for your code. Here's how each annotation used in USDTOCHF() helps you with your Apps Script development:

  • @param describes each parameter passed into the function.
  • @return describes what the function returns.
  • @customfunction (you should always add @customfunction in any custom function's doc comment) notifies Sheets to autocomplete your custom function, similar to how Sheets autocompletes built-in functions when you type a function name in a cell as seen in the following animation:

Notice the text that appears in the autocomplete pop-up window matches the description text that you placed in the comment block. You can make your custom functions easier to use by writing clear and complete descriptions.

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 and returns that value converted to Swiss francs in the numeric variable swissFrancs by multiplying it by a fixed exchange rate. The input parameter is the value contained in the cell that you 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 (that is, column I in this example).

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

Concatenating a string prefix

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

  1. In the script editor, change return var on line 10 to return 'CHF' + swissFrancs.

The + operator appends the string CHF to the front of the value contained in swissFrancs, so your code should look like this:

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

Now you can proceed with the instructions, and save and run your function to see the results:

  1. Select , File > Save, or Control+S (or Command+S on Macintosh) to save your script.

The Swiss franc now prefixes the values of column I as seen in the following screenshot:

Now, your custom function not only converts US dollars to Swiss francs, but it also outputs the currency with a string prefix.

Advanced: Fetching external data

That's a good start for a basic custom function, but the example assumes a constant exchange rate for dollars to Swiss francs. Suppose that you want to always use the current exchange rate, so that whenever you reload the spreadsheet, it recalculates the values according to the latest rate. To do that, you need a means of discovering the current exchange rate, but that information isn't readily available in Sheets. Fortunately, you can use Apps Script to get it!

You can use the following code to get the current conversion rate of Swiss francs to US 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 that 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;
}

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

Custom function guidelines

Congrats! You completed the exercises for custom functions. As you use custom functions in your projects, you need to understand their restrictions. The following list summarizes the limitations detailed in the Guidelines for custom functions:

  • 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. For more information, see Using Apps Script services.
  • Don't name a custom function with the same name as another built-in function or end the name with an underscore. For more information, see Naming.
  • Don't pass variable arguments to custom functions. You can only pass deterministic (fixed value) values to custom functions as arguments. Passing variable arguments, such as the result of =RAND(), will break the custom function. For more information, see Arguments.
  • Don't create functions that take more than 30 seconds to complete. If it takes longer than that, then 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. For more information, see Return values.

Now, you know how to improve your spreadsheets by using the script editor to work with macros and create custom functions!

Did you find this codelab helpful?

Yes No

What you covered

  • Basic Apps Script concepts
  • How to navigate the script editor
  • How to create and update macros in Sheets
  • How to create custom functions in Sheets