# Apps Script Coach — Guided Workshop Handbook

DiscoveryVIP · October 8, 2026

Browser exercises simulate selected Apps Script services and never access Google accounts. Code must be adapted to accessible test files before running in Google. The coach is rule-based, not a live AI tutor.

## 1. Your first service call

Start here

Goal: Write the current spreadsheet name into Report!A1.

Apps Script is JavaScript running in Google’s scripting environment. Google provides service objects such as SpreadsheetApp so your code can work with Workspace files. A function groups instructions; main is the entry-point name used in these workshops. The braces hold the work to run. In Google’s editor you select that function and click Run. Here the coach calls it in a fresh simulated environment. A Spreadsheet represents the whole file, a Sheet represents one tab, and a Range represents cells. Keep those objects distinct as you follow the chain.

### Plan

1. Get the current file with SpreadsheetApp.getActiveSpreadsheet().
2. Ask the file for its name with getName().
3. Choose its Report tab and write the name to A1 using setValue().

### Predict

What does getActiveSpreadsheet() represent?

1. One cell
2. The spreadsheet file
3. A row of values

Answer: 2. The Spreadsheet object contains the tabs; it is not a Range or an array.

### Progressive hints

1. Start with book.getName(); it returns text.
2. book.getSheetByName("Report") returns the destination Sheet.
3. Use .getRange("A1").setValue(name) on that Sheet.

### Worked solution

```javascript
function main() {
  const book = SpreadsheetApp.getActiveSpreadsheet();
  const name = book.getName();
  book.getSheetByName("Report").getRange("A1").setValue(name);
  Logger.log(name);
}
```

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app

## 2. Bound or standalone?

Start here

Goal: Open the sample spreadsheet by ID and write Ready to Report!A1.

A bound project is created from a Workspace file. A standalone project is created independently. Do not rely on a currently open spreadsheet when your code has no active container context. Use an explicit ID and check that the executing account can access that file. The lab’s ID book-demo is a fixture, not a real Drive identifier. In your own Google project replace it with a test spreadsheet ID. Opening a file does not create or copy it. Authorization and permission errors are real deployment concerns that this browser cannot reproduce.

### Plan

1. Use SpreadsheetApp.openById("book-demo").
2. Choose the Report tab from the returned file.
3. Write Ready to its A1 cell; the same code should work in both test contexts.

### Predict

In a standalone project, which approach avoids relying on an active file?

1. openById with an accessible file ID
2. Assume a browser tab is active
3. Call getRange on SpreadsheetApp directly

Answer: 1. An explicit file ID identifies the resource independently of active editor context.

### Progressive hints

1. The active spreadsheet is null in the standalone test.
2. Replace the first service method, not the Report tab.
3. Use SpreadsheetApp.openById("book-demo").

### Worked solution

```javascript
function main() {
  const book = SpreadsheetApp.openById("book-demo");
  book.getSheetByName("Report").getRange("A1").setValue("Ready");
}
```

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/guides/standalone

## 3. Read one cell

Start here

Goal: Copy Tasks!A2 to Report!A1 using getValue().

getRange("A2") describes a location. getValue() retrieves the top-left cell’s value from that Range. A stored cell can be text, a number, a Boolean or another supported value type. JavaScript variables hold the returned value; they are not live links back to the cell. When the source changes, read it again. In our fixtures A2 contains a person’s name, and the second check changes it to prove that your script reads the cell rather than hard-coding Ada.

### Plan

1. Get the Tasks tab.
2. Read A2 with getRange(...).getValue().
3. Write the retrieved value into Report!A1.

### Predict

After const name = range.getValue(), name is…

1. A value read at that moment
2. A live cell object
3. The whole spreadsheet

Answer: 1. getValue returns the cell value, not the Range itself.

### Progressive hints

1. A Range does not become a value until you call getValue().
2. Save the retrieved value in a const variable.
3. Use setValue(name) on the destination range.

### Worked solution

```javascript
function main() {
  const book = SpreadsheetApp.getActiveSpreadsheet();
  const name = book.getSheetByName("Tasks").getRange("A2").getValue();
  book.getSheetByName("Report").getRange("A1").setValue(name);
}
```

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/reference/spreadsheet/range

## 4. Change a specific cell

Start here

Goal: Set Tasks!C2 to Done without changing the header or another person’s status.

A1 notation combines a column letter and a row number. Numeric getRange(row, column) uses one-based positions: row 2, column 3 is C2. JavaScript array indexes are different: they begin at zero. Read the destination carefully before writing. A successful execution only means the code ran; it does not prove it changed the intended cell. The coach checks both the requested result and neighboring values.

### Plan

1. Get the Tasks Sheet.
2. Select row 2, column 3 or C2.
3. Use setValue("Done") on that Range.

### Predict

Which call selects C2?

1. getRange(1, 2)
2. getRange(2, 3)
3. getRange(3, 2)

Answer: 2. The first argument is row and the second is column, both starting at one.

### Progressive hints

1. The starter swaps the row and column.
2. Column C is column 3.
3. Replace getRange(3, 2) with getRange(2, 3).

### Worked solution

```javascript
function main() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tasks");
  sheet.getRange(2, 3).setValue("Done");
}
```

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/reference/spreadsheet/sheet

## 5. Read a table into an array

Working with Sheets

Goal: Copy the first data row A2:C2 to Report!A1:C1.

getValues() returns an array of rows. Even one row is nested: [["Ada", 30, "Open"]]. The outer array describes rows; each inner array describes columns. setValues() expects the same rectangular shape as the destination Range. Keeping the returned matrix intact is often the simplest way to move a block. It also avoids confusing getValue, which reads one value, with getValues, which reads a matrix.

### Plan

1. Select Tasks!A2:C2.
2. Read its matrix using getValues().
3. Write that matrix to a one-row, three-column destination.

### Predict

What shape does getValues() return for A2:C2?

1. Three independent arguments
2. A flat array only
3. An outer array containing one row array

Answer: 3. The result preserves a two-dimensional grid.

### Progressive hints

1. The singular read method loses the rectangular shape.
2. Use getValues(), including the final s.
3. Keep the returned matrix intact when passing it to setValues.

### Worked solution

```javascript
function main() {
  const book = SpreadsheetApp.getActiveSpreadsheet();
  const values = book.getSheetByName("Tasks").getRange("A2:C2").getValues();
  book.getSheetByName("Report").getRange("A1:C1").setValues(values);
}
```

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/reference/spreadsheet/range

## 6. Write a rectangular block

Working with Sheets

Goal: Write a two-row summary table to Report!A1:B2.

Batch writing means preparing a rectangular array in JavaScript and sending it to a matching Range. For two rows and two columns, supply two inner arrays with two items each. Mismatched dimensions are a common error. In this task the required rows are ["Metric", "Value"] and ["Tasks", 3]. The number 3 is intentionally a fixed example here; the next workshops will read and calculate from actual source rows.

### Plan

1. Prepare the two row arrays.
2. Select Report!A1:B2.
3. Pass the matrix to setValues(), not setValue().

### Predict

How many inner arrays are required for a 2-row range?

1. Two
2. Four
3. One flat array

Answer: 1. Each inner array represents a row.

### Progressive hints

1. Compare the destination’s row count with values.length.
2. A1:B1 is only one row high.
3. Change the destination to A1:B2.

### Worked solution

```javascript
function main() {
  const report = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Report");
  report.getRange("A1:B2").setValues([["Metric", "Value"], ["Tasks", 3]]);
}
```

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/reference/spreadsheet/range

## 7. Append a useful record

Working with Sheets

Goal: Append ["Mina", 25, "Open"] to the Tasks table.

appendRow() adds a row below the current data. It accepts a flat array for that one row, unlike the two-dimensional matrix used by setValues(). Appending is useful for logs and incoming records. Re-running the function appends again, so it is not automatically duplicate-safe. In this lesson that repeat behavior is intentional: the second check calls main twice and expects two added rows.

### Plan

1. Get the Tasks Sheet.
2. Call appendRow with three values in one array.
3. Inspect the preview to confirm the record is added below existing data.

### Predict

Running an append-only function twice normally…

1. Updates the same row automatically
2. Adds two rows
3. Creates no second row

Answer: 2. appendRow does not perform duplicate detection for you.

### Progressive hints

1. appendRow belongs to a Sheet, not a Range.
2. Pass one flat row array.
3. Use .appendRow(["Mina", 25, "Open"]).

### Worked solution

```javascript
function main() {
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tasks")
    .appendRow(["Mina", 25, "Open"]);
}
```

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/reference/spreadsheet/sheet

## 8. Format the header

Working with Sheets

Goal: Make Tasks!A1:C1 bold with background #dceee7.

Range formatting methods change presentation rather than the stored values. Many methods return the Range, which allows method chaining. That return value is why setFontWeight(...).setBackground(...) works on the same cells. Formatting should support meaning: use it to distinguish a header, then verify that the underlying data has not changed. The simulator records formatting metadata; the preview shows it separately from the raw cell values.

### Plan

1. Select A1:C1 on Tasks.
2. Call setFontWeight("bold").
3. Set the background to #dceee7.

### Predict

What does formatting a cell normally change?

1. The source row count
2. Only the JavaScript variable name
3. Its presentation

Answer: 3. The content and presentation are separate concerns.

### Progressive hints

1. Use a formatting method on the header Range.
2. setFontWeight expects "bold" in this task.
3. Chain setBackground("#dceee7") after the font-weight call.

### Worked solution

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

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/reference/spreadsheet/range

## 9. Build an open-task report

Working with Sheets

Goal: Copy the header and only Open tasks into Report. Rebuilding must remove old report rows.

This is your first complete read-transform-write workflow. getDataRange().getValues() retrieves the table. JavaScript keeps the header and filters data rows by the Status column. clearContents() removes old report values before a single batch write. This prevents stale rows when a later report is shorter. In production, validate the new output before clearing an important destination, and consider a separate output tab or backup strategy. Use a practice file for this exercise.

### Plan

1. Read the Tasks matrix and separate its first row.
2. Keep data rows whose third item equals Open.
3. Clear Report contents and write the header plus filtered rows with matching dimensions.

### Predict

Why clear old report contents before writing a shorter result?

1. To remove stale trailing rows
2. To delete the source file
3. To increase the number of tasks

Answer: 1. A shorter write does not automatically erase cells below it.

### Progressive hints

1. The third column is row[2] in a JavaScript array.
2. Use rows.slice(1).filter(row => row[2] === "Open").
3. Write output.length rows by 3 columns after clearContents().

### Worked solution

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

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/guides/support/best-practices

## 10. Remember a setting

Useful Workspace services

Goal: Store REPORT_TAB = Report using script properties, then read it into Tasks!D1.

PropertiesService provides small key-value stores for configuration. Script properties are shared within the script project; user and document properties have different scopes. Values are strings, not arbitrary live JavaScript objects. Store a tab name here so it can be read independently of a local variable. In a real project, consider who can edit the project and access its properties; do not treat project configuration as a secret vault.

### Plan

1. Get PropertiesService.getScriptProperties().
2. Use setProperty with key REPORT_TAB and value Report.
3. Read getProperty("REPORT_TAB") and write it to D1.

### Predict

What type does a stored property value normally use?

1. A Sheet object
2. A string
3. A live formula object

Answer: 2. Property values are stored as strings.

### Progressive hints

1. The first argument is a key; the second is a value.
2. Store with setProperty, then retrieve with getProperty.
3. Write the retrieved text using setValue.

### Worked solution

```javascript
function main() {
  const props = PropertiesService.getScriptProperties();
  props.setProperty("REPORT_TAB", "Report");
  const name = props.getProperty("REPORT_TAB");
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tasks").getRange("D1").setValue(name);
}
```

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/reference/properties/properties-service

## 11. Give a script a menu

Useful Workspace services

Goal: Add a Practice menu with one Run report item linked to buildReport.

A bound spreadsheet can have a custom menu. getUi() gives access to the editor interface in an appropriate bound context. createMenu(), addItem() and addToUi() build and display it. The item uses a function name string, not an immediate function call. In a real spreadsheet you would usually call this menu setup from onOpen(e), and define the named handler. The lab records the menu structure; it does not display a real Google editor menu.

### Plan

1. Call SpreadsheetApp.getUi().createMenu("Practice").
2. Add Run report with handler name "buildReport".
3. Finish with addToUi().

### Predict

Which argument registers a handler without calling it immediately?

1. buildReport()
2. The current cell value
3. "buildReport"

Answer: 3. The menu stores the name of the function to run later.

### Progressive hints

1. getUi() provides a Ui object in this bound fixture.
2. addItem needs the visible label and a handler-name string.
3. Finish the builder chain with addToUi().

### Worked solution

```javascript
function main() {
  SpreadsheetApp.getUi().createMenu("Practice")
    .addItem("Run report", "buildReport").addToUi();
}
function buildReport() {
  Logger.log("Report handler selected");
}
```

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/guides/menus

## 12. Work with a document body

Useful Workspace services

Goal: Open doc-demo and append the paragraph Practice summary.

Google Docs can contain tabs. This lesson explicitly selects the first tab, converts it to a DocumentTab, and then gets its body. appendParagraph adds text to that body. Opening a document by ID requires the executing account to have access in a real project. Re-running an append operation adds another paragraph; it is not a replacement. The lab provides one document with one tab to make the object chain visible.

### Plan

1. Use DocumentApp.openById("doc-demo").
2. Get the first tab with getTabs()[0], then asDocumentTab().getBody().
3. Append Practice summary and close the document.

### Predict

What does appendParagraph do on a body?

1. Adds a paragraph
2. Replaces every paragraph
3. Creates a spreadsheet

Answer: 1. Appending adds content; replacing requires a different operation.

### Progressive hints

1. getTabs returns an array; its first element is index 0.
2. Convert the tab with asDocumentTab() before getBody().
3. Use body.appendParagraph("Practice summary").

### Worked solution

```javascript
function main() {
  const doc = DocumentApp.openById("doc-demo");
  const body = doc.getTabs()[0].asDocumentTab().getBody();
  body.appendParagraph("Practice summary");
  doc.saveAndClose();
}
```

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/reference/document/document

## 13. Create a Drive text file

Useful Workspace services

Goal: Create notes.txt containing Ready to learn in folder-output.

DriveApp provides access to Drive files and folders. getFolderById() locates a destination folder; createFile() creates a new file within it. The returned File object can provide its ID or URL for later work. The ID in this workshop is fictional. In a real project replace it with an accessible test folder ID. Creating the file again usually makes a second file rather than updating the first one, even when the name matches.

### Plan

1. Get the destination folder by ID.
2. Call createFile with name, content and MimeType.PLAIN_TEXT.
3. Inspect the created file record in the output panel.

### Predict

Creating the same named file again usually…

1. Updates the old file automatically
2. Creates another file
3. Renames the folder

Answer: 2. A matching name is not a stable identity or an update operation.

### Progressive hints

1. createFile belongs to the destination Folder.
2. Pass three arguments: file name, content, MIME type.
3. Use MimeType.PLAIN_TEXT for the third argument.

### Worked solution

```javascript
function main() {
  const folder = DriveApp.getFolderById("folder-output");
  const file = folder.createFile("notes.txt", "Ready to learn", MimeType.PLAIN_TEXT);
  Logger.log(file.getId());
}
```

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/reference/drive/folder

## 14. Create a useful form

Useful Workspace services

Goal: Create a Feedback form with one required text item titled Your name.

FormApp.create() creates a form object. addTextItem() returns a text-item object, which has its own methods such as setTitle() and setRequired(). This illustrates why knowing return types matters: a method on the item is different from a method on the whole form. In Google, creating a form changes your account. Here it creates only a record in the browser fixture. Sharing, publishing and collecting responses are separate steps not simulated by this lesson.

### Plan

1. Create the form titled Feedback.
2. Add a text item to the form.
3. Set its title and required status.

### Predict

What does addTextItem() return?

1. The entire spreadsheet
2. A submitted response
3. A TextItem you can configure

Answer: 3. The returned item has title and required-field methods.

### Progressive hints

1. The item is created by form.addTextItem().
2. setRequired expects the Boolean true, not the string "true".
3. Chain .setTitle("Your name").setRequired(true).

### Worked solution

```javascript
function main() {
  const form = FormApp.create("Feedback");
  form.addTextItem().setTitle("Your name").setRequired(true);
}
```

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/reference/forms/form-app

## 15. Prepare an email draft

Useful Workspace services

Goal: Create one draft to learner@example.com with subject Practice and body Report ready.

GmailApp.createDraft(recipient, subject, body) creates a draft rather than sending it. Draft-first automation gives a person an opportunity to review the recipient and content before taking the external action. In a real account this method requires authorization and creates a draft in Gmail. The lab creates only a simulated draft record and cannot send messages. A draft operation is still an account change in production, so practice with your own test recipient and review the result.

### Plan

1. Use GmailApp.createDraft with three text arguments.
2. Inspect the recipient, subject and body.
3. Do not add a send operation; the goal is a draft only.

### Predict

createDraft immediately sends the message…

1. No
2. Yes
3. Only if the subject is short

Answer: 1. Creating and sending are different operations.

### Progressive hints

1. The three arguments are recipient, subject and body.
2. The recipient is a string, including quotes.
3. Use GmailApp.createDraft("learner@example.com", "Practice", "Report ready").

### Worked solution

```javascript
function main() {
  const draft = GmailApp.createDraft("learner@example.com", "Practice", "Report ready");
  Logger.log(draft.getId());
}
```

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/reference/gmail/gmail-app

## 16. Read an API response

Useful Workspace services

Goal: Fetch the fixture endpoint and write its first item name into Report!A1.

UrlFetchApp.fetch() returns an HTTPResponse object, not already-parsed JSON. getContentText() gives the response text, and JSON.parse() converts valid JSON into JavaScript data. Inspect the response shape before choosing a property path. In this fixture the data has an items array, and its first item has a name. The address uses example.invalid deliberately; the lab intercepts this service and returns authored data. A real script needs an actual approved endpoint and suitable error handling.

### Plan

1. Fetch https://example.invalid/items in the simulated service.
2. Read text, then JSON.parse it.
3. Write data.items[0].name into Report!A1.

### Predict

Which step turns JSON text into a JavaScript object?

1. getRange()
2. JSON.parse()
3. setValue()

Answer: 2. Parsing transforms valid JSON text; fetch itself returns a response object.

### Progressive hints

1. Call getContentText() on the response first.
2. Use JSON.parse(response.getContentText()).
3. Array indexing starts at zero: data.items[0].name.

### Worked solution

```javascript
function main() {
  const response = UrlFetchApp.fetch("https://example.invalid/items");
  const data = JSON.parse(response.getContentText());
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Report").getRange("A1").setValue(data.items[0].name);
}
```

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/reference/url-fetch/http-response

## 17. Handle an HTTP failure

Reliable automations

Goal: Write OK for a 200 response and HTTP error for a 500 response.

External services can return failures. With muteHttpExceptions:true, Apps Script returns an HTTPResponse for HTTP error status codes so your code can inspect the status. It does not turn a failed response into a successful one, and it does not silence every possible network exception. In this focused exercise, distinguish a status code of 200 from other codes and write a clear outcome. Real integrations may accept a range of success codes, validate the response body and use bounded retries where appropriate.

### Plan

1. Fetch with {muteHttpExceptions: true}.
2. Read getResponseCode().
3. Write OK for 200, otherwise HTTP error.

### Predict

muteHttpExceptions:true means…

1. All requests succeed
2. No authorization is needed
3. HTTP error responses can be inspected instead of thrown

Answer: 3. The status still needs to be checked.

### Progressive hints

1. Without muteHttpExceptions, the 500 fixture throws before your check.
2. Use response.getResponseCode() to get the number.
3. Choose a message with === 200 and write it to the report.

### Worked solution

```javascript
function main() {
  const response = UrlFetchApp.fetch("https://example.invalid/status", {muteHttpExceptions: true});
  const message = response.getResponseCode() === 200 ? "OK" : "HTTP error";
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Report").getRange("A1").setValue(message);
}
```

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app

## 18. Avoid repeated setup

Reliable automations

Goal: Create one hourly trigger for refreshReport, even when main runs twice.

An installable trigger is a stored resource. Calling create repeatedly can install duplicates, causing repeated executions later. Check existing project triggers before adding the handler. This lesson identifies a trigger by handler name, which is suitable for this simple one-trigger-per-handler setup; a more complex project may need to distinguish trigger types and resources too. The browser records trigger definitions but never runs a schedule. In Google, installable triggers run under the creator’s authorization and should be tested and monitored.

### Plan

1. Read ScriptApp.getProjectTriggers().
2. Check whether any getHandlerFunction() is refreshReport.
3. Only when absent, build a time-based trigger everyHours(1).

### Predict

Creating the same trigger repeatedly is automatically deduplicated…

1. No; check before creating
2. Yes
3. Only at night

Answer: 1. Repeated create calls can add duplicate trigger resources.

### Progressive hints

1. getProjectTriggers() returns trigger objects.
2. Compare getHandlerFunction() with the handler-name string.
3. Guard newTrigger(...).timeBased().everyHours(1).create() with if (!exists).

### Worked solution

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

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/guides/triggers/installable

## 19. Use a lock responsibly

Reliable automations

Goal: Append one log row only if a script lock is acquired, and release it afterward.

Two executions can overlap and act on the same shared data. LockService helps coordinate cooperating executions. tryLock(timeout) reports whether the lock was acquired; do not continue with the protected write when it returns false. Use try/finally so releaseLock is called even if the protected work throws. A lock is not a transaction and does not undo a partial write. In this exercise the simulator tests both available and unavailable lock conditions.

### Plan

1. Get a script lock and call tryLock(1000).
2. Return immediately if no lock is acquired.
3. Append ["Logged"] to Report inside try, then release in finally.

### Predict

If tryLock returns false, you should…

1. Continue the protected write anyway
2. Skip the protected work or handle the contention
3. Delete the sheet

Answer: 2. Proceeding would defeat the purpose of acquiring the lock.

### Progressive hints

1. Use if (!lock.tryLock(1000)) return.
2. The write belongs inside try.
3. Place lock.releaseLock() in a finally block.

### Worked solution

```javascript
function main() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(1000)) return;
  try {
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Report").appendRow(["Logged"]);
  } finally {
    lock.releaseLock();
  }
}
```

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/reference/lock/lock-service

## 20. Finish a reusable report builder

Reliable automations

Goal: Open book-demo by ID, rebuild the Open-task report and store LAST_COUNT as its data-row count.

Combine what you learned into a small automation that does not depend on an active spreadsheet. Read once, select Open rows in JavaScript, replace the output in a dedicated Report tab and store the result count as a script property. Each operation should have a clear purpose. This capstone checks a standalone run and a changed source with no Open tasks. Before adapting it to real data, validate headers and row shapes, decide how to recover after a partial write and document who owns the workflow.

### Plan

1. Open the file explicitly and read Tasks.getDataRange().getValues().
2. Build header plus Open data rows, clear Report and batch-write them.
3. Store LAST_COUNT as String(output.length - 1) in script properties.

### Predict

Why test a no-Open-tasks input?

1. To remove the need for permissions
2. To prove the UI color is right
3. To check empty results and stale-output removal

Answer: 3. A useful report must handle an empty data result while keeping its header.

### Progressive hints

1. Reuse the report pattern, but openById replaces active-spreadsheet access.
2. Count data rows by subtracting the one header row.
3. The no-Open case should leave one header row and LAST_COUNT equal to "0".

### Worked solution

```javascript
function main() {
  const book = SpreadsheetApp.openById("book-demo");
  const rows = book.getSheetByName("Tasks").getDataRange().getValues();
  const output = [rows[0], ...rows.slice(1).filter(row => row[2] === "Open")];
  const report = book.getSheetByName("Report");
  report.clearContents();
  report.getRange(1, 1, output.length, 3).setValues(output);
  PropertiesService.getScriptProperties().setProperty("LAST_COUNT", String(output.length - 1));
}
```

### Reflect

Explain the object chain, the expected effect and what must change for a real Google project.

Official reference: https://developers.google.com/apps-script/guides/support/best-practices