# Apps Script Automation Lab

30 guided workshops · DiscoveryVIP

Browser practice uses simulated services. Run exported code in a disposable Google practice project, after replacing fictional IDs and reviewing the methods. No Google account is connected to the browser lab.

## 1. Meet the spreadsheet object

1 · Find your footing · bound

Apps Script runs JavaScript on Google’s servers. Its built-in services connect code to Workspace. In a spreadsheet-bound project, the active spreadsheet is the container file. A Spreadsheet contains Sheet tabs; a Range identifies cells. You will use main as a selectable entry function.

**Challenge:** Return the current spreadsheet name.

**Method focus:** SpreadsheetApp.getActiveSpreadsheet, Spreadsheet.getName

**Hint:** Call getName() on the Spreadsheet object.

**Watch for:** Calling getName() on a string fails because the string is no longer a Spreadsheet.

```javascript
function main() {
  return SpreadsheetApp.getActiveSpreadsheet().getName();
}
```

## 2. Open a file explicitly

1 · Find your footing · standalone

A standalone project is created at script.google.com rather than inside a file. It has no guaranteed active spreadsheet. Open the required file using its ID and your account’s access. The fixture ID book-demo stands in for the characters between /d/ and /edit in a real Sheets URL.

**Challenge:** Open book-demo by ID and return its name.

**Method focus:** SpreadsheetApp.openById

**Hint:** openById returns a Spreadsheet; it does not activate an editor window.

**Watch for:** Depending on an active file makes a background standalone job unreliable.

```javascript
function main() {
  return SpreadsheetApp.openById("book-demo").getName();
}
```

## 3. Find a tab and handle absence

1 · Find your footing · bound

Tab names are case-sensitive. getSheetByName returns a Sheet when found, or null. Check the result before using it. Explicit names are more predictable than whatever tab a person last selected.

**Challenge:** Return the Tasks tab name, or Missing if it does not exist.

**Method focus:** Spreadsheet.getSheetByName

**Hint:** Test whether the returned sheet exists before calling getName().

**Watch for:** Calling a method on null produces an error.

```javascript
function main() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tasks");
  return sheet ? sheet.getName() : "Missing";
}
```

## 4. Read one cell

1 · Find your footing · bound

getRange selects cells; it does not read their contents yet. getValue reads the top-left cell as a value. Numbers stay numbers, which matters when calculating. A1 notation uses column letters and one-based row numbers.

**Challenge:** Return the minutes in Tasks!B2.

**Method focus:** Sheet.getRange, Range.getValue

**Hint:** Use getValue() for the top-left cell value.

**Watch for:** getRange() alone returns a Range object, not its contents.

```javascript
function main() {
  const book = SpreadsheetApp.getActiveSpreadsheet();
    const tasks = book.getSheetByName("Tasks");
  return tasks.getRange("B2").getValue();
}
```

## 5. Read a rectangular grid

1 · Find your footing · bound

getValues returns a two-dimensional array: one array per row, one item per column. Sheet coordinates start at 1; JavaScript array indexes start at 0. Reading one rectangle lets you process values locally instead of making many service calls.

**Challenge:** Return all values in Tasks!A2:C4.

**Method focus:** Range.getValues

**Hint:** A three-row, three-column range becomes an array of three arrays.

**Watch for:** Using values[1][1] reads the second row and second column of the returned array.

```javascript
function main() {
  const book = SpreadsheetApp.getActiveSpreadsheet();
    const tasks = book.getSheetByName("Tasks");
  return tasks.getRange("A2:C4").getValues();
}
```

## 6. Inspect the data boundary

1 · Find your footing · bound

getLastRow reports the final row containing content, not the number of data records. A header counts as content; blank gaps can appear before the final row. getLastColumn similarly finds the rightmost content. Empty sheets return zero for both.

**Challenge:** Return [last row, last column] for Tasks.

**Method focus:** Sheet.getLastRow, Sheet.getLastColumn

**Hint:** Keep header rows and blank gaps separate from your record count.

**Watch for:** Treating the last row number as a record count can include headers and gaps.

```javascript
function main() {
  const book = SpreadsheetApp.getActiveSpreadsheet();
    const tasks = book.getSheetByName("Tasks");
  return [tasks.getLastRow(), tasks.getLastColumn()];
}
```

## 7. Write a status cell

2 · Change cells with intent · bound

setValue writes a single supplied value to the selected range. Choose a one-cell destination when writing one status. It replaces existing cell content. In this lab every run starts from clean fictional data; real spreadsheet edits persist.

**Challenge:** Write Ready into Report!A1.

**Method focus:** Range.setValue

**Hint:** Select a one-cell range before calling setValue().

**Watch for:** Writing into the wrong tab can overwrite unrelated content.

```javascript
function main() {
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Report").getRange("A1").setValue("Ready");
}
```

## 8. Write a rectangular report

2 · Change cells with intent · bound

setValues requires a rectangular two-dimensional array whose height and width exactly match the target range. It batches several cells into one write. Even a one-row result needs an outer array.

**Challenge:** Write [["Metric", "Minutes"], ["Open", 50]] to Report!A1:B2.

**Method focus:** Range.setValues

**Hint:** The destination is two rows by two columns.

**Watch for:** A flat array cannot satisfy setValues because it has no row arrays.

```javascript
function main() {
  const rows = [["Metric", "Minutes"], ["Open", 50]];
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Report").getRange(1,1,2,2).setValues(rows);
}
```

## 9. Append a log entry

2 · Change cells with intent · bound

appendRow adds one row after the current data region. It is convenient for a small log, but repeated runs add repeated entries. A large import is generally better handled with a single setValues call.

**Challenge:** Append ["Review", 15, "Open"] to Tasks.

**Method focus:** Sheet.appendRow

**Hint:** appendRow takes a single row array.

**Watch for:** Rerunning a log operation may create a duplicate; appending is not an update.

```javascript
function main() {
  const book = SpreadsheetApp.getActiveSpreadsheet();
    const tasks = book.getSheetByName("Tasks");
  tasks.appendRow(["Review",15,"Open"]);
}
```

## 10. Clear values without clearing style

2 · Change cells with intent · bound

Range.clearContent removes values and formulas while leaving formatting. Sheet.clearContents applies across the sheet. The singular and plural names belong to different objects. Select the smallest intended region.

**Challenge:** Clear Tasks!C2:C4 without changing names or minutes.

**Method focus:** Range.clearContent

**Hint:** Range.clearContent() targets only the chosen cells.

**Watch for:** Clearing the entire sheet also removes headers and unrelated values.

```javascript
function main() {
  const book = SpreadsheetApp.getActiveSpreadsheet();
    const tasks = book.getSheetByName("Tasks");
  tasks.getRange("C2:C4").clearContent();
}
```

## 11. Make headers readable

2 · Change cells with intent · bound

Formatting methods often return the same Range, allowing a method chain. Formatting communicates structure without changing underlying values. The lab records format settings separately so you can inspect what was requested.

**Challenge:** Make Tasks!A1:C1 bold with background #d9ead3.

**Method focus:** Range.setFontWeight, Range.setBackground

**Hint:** Both formatting methods return the Range, so they can be chained.

**Watch for:** Formatting a cell does not convert its stored value.

```javascript
function main() {
  const book = SpreadsheetApp.getActiveSpreadsheet();
    const tasks = book.getSheetByName("Tasks");
  tasks.getRange("A1:C1").setFontWeight("bold").setBackground("#d9ead3");
}
```

## 12. Format numbers for people

2 · Change cells with intent · bound

A number format changes how numeric values display. It does not multiply, round, or otherwise change the underlying number. The simulator records the requested pattern; Google Sheets renders the formatted text in the real file.

**Challenge:** Apply the number format 0.0 to Tasks!B2:B4.

**Method focus:** Range.setNumberFormat

**Hint:** setNumberFormat changes presentation while the stored number stays the same.

**Watch for:** Do not use a display format as a substitute for a calculation.

```javascript
function main() {
  const book = SpreadsheetApp.getActiveSpreadsheet();
    const tasks = book.getSheetByName("Tasks");
  tasks.getRange("B2:B4").setNumberFormat("0.0");
}
```

## 13. Build an open-task report

3 · Useful spreadsheet workflows · bound

Read a complete data rectangle once, remove the header, filter in memory, then write the result once. An empty result has no valid zero-height Range, so guard before writing. Clear the old report first so short new results do not leave stale rows.

**Challenge:** Copy only Open task rows to Report, with no header; handle no matches.

**Method focus:** Sheet.getDataRange, Range.getValues, Range.setValues

**Hint:** Guard rows.length before creating a range for the filtered results.

**Watch for:** An empty array cannot be written into a zero-height range.

```javascript
function main() {
  const book = SpreadsheetApp.getActiveSpreadsheet();
    const tasks = book.getSheetByName("Tasks");
  const rows = tasks.getDataRange().getValues().slice(1).filter(row => row[2] === "Open");
  const report = book.getSheetByName("Report");
  report.clearContents();
  if (rows.length) report.getRange(1,1,rows.length,3).setValues(rows);
}
```

## 14. Create a missing report tab

3 · Useful spreadsheet workflows · bound

A reusable script should cope with both a new file and a file it has processed before. Find the destination first, then create it only if missing. Creating the same named tab unconditionally fails on the second run.

**Challenge:** Ensure Report exists, write Summary to A1, and return its name.

**Method focus:** Spreadsheet.insertSheet, Spreadsheet.getSheetByName

**Hint:** Reuse a found Sheet, or call insertSheet when no Sheet exists.

**Watch for:** Unconditionally inserting Report fails if that tab already exists.

```javascript
function main() {
  const book = SpreadsheetApp.getActiveSpreadsheet();
  const report = book.getSheetByName("Report") || book.insertSheet("Report");
  report.getRange("A1").setValue("Summary");
  return report.getName();
}
```

## 15. Add a spreadsheet menu

3 · Useful spreadsheet workflows · bound

A bound spreadsheet can add a custom menu in its editor UI. Each item names a handler function as a string; it does not call that function immediately. In a real project put menu creation inside onOpen(e), save, and reopen the spreadsheet.

**Challenge:** Create a Tasks menu with a Build report item that names buildReport as its handler.

**Method focus:** SpreadsheetApp.getUi, Ui.createMenu, Menu.addItem

**Hint:** Pass the handler name as text; provide that function in the real project.

**Watch for:** Calling buildReport() while defining the item runs it at menu creation time.

```javascript
function main() {
  SpreadsheetApp.getUi().createMenu("Tasks").addItem("Build report", "buildReport").addToUi();
}
```

## 16. Understand an edit event

3 · Useful spreadsheet workflows · bound

An onEdit(e) trigger receives an event with a Range. Clicking Run in the editor does not manufacture that event. Check which sheet, row and column changed before doing work. This workshop constructs a Range directly to practice the same inspection methods.

**Challenge:** Return the sheet name, row and column for Tasks!C2.

**Method focus:** Range.getRow, Range.getColumn, Range.getSheet

**Hint:** An edit handler should inspect e.range and exclude headers or unrelated columns.

**Watch for:** Assuming e exists when you manually run onEdit causes undefined errors.

```javascript
function main() {
  const book = SpreadsheetApp.getActiveSpreadsheet();
    const tasks = book.getSheetByName("Tasks");
  const range = tasks.getRange("C2");
  return [range.getSheet().getName(),range.getRow(),range.getColumn()];
}
```

## 17. Store script configuration

4 · Configuration and reliability · bound

Script properties store string key-value settings shared within a script project. They persist between real executions. They are useful for IDs and configuration, but anyone with edit access to the project should be considered able to access them.

**Challenge:** Store REPORT_ID as book-demo and return it.

**Method focus:** PropertiesService.getScriptProperties, Properties.setProperty, Properties.getProperty

**Hint:** getProperty returns a string or null when the key is absent.

**Watch for:** Script properties should not be treated as hidden from project editors.

```javascript
function main() {
  const props = PropertiesService.getScriptProperties();
  props.setProperty("REPORT_ID", "book-demo");
  return props.getProperty("REPORT_ID");
}
```

## 18. Handle missing configuration

4 · Configuration and reliability · bound

Missing settings are an expected starting condition. Check for null or an empty value and return a useful message before making an API call. This makes failures easier to diagnose than an obscure openById error.

**Challenge:** Read REPORT_ID; return Missing REPORT_ID if absent, otherwise return the ID.

**Method focus:** Properties.getProperty

**Hint:** Validate required configuration before opening a file.

**Watch for:** Passing a missing ID to openById hides the real setup problem.

```javascript
function main() {
  const id = PropertiesService.getScriptProperties().getProperty("REPORT_ID");
  return id || "Missing REPORT_ID";
}
```

## 19. Serialize structured settings

4 · Configuration and reliability · bound

Properties contain strings, not JavaScript objects. Serialize an object with JSON.stringify and reconstruct it with JSON.parse. Parsing can fail for damaged or old data, so production code should validate the shape and choose a recovery behavior.

**Challenge:** Store {"status":"Open","limit":10} under FILTER as JSON; return the parsed limit.

**Method focus:** Properties.setProperty, Properties.getProperty

**Hint:** JSON preserves structure inside a string value.

**Watch for:** Directly storing an object does not preserve its fields as structured data.

```javascript
function main() {
  const props = PropertiesService.getScriptProperties();
  props.setProperty("FILTER", JSON.stringify({status:"Open", limit:10}));
  return JSON.parse(props.getProperty("FILTER")).limit;
}
```

## 20. Protect a shared update

4 · Configuration and reliability · bound

Two executions can read the same old counter and overwrite each other. A script lock coordinates code in that project that follows the same lock discipline. Acquire it before the read and release it in finally. A lock does not turn multiple service operations into a database transaction.

**Challenge:** Increment COUNT under a script lock; return Busy if unavailable, otherwise the new number.

**Method focus:** LockService.getScriptLock, Lock.tryLock, Lock.releaseLock

**Hint:** Use finally so the lock is released even if the update throws.

**Watch for:** Acquiring a lock after reading the old value leaves a race window.

```javascript
function main() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(1000)) return "Busy";
  try {
    const p = PropertiesService.getScriptProperties();
    const n = Number(p.getProperty("COUNT") || 0) + 1;
    p.setProperty("COUNT", String(n));
    return n;
  } finally { lock.releaseLock(); }
}
```

## 21. Cache a reusable value

4 · Configuration and reliability · bound

Cache is a temporary performance aid. Values are strings and may disappear before their requested expiration, so a miss must remain correct. The browser model supports expiration but does not simulate early eviction. Use properties or files for durable state.

**Challenge:** Put the string Ready under summary for 60 seconds, then return it.

**Method focus:** CacheService.getScriptCache, Cache.put, Cache.get

**Hint:** A cache miss must trigger recomputation or a valid fallback.

**Watch for:** Cache expiration is not a durability guarantee.

```javascript
function main() {
  const cache = CacheService.getScriptCache();
  cache.put("summary", "Ready", 60);
  return cache.get("summary");
}
```

## 22. Inspect an HTTP response

4 · Configuration and reliability · bound

fetch returns an HTTPResponse, not parsed JSON. Read its status and then parse its text when the content is JSON. With muteHttpExceptions, HTTP failure responses are returned for inspection rather than throwing immediately; network errors may still throw. The endpoint here is fictional.

**Challenge:** Fetch https://example.invalid/items with muted HTTP errors; return the status and raw body.

**Method focus:** UrlFetchApp.fetch, HTTPResponse.getResponseCode, HTTPResponse.getContentText

**Hint:** Inspect the HTTP status before trusting a response body.

**Watch for:** An HTTPResponse is not the parsed JSON object.

```javascript
function main() {
  const response = UrlFetchApp.fetch("https://example.invalid/items", {muteHttpExceptions:true});
  return [response.getResponseCode(),response.getContentText()];
}
```

## 23. Walk a Drive file iterator

5 · Connect Workspace services · bound

getFiles returns an iterator rather than an array. Check hasNext before consuming next. Iteration order should not be relied upon; sort values if presentation order matters. folder-demo is a fictional ID to replace in Google.

**Challenge:** Return sorted file names inside folder-demo.

**Method focus:** DriveApp.getFolderById, Folder.getFiles, FileIterator.hasNext, FileIterator.next

**Hint:** Use while (iterator.hasNext()) to consume each file once.

**Watch for:** Calling next() after exhaustion fails.

```javascript
function main() {
  const files = DriveApp.getFolderById("folder-demo").getFiles();
  const names = [];
  while (files.hasNext()) names.push(files.next().getName());
  return names.sort();
}
```

## 24. Copy a template into a folder

5 · Connect Workspace services · bound

makeCopy creates a separate file. Supplying a Folder places the new copy in that location. Every run creates another copy unless you add a deliberate deduplication strategy. Copying does not itself merge fields or replace placeholders.

**Challenge:** Copy template into folder-output with the name Weekly copy; return its name.

**Method focus:** DriveApp.getFileById, File.makeCopy

**Hint:** makeCopy returns the new File so you can inspect its ID or name.

**Watch for:** Repeated copies require an explicit plan to prevent unwanted duplicates.

```javascript
function main() {
  const folder = DriveApp.getFolderById("folder-output");
  const copy = DriveApp.getFileById("template").makeCopy("Weekly copy",folder);
  return copy.getName();
}
```

## 25. Export a plain-text report

5 · Connect Workspace services · bound

A folder can create a text file from a name, text content and MIME type. This is useful for simple summaries or logs. CSV requires additional escaping for quotes, commas and line breaks; merely naming a text file .csv does not handle that.

**Challenge:** Create summary.txt in folder-output containing Open minutes: 50.

**Method focus:** Folder.createFile, MimeType.PLAIN_TEXT

**Hint:** The content string becomes the text file body.

**Watch for:** A filename extension does not transform or escape file content.

```javascript
function main() {
  const file = DriveApp.getFolderById("folder-output").createFile("summary.txt","Open minutes: 50",MimeType.PLAIN_TEXT);
  return file.getName();
}
```

## 26. Create a Google Doc summary

5 · Connect Workspace services · bound

DocumentApp creates a document and returns its object. Google Docs supports tabs: explicitly choose the first document tab here, then access its body. Append a paragraph and save. A production workflow should select the intended tab and handle nested tabs if needed.

**Challenge:** Create a document named Task summary and append Open minutes: 50 to its first tab.

**Method focus:** DocumentApp.create, Document.getTabs, Body.appendParagraph

**Hint:** Choose the document tab before asking for its body.

**Watch for:** A document can contain more than one tab; assuming all content is in one body misses information.

```javascript
function main() {
  const doc = DocumentApp.create("Task summary");
  const body = doc.getTabs()[0].asDocumentTab().getBody();
  body.appendParagraph("Open minutes: 50");
  const text = body.getText();
  doc.saveAndClose();
  return text;
}
```

## 27. Create a draft for review

5 · Connect Workspace services · bound

createDraft saves an email draft; it does not send it. Keep recipient, subject and body separate. In Google, Gmail authorization is needed. This lab records the draft locally without contacting Gmail. Real runs can create repeated drafts.

**Challenge:** Create a draft to learner@example.com with subject Task summary and body Open minutes: 50.

**Method focus:** GmailApp.createDraft

**Hint:** Creating a draft leaves a human review step before sending.

**Watch for:** A draft is still a persistent mailbox item when executed in Google.

```javascript
function main() {
  GmailApp.createDraft("learner@example.com","Task summary","Open minutes: 50");
}
```

## 28. Create a simple intake form

5 · Connect Workspace services · bound

FormApp creates and edits forms. A text item builder configures one question. Form access, publication and responder settings must be reviewed in the real Forms UI before distribution; creating a form does not establish the intended audience.

**Challenge:** Create Task intake with a required text question named Task name.

**Method focus:** FormApp.create, Form.addTextItem, TextItem.setRequired

**Hint:** setRequired(true) configures the question, not the form’s sharing audience.

**Watch for:** Creating a form does not mean its access settings match your intended audience.

```javascript
function main() {
  FormApp.create("Task intake").addTextItem().setTitle("Task name").setRequired(true);
}
```

## 29. Plan a recurring execution

6 · Put it together · standalone

Installable triggers run under their creator’s account and require appropriate authorization. Avoid installing duplicates for the same handler. This exercise checks current-user project triggers by handler; it is a simple policy, not a scheduler with exact wall-clock guarantees.

**Challenge:** Ensure exactly one hourly trigger for buildReport, even if main runs twice.

**Method focus:** ScriptApp.getProjectTriggers, ScriptApp.newTrigger

**Hint:** Check existing triggers before creating another recurring job.

**Watch for:** Creating a new trigger every run can multiply background executions.

```javascript
function main() {
  const exists = ScriptApp.getProjectTriggers().some(t => t.getHandlerFunction() === "buildReport");
  if (!exists) ScriptApp.newTrigger("buildReport").timeBased().everyHours(1).create();
}
```

## 30. Capstone: a standalone open-task digest

6 · Put it together · standalone

Combine explicit file access, one batch read, local filtering, one report write and a reviewable draft. The script is intentionally a manual prototype: a real recurring digest also needs deduplication, error handling and a policy for partial success. Do not schedule it until repeated runs have the behavior you intend.

**Challenge:** Open book-demo, total Open task minutes, write [["Open minutes", total]] to Report, and draft that total to learner@example.com.

**Method focus:** SpreadsheetApp.openById, Range.getValues, Range.setValues, GmailApp.createDraft

**Hint:** Read from an explicit file and calculate from current values instead of a hard-coded total.

**Watch for:** Scheduling this prototype without deduplication can create repeated drafts.

```javascript
function main() {
  const book = SpreadsheetApp.openById("book-demo");
  const rows = book.getSheetByName("Tasks").getDataRange().getValues().slice(1);
  const total = rows.filter(r=>r[2]==="Open").reduce((sum,r)=>sum+Number(r[1]),0);
  book.getSheetByName("Report").getRange("A1:B1").setValues([["Open minutes",total]]);
  GmailApp.createDraft("learner@example.com","Open task digest","Open minutes: "+total);
}
```