What is GAS (Google Apps Script)?
A beginner's guide to automating Google Sheets

GAS is Google's own way of running programs. It uses the same syntax as JavaScript and can work with Google Sheets as well as Gmail, Google Calendar, Google Drive and Google Forms.

All you need is a Google account. It is free and there is nothing to install: open a spreadsheet in your browser and start writing. Your program runs on Google's servers, not on your computer.

Google Sheets has "macros" that record what you do so you can replay it as often as you like. Start recording with "Extensions" → "Macros" → "Record macro", then select and type in cells, and your actions are saved as GAS code.

/** @OnlyCurrentDoc */

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('B2').activate();
  spreadsheet.getCurrentCell().setValue('test');
  spreadsheet.getActiveRangeList().setFontWeight('bold');
};

In other words, a macro is GAS inside. Even if you give the macro a name such as "Bold test", the function in the code is called myFunction. The name shown in the menu and the function name are stored separately.

While recording, you choose "Use absolute references" or "Use relative references" in the bar at the bottom of the screen. Absolute references are saved as cell addresses such as B2, so the macro writes to the same place whichever cell is selected. Relative references are saved as "one row below the current cell" and work from the selected cell.

Recording alone cannot handle tables whose length changes or steps that depend on a condition. From there on, you read the recorded code and add to it.

• Remove blank rows from pasted data and tidy the headers
• Look at amounts or dates and write labels such as "Large" or "Overdue" in a status column
• Add your own menu to the spreadsheet and run tasks from it like a button
• Create your own functions that can be used in cells (custom functions)
• Send emails with Gmail based on the contents of a table
• Run a task at a set time, such as 8 a.m. every day (triggers)
• Copy Google Forms responses to another sheet as they arrive

Each of these takes minutes or even tens of minutes by hand. Write it once, and from then on you just click a menu item, or it runs without you doing anything.

1. Open the spreadsheet and choose "Extensions" → "Apps Script"
2. The editor opens in a new tab, with "Code.gs" on the left and function myFunction() { } on the right
3. Write your code inside { } and save (Ctrl+S, or ⌘+S on a Mac)
4. Click "▷ Run" at the top. The result appears in the "Execution log" below

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const value = sheet.getRange('A1').getValue();
  Logger.log(value);
  sheet.getRange('B1').setValue('Checked');
}

Logger.log writes a value to the execution log. Numbers are shown with a decimal point, such as 120.0 (console.log shows 120). When something does not work as expected, check the values in the log first.

The first time you run code you wrote, a screen asks whether the script may read and edit your spreadsheet. Many people get stuck here, so let's go through it step by step.

1. "Authorization required" → Review permissions / OK
2. Choose an account
3. "Google hasn't verified this app" → "Advanced" at the bottom left
4. "Go to (project name) (unsafe)"
5. Check what you are allowing and click "Allow" (if there are items to select, select them and click "Continue")

"Google hasn't verified this app" is a warning shown for scripts that have not gone through Google's review. Scripts you wrote and macros you recorded have not been reviewed, so this screen always appears. If you made it yourself, it is fine to continue.

On the other hand, if this screen appears in a spreadsheet someone you don't know gave you, choose "Back to safety". Once allowed, the script can work with your spreadsheets and email using your account.

If you put /** @OnlyCurrentDoc */ at the top of your code, the script can only touch "this spreadsheet", and the permissions you are asked to grant are narrower. Recorded macros include it from the start.

You can go through the same steps in mission 1 of the practice.

Here is what you use most often when working with a spreadsheet. Row and column numbers start at 1, while the indexes of an array read with getValues start at 0.

function judgeTargets() {
  const sheet = SpreadsheetApp.getActiveSheet();
  for (let row = 2; row <= sheet.getLastRow(); row++) {
    const sales = sheet.getRange(row, 2).getValue();
    const target = sheet.getRange(row, 3).getValue();
    if (sales >= target) {
      sheet.getRange(row, 4).setValue('Met');
    } else {
      sheet.getRange(row, 4).setValue('Missed');
    }
  }
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Monthly tasks')
    .addItem('Mark large deals', 'markLarge')
    .addToUi();
}

A function named onOpen runs automatically when the spreadsheet opens. Running it with "▷ Run" in the editor gives the error Cannot call SpreadsheetApp.getUi() from this context., so save and then reopen the spreadsheet to check it.

The two things people look up most for GAS are sending email and triggers.

function sendReminders() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const rows = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues();
  rows.forEach(([name, email]) => {
    MailApp.sendEmail(email, 'Submission deadline reminder', name + ',\n\nThe deadline is this Friday.');
  });
}

There is a sending limit: a free Google account can send up to 100 emails a day (Google Workspace accounts can send more). Each run can last up to 6 minutes; after that it stops with "Exceeded maximum execution time".

To run on a schedule, click "Triggers" (the clock icon) on the left of the editor → "Add Trigger", then choose the function and "Time-driven". You can pick a time of day, every day, week or month.

Email really is sent even when you are only trying the code out. Test first with a table that contains only your own address.

The ideas are the same in both: read cells, write cells, repeat and branch on conditions. Learn one and you can read the other. Excel macros are covered in our Excel macro guide.

In this site's macro and GAS simulation, you practice by running real code in a virtual spreadsheet and Apps Script editor. You fill in blanks in the code, and on a phone you choose each blank from four options.

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.

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.

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.

Start the simulation (13 missions) →

A good way to start is to "Record macro" in one of your own spreadsheets and read the code it produces. It makes it easier to see what each command does.

Generative AI can write code for you too. But to judge whether the code is right, or where to fix it, you need to be able to read at least the "Common code" in this article.