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.

Features of Apps Script include the following:

  • Apps Script's built-in services let you read, update, and manipulate your G Suite 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 G Suite editors that let you activate scripts directly from those editors with menu items, dialogs, and sidebars.

...and much more.

This 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 in order to save you 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, formula, 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
  • ...and much more.

What you'll learn in this playlist

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

  1. Macros and Custom Functions
  2. Spreadsheets, Sheets, and Ranges
  3. Working with data
  4. Data formatting
  5. Chart and present data in Slides

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.

Welcome to the first codelab of this playlist! In this codelab, you will 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 Google 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

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

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, Google Sheets provides macros. Macros let you ‘record' a series of actions in a sheet. With a recorded macro, you can repeat that series of 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 the Make a copy button.

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

Creating 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:

  1. Click cell A1 to focus the cursor in that row. This is your header row.
  2. In the menu, select Tools > Macros > Record Macros.

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

  1. In the Macro box, select Relative reference.

  1. Select row 1.

  1. Recolor 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 Ctrl+B (or Cmd+B on Macs).
  2. Select View > Freeze > 1 row to freeze top row.

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

Using Sheets' User Interface, you've made a macro specialized for formatting headers!

You've made a macro for formatting headers. Move to the next section to learn how to apply macros in Sheets.

Activating your macro

You've created a macro in Sheets! You can try applying your macro in Sheets by following these instructions:

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

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

  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).

You've learned how to apply Macros in Sheets. Congrats! Your spreadsheet should look like this:

Macros allow you to create spreadsheets efficiently, and, in the next part of this codelab, you will learn how to make your macros even more powerful! Here's the 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. And, in the next section, you'll learn how to modify that macro code directly using Apps Script's in-browser 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 in order to take those same actions in the same order.

Looking at the script editor

Now that you've created a macro, you can look at it's code. You can look at your macro script by selecting Tools > Script editor to open a browser code editor for Apps Script.

Now you can see the script editor! The script editor enables you to both write code in Apps Script and run those scripts on Google servers.

The script editor shown above has several components:

  • A project title or name of the script project containing your scripts.
  • A menu or list of operations relating to your project.
  • A taskbar or list of operations relating to your script like 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 called Header. When you activate the macro, Sheets runs this function.

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

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. 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 a good idea to include this annotation when you're expecting to work only with a single file. The macro recorder adds this comment automatically 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);
};

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 do not need inputs.The brackets 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. But 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 on 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 getActiveRangeList() on spreadsheet more than once. As you code more and more using Apps Script, you can 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:

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 macros function, and what topics future codelabs delve into.

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

Customizing 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 how 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 re-record it. But, by using the Apps Script editor, you can make those changes directly. Here's one way of doing it:

  1. In the script editor, replace sheet.getMaxColumns() with 10 on line 6. 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);
}
  1. Select , File > Save, or Ctrl+S (Cmd+S on Macs) to save your script. Next you will test the edited macro.
  2. A prompt may appear to request a project name. If so, 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 in order to specify which cells to act on.

Now, it's time to learn about customizing your macro colors!

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.

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

  1. In Sheets, switch back to the original sheet (Sheet 1) 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. 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);
}
  1. Select , File > Save, or Ctrl+S (Cmd+S on Macs) 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 here:

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 have adjusted the background color set by your macro. Suppose now that you want to change the text color as well, to make the header labels more visible. You can do that by changing the second color code:

  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. 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);
}
  1. Select , File > Save, or Ctrl+S (Cmd+S on Macs) 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've seen how macros are actually Sheets actions recorded as Apps Script code. In the next section, you can see another way Apps Script can help you work with Google Sheets: custom functions.

Like most spreadsheet applications, Google Sheets has a number of built-in formula functions such as =SUM() that enable quick calculations on spreadsheet data. Custom functions are simply functions that you define yourself, 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.

Creating a new script file

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

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

A new tab named customFunctions.gs within the editor should appear:

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

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

Converting US dollars to Swiss francs

Suppose you wanted to adjust the data for ‘Top Ten Highest Grossing Films 2018' to show not only the 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, adjust your dataset to allow the function to demonstrate a proper output. To do this:

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

  1. 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!

  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;
}

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.

  1. Select , File > Save, or Ctrl+S (Cmd+S on Macs) to save your script.
  2. In Sheets, select the I2 cell.
  3. In the function bar, enter =USDTOCHF(H2).

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

  1. Move your cursor to the bottom right corner of the I2 cell and select the small blue box (your cursor should transform into when hovered over the blue box).
  2. Click and drag the blue box downwards to highlight the I3:I11.

Column I now lists the Swiss franc conversions of the column G's U.S. dollar values!

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

Analyzing the function USDTOCHF()

Starting from 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
 */

Comment blocks like this one 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 type a function name in a cell as seen below:

Notice that 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, 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 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 do that 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. Your code should now 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;
}

Review the steps below, so you can save and run your function to see the results!

  1. Select , File > Save, or Ctrl+S (Cmd+S on Macs) to save your script.

The Swiss franc now prefixes the values of column I:

You've updated your custom function that now not only converts U.S. dollars to Swiss francs but also outputs the currency with a string prefix.

Advanced: Fetching external data

This is a good start for a basic custom function, but this example assumes that the exchange rate for dollars to Swiss francs is constant. Suppose instead you wanted to have the current exchange rate always used, so that whenever the sheet is reloaded the values would be 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 that is 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 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;
}

This code fetches the current exchange rate from a financial information server using third-party exchange rate API. This is done using Apps Script services like UrlFetchApp and CacheService. Those advanced concepts are out of the scope of this specific codelab, but you can start to see the versatility of Apps Script automating 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 that 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. See Using Apps Script services.
  • Don't name a custom function the same as another built-in function, or end the name with an underscore. See the Naming guidelines.
  • 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. 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. See the Return values guidelines.

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

You've completed the first codelab of Fundamentals of Apps Script. 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 No

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.

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!