Google Sheets Macros

A macro in Google Sheets is a recording of a series of actions that can be repeated via the Macro menu or with a hotkey.

To start making a macro, click Tools > Macros > Record Macros.

A dialog box pops up showing the macro is currently recording, and lets you choose between absolute and relative references.

• Absolute references are fixed to the exact location as recorded (ie cell A5)
• Relative references are applied using the user’s current location as a starting point

Once recording, you can perform some actions (such as formatting a header row), and click the “Save” button in the dialog box, give it a name and an optional hotkey.

The first time you try to use a recorded macro, you will be prompted to authorize it. Authorization cancels the action, so you’ll have to re-run it after you’ve authorized it for it to actually run.

Macros as Code

The really cool thing – is that your recorded actions are automatically translated into statements in a JavaScript function using the Apps Script APIs. After saving a macro, you can go to Tools > Script Editor to view the recorded macro as a script.

Here you’ll see the script editor with a function of the same name as your macro, containing the code statements to reproduce the actions. This macro “Header” changes the colors of the first header row and freezes it.

Here’s that code, with comments for each statement:

/** @OnlyCurrentDoc */
// ^ limit script permissions to current doc

function Header() {
  
  // get the active spreadsheet (the entire document)
  var spreadsheet = SpreadsheetApp.getActive();

  // get the currently selected sheet
  var sheet = spreadsheet.getActiveSheet();

  // select the entire row, relative to cursor starting point (equivalent of clicking number to left of a row)
  sheet.getRange(spreadsheet.getCurrentCell().getRow(), 1, 1, 
  sheet.getMaxColumns()).activate();

  // set background color, set foreground color, and bold it
  spreadsheet.getActiveRangeList()
    .setBackground('#4c1130')
    .setFontColor('#ffffff')
    .setFontWeight('bold');

  // freeze the first row
  spreadsheet.getActiveSheet().setFrozenRows(1);
}

Comments are closed.