APPS SCRIPT
Macro and GAS (Apps Script) simulation
Practice everything from recording macros to GAS code by running it in a virtual spreadsheet and Apps Script editor. You fill in blanks in the code, so you can follow along even as a beginner. On a phone, you choose each blank from four options.
CHAPTER 1
Chapter 1 Recording and running macros
Run a recorded macro and record your own. Absolute and relative references, and how to read and edit recorded code.
- 1Run a recorded macroThis spreadsheet contains a macro called "Format headers" that makes the headers bold and colors them. Run it from "Extensions" → "Macros".Loading
- 2Record a macro (absolute references)Start recording with "Extensions" → "Macros" → "Record macro", type "Total" in A6 and make it bold. After saving, run the recorded macro from "Extensions" → "Macros" to check that it repeats the same actions.Loading
- 3Record with relative referencesRecord, with relative references, a macro that types "Completed" in the "Status" column (two to the right) of the selected item's row. Start recording with A2 selected.Loading
- 4Edit recorded codeYou opened the recorded macro in the Apps Script editor. Fill in the blanks so that the header A1:C1 gets a pale yellow (#fff2cc) background, then click ▷ Run.Loading
CHAPTER 2
Chapter 2 Read, repeat, branch
Read cells with getValue, write with setValue and check values in the execution log. Loop through every row with for and branch with if.
- 5Read a cell and write it to the execution logRead the unit price of the ballpoint pen in B2 and write it to the execution log.Loading
- 6Write to a cell (setValue)Write the amount for the ballpoint pen (unit price × quantity) in D2.Loading
- 7Read at once (getValues and two-dimensional arrays)Read A2:C5 at once and write the name of the second item (Notebook) to the execution log.Loading
- 8Loop through every row with for (getLastRow)Write the amount (unit price × quantity) for every item in column D. Find the last row with getLastRow so the code still works when the number of items changes.Loading
- 9Branch with ifWrite "Met" in the result column if sales are at or above the target, or "Missed" if they fall short. Exactly on target counts as "Met".Loading
CHAPTER 3
Chapter 3 Case study: tidying the monthly sales sheet
Remove blank rows, mark large deals in one go and run it from your own menu. Finally, write your own function for cells.
- 10Delete blank rows from the bottomThe October details you pasted contain blank rows. Delete all of them. When a row is deleted, the rows below move up, so check the rows from the bottom.Loading
- 11Write the result column at once (setValues)Write "Large" in the category of each entry whose amount is 100,000 or more. Instead of writing row by row, read the column at once and write it at once.Loading
- 12Run it from your own menu (onOpen)Make a "Monthly tasks" menu appear when the spreadsheet opens, with "Mark large deals" running markLarge. After saving, reopen the spreadsheet by reloading the browser (F5) and run it from the menu.Loading
- 13Create a function you can use in cells (custom function)Column C contains =WITH_TAX(B2), but the function doesn't exist yet, so it shows #NAME?. Write and save a function that adds 10% tax to the pre-tax amount, rounds down to a whole number and returns it.Loading
* This is a simulation of how Google Sheets and Apps Script behave and may differ in some ways from the real thing.
* Google Sheets is a trademark of Google LLC. This site is not affiliated with Google.