Notes on the Google Sheets Apps Script API

sheets-apps-script-api.md

Pretty much all queries start off as: SpreadsheetApp.getActiveSheet()

Class: Sheet

getRange(row, column, numRows, numColumns)

Class: Range

Google Documentation

Event: onSelectionChange

/**
 * The event handler triggered when the selection changes in the spreadsheet.
 * @param {Event} e The onSelectionChange event.
 */
function onSelectionChange(e) {
  // Set background to red if a single empty cell is selected.
  var range = e.range;
  if(range.getNumRows() === 1 
      && range.getNumColumns() === 1 
      && range.getCell(1, 1).getValue() === "") {
    range.setBackground("red");
  }
}

Linking

const sheet = SpreadsheetApp.getActiveSheet();
const rangeToBeLinked = sheet.getRange('A2');
const rangeToAddLink = sheet.getRange('D32')
const richText = SpreadsheetApp.newRichTextValue()
    .setText('Click to go to ' + rangeToBeLinked.getA1Notation())
    .setLinkUrl('#gid=' + sheet.getSheetId() + '&range=' + 'A' + rangeToBeLinked.getRow())
    .build();
rangeTwo.setRichTextValue(richText );

Link hash URLs allow you to jump to other sheets (link to this):

#gid=824217278&range=A4:A6

And get sheet ID with SpreadsheetApp.getActiveSheet().getSheetID()

(Stack Overflow)

Alternatively can set formula for hyperlink (less desirable):

cell.setFormula('=HYPERLINK("http://www.google.com/","Google")');

---

• Sheets event objects
• Triggers for editor add-ons