Apps Script editor, SpreadsheetApp basics

Concept

1. What is Apps Script?

JavaScript that runs on Google's servers and can control Sheets, Gmail, Drive, Calendar and Docs. It's free with any Google account, needs no installation, and keeps running even when your computer is off.

2. Open the editor

In your Google Sheet: Extensions → Apps Script. A new tab opens with a file Code.gs containing an empty myFunction.

A script opened this way is bound to the sheet — it lives inside the file and can use getActiveSpreadsheet(). (A standalone script from script.google.com must open sheets by ID.)

Rename the project at the top (e.g. "Stock Tools").

3. Your first function

function hello() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Data');
  Logger.log('File: ' + ss.getName());
  Logger.log('Sheet: ' + sheet.getName() + ', last row: ' + sheet.getLastRow());
}

Save (Ctrl + S).
Choose hello in the function dropdown → Run.
First run asks for authorisation: Review permissions → choose your account. For your own scripts you may see "Google hasn't verified this app" → Advanced → Go to (project name). You're giving your own script access to your own sheets.
The Execution log at the bottom shows the output.

4. The object chain

SpreadsheetApp  →  Spreadsheet (file)  →  Sheet (tab)  →  Range (cells)

Code	Gets
`SpreadsheetApp.getActiveSpreadsheet()`	the file this script is bound to
`SpreadsheetApp.openById('ID')`	another file (ID is in its URL)
`ss.getSheetByName('Data')`	a tab by name (returns `null` if missing)
`sheet.getRange('A1')`	a cell
`sheet.getRange('A2:E10')`	a block
`sheet.getRange(2, 1)`	row 2, column 1 = A2

5. Reading and writing a cell

function readWrite() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  const price = sheet.getRange('B2').getValue();
  sheet.getRange('C2').setValue(price * 1.18);
  sheet.getRange('D2').setFormula('=B2*1.18');   // or write a formula
  sheet.getRange('A1:E1').setFontWeight('bold').setBackground('#1F4E78').setFontColor('#FFFFFF');
}

Setter methods can be chained, as in the last line.

6. Last row and last column

const lastRow = sheet.getLastRow();      // last row with any content
const lastCol = sheet.getLastColumn();

Much simpler than VBA's .End(xlUp). Watch out: a stray value far below your data counts too.

7. Quick pop-ups

SpreadsheetApp.getUi().alert('Done!');
SpreadsheetApp.getActiveSpreadsheet().toast('Report ready', 'ExcelWalaa', 5);

getUi() works only when someone runs the script from the sheet (not from a timed trigger). toast shows a small message in the corner.

Common mistakes

Sheet name typo (getSheetByName returns null → "Cannot read properties of null"). Running from the editor and expecting getUi() dialogs to appear in the editor (they appear in the sheet tab). Forgetting to save before Run.

What you'll learn