Macros & Custom Functions
Custom Functions in Google Sheets
Last updated
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 Tools > 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.
Key Terms: Macros can use two types of references in Sheets to refer to specific cells. Absolute references are fixed to the exact locations recorded (for example, cell A5), while relative references are applied using the user's current selection as a starting point (for example, the fourth cell down from the current selection).
This codelab asks you to use relative references for your macros.
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:
In the new sheet, add some text to A1:C2. Feel free to follow the example inputs below:
Highlight the first row.
Select Tools > Macros > Header to apply the Macro to the selected area.
Authorize the macro by following the on-screen instructions.
Note: If you're using a gmail.com account, you might also get an unverified app dialog when you first use your script. Google uses this to warn users who may be using code from unknown or untrusted authors. If you see this dialog, it's OK to proceed since you're the script author. Follow the on-screen prompts to continue authorizing the script. The process of getting this permission is called authorization.
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.
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. View the macro script by selecting Tools > Script editor 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.
Note: The macros and scripts you're creating in this codelab are attached to the Google Sheet file they operate on, and can be accessed at any time from the Sheets Tools > Script editor menu item. Scripts attached to a Google Sheet like this are called container bound.
Analysis of macro.gs
macro.gsReview 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:
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:
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:
Here, getActive() returns an object representing the current active spreadsheet file in Sheets and sets it to the new variable spreadsheet.
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.
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 activeRangeListinspreadsheet. In this case, it's simply the first row the previous line activated.Both the
setBackground(color)andsetFontColor(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:
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()with10. This edit changes the range of cells the macro affects in the spreadsheet.
To save your script, click Save
.To rename your project, enter "Macros and Custom Functions" as the new project name and click Rename.
In the script editor, from the functions list, select
Headerand 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
#4c1130with#afeeee. These values represent different colors using hex triplet notation.
To save your script, click Save
.From the functions list, select
Headerand 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
#ffffffwith#191970. This causes the macro to set a font color of navy blue.
To save your script, click Save
.From the functions list, select
Headerand 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.
Google Sheets offers hundreds of built-in functions like AVERAGE, SUM, and VLOOKUP. When these aren’t enough for your needs, you can use Google Apps Script to write custom functions — say, to convert meters to miles or fetch live content from the Internet — then use them in Google Sheets just like a built-in function.
Getting started
Custom functions are created using standard JavaScript. If you're new to JavaScript, Codecademy offers a great course for beginners. (Note: this course wasn't developed by and isn't associated with Google.)
Here's a simple custom function, named DOUBLE, which multiplies an input value by 2:
Autocomplete
Google Sheets supports autocomplete for custom functions much like for built-in functions. As you type a function name in a cell, you will see a list of built-in and custom functions that matches what you enter.
Custom functions will appear in this list if their script includes a JsDoc @customfunction tag, as in the DOUBLE() example below.
Optimization
Each time a custom function is used in a spreadsheet, Google Sheets makes a separate call to the Apps Script server. If your spreadsheet contains dozens (or hundreds, or thousands!) of custom function calls, this process can be quite slow.
Consequently, if you plan to use a custom function multiple times on a large range of data, consider modifying the function so that it accepts a range as input in the form of a two-dimensional array, then returns a two-dimensional array that can overflow into the appropriate cells.
For example, the DOUBLE() function shown above can be rewritten to accept a single cell or range of cells as follows:
The above approach uses the map method of JavaScript's Array object to recursively call DOUBLE on every value in the two-dimensional array of cells. It returns a two-dimensional array that contains the results. This way, you can call DOUBLE just once but have it calculate for a large number of cells at once, as shown in the screenshot below. (You could accomplish the same thing with nested if statements instead of the map call.)
Similarly, the custom function below efficiently fetches live content from the Internet and uses a two-dimensional array to display two columns of results with just a single function call. If each cell required its own function call, the operation would take considerably more time, since the Apps Script server would have to download and parse the XML feed each time.
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.
Name the new script file
customFunctionsand press Enter. (Apps Script automatically appends a.gsextension 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.
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 formyFunction()with the following code:
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:
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()
USDTOCHF()The initial comments detail the code's purpose:
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@paramannotation to describe each parameter passed into the function.@return: You can utilize the@returnannotation to describe what the function returns.@customfunction: You should always add@customfunctionin 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():
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
@returnannotation to return string instead of number.Change
return swissFrancstoreturn 'CHF' + swissFrancs.
The + operator appends the string CHF to the front of the value contained in swissFrancs. Your code should now look like this:
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:
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.
