32 built-in-method workshops. Search by method, service or project context.
1. Identify the bound spreadsheet
Bound · SpreadsheetApp.getActiveSpreadsheet()
Returns: Spreadsheet or null
A bound project can refer to its parent spreadsheet in a supported bound execution context. The returned Spreadsheet object exposes getName and getId. Guard null before reading its methods. This is the starting point for a menu-driven tool used in one file. It is not a promise of active context in a web app or standalone execution.
Effect: Reads the parent spreadsheet identity; no cells change.
function runTask(){
const book=SpreadsheetApp.getActiveSpreadsheet();
if(!book)return null;
return {name:book.getName(),id:book.getId()};
}
Google setup: Create a disposable Sheet and open Extensions → Apps Script. Run from its editor; compare the logged name with the file title.
A standalone project has no container file to infer. Supply the spreadsheet ID to openById, then select its tab and cell. The ID is the part of a Sheets URL after /d/ and before the next slash. Access still depends on the executing account. Opening by ID identifies a file; it does not grant permission.
Effect: Reads A2 from an explicitly selected file.
function runTask(){
const book=SpreadsheetApp.openById("book-demo");
const sheet=book.getSheetByName("Tasks");
if(!sheet)throw new Error("Missing Tasks tab");
return sheet.getRange("A2").getValue();
}
Google setup: Create a project at script.google.com. Create a separate Sheet with a Tasks tab, Name in A1 and Ada in A2. Replace book-demo with its real ID.
A Spreadsheet holds sheets. getSheetByName looks up a tab by its exact name and returns null if absent. A file title and a sheet tab name are different. Handle absence explicitly before calling getRange. This is useful when several tabs live in one workbook and the automation must always target the same one.
Effect: Looks up a tab without creating one.
function runTask(){const book=SpreadsheetApp.openById("book-demo");const sheet=book.getSheetByName("Tasks");return sheet?sheet.getName():null;}
Google setup: Use either project type. Replace book-demo with a test spreadsheet ID. Rename Tasks temporarily to observe the null result.
getRange selects a rectangle; getValues reads its cells in one call. Each returned row is an array. Values retain their underlying types, which may include numbers, booleans, strings and dates in real Sheets. The local fixture has no dates or formula engine. Batch reading is useful when building a report from multiple rows.
Effect: Reads A2:C3 as two rows of values.
function runTask(){return SpreadsheetApp.openById("book-demo").getSheetByName("Tasks").getRange("A2:C3").getValues();}
Google setup: Use a Tasks tab with Name, Minutes, Status headers and two body rows. Replace the example spreadsheet ID. Log the resulting array.
Use display values when your output should reflect the text a person sees. Real Sheets applies locale and number/date formatting. This model only converts fixture primitives to strings; it does not implement locale or formatting rendering. Compare this method with getValues before choosing data for arithmetic or an email summary.
Effect: Reads text representations rather than raw cell types.
function runTask(){return SpreadsheetApp.openById("book-demo").getSheetByName("Tasks").getRange("B2:B3").getDisplayValues();}
Google setup: Use a test spreadsheet with numeric values in B2:B3. Apply a number format in Google and compare getValues with getDisplayValues.
setValues expects one array per row, with exactly the same shape as the destination range. This exercise writes a header and one data row. It does not append. Rerunning replaces the same rectangle. Validate both dimensions before writing and use a dedicated output tab so unrelated content is not overwritten.
Effect: Writes a two-by-two report into Report A1:B2.
function runTask(){const sheet=SpreadsheetApp.openById("book-demo").getSheetByName("Report");sheet.getRange(1,1,2,2).setValues([["Name","Minutes"],["Ada",30]]);return sheet.getRange("A1:B2").getValues();}
Google setup: Create an empty dedicated Report tab in your test spreadsheet. Replace book-demo. Confirm only A1:B2 is written.
Many Range setters return the same Range, allowing a chain of formatting calls. Here a header becomes bold with a background, while the minutes column receives a numeric format. Formatting does not convert strings into numbers. The inspector records formatting metadata; it does not render a real Sheets grid.
Effect: Applies formatting to selected cells.
function runTask(){const sheet=SpreadsheetApp.openById("book-demo").getSheetByName("Tasks");sheet.getRange("A1:C1").setFontWeight("bold").setBackground("#d9ead3");sheet.getRange("B2:B4").setNumberFormat("0.00");return "Formatted";}
Google setup: Run in a test Sheet with Tasks data. Inspect the visible header and number formatting after running.
appendRow is convenient for a small log entry. Every successful call adds another row, so retries can duplicate entries. For larger output use a prepared rectangular batch. This lesson deliberately appends one controlled audit record; the repeat check demonstrates why append and replace are different operations.
Effect: Adds an audit row at the end of the report.
function runTask(){const sheet=SpreadsheetApp.openById("book-demo").getSheetByName("Report");sheet.appendRow(["Checked",3]);return sheet.getLastRow();}
Google setup: Use an empty Report tab in a disposable workbook. Run twice and inspect the two audit entries.
Range.clearContent removes contents while retaining formatting. It is not the same as clearing an entire sheet. Use it only on a deliberately owned output area. If a regenerated report becomes shorter, clearing its previous output range can prevent stale rows; validate the new input before clearing anything.
Effect: Clears only the selected range values.
function runTask(){const r=SpreadsheetApp.openById("book-demo").getSheetByName("Tasks").getRange("B2:B3");r.clearContent();return r.getValues();}
Google setup: Practice only on a copied Tasks tab. Compare the cleared values and retained formatting.
getLastRow reports the last row containing content, not the number of business records. Subtract the header only after checking that body rows exist. getRange cannot take a height of zero. Blank gaps inside the rectangle remain in the returned data. This pattern is a starting point for a bounded report reader.
Effect: Reads data beneath the header without requesting zero rows.
function runTask(){const s=SpreadsheetApp.openById("book-demo").getSheetByName("Tasks");const last=s.getLastRow();if(last<2)return [];return s.getRange(2,1,last-1,3).getValues();}
Google setup: Use Tasks with a header, then try a header-only copy and a completely empty tab.
Look up the output tab before inserting it. Repeated runs should reuse it rather than fail because its name already exists. This is a simple ensure-exists pattern, not a concurrency guarantee. If multiple executions may initialize simultaneously, plan a lock and recheck inside it.
Effect: Ensures one Summary tab exists.
function runTask(){const book=SpreadsheetApp.openById("book-demo");const sheet=book.getSheetByName("Summary")||book.insertSheet("Summary");return sheet.getName();}
Google setup: Use a test spreadsheet without Summary. Run twice and confirm the second run reuses the same tab.
A bound menu starts with SpreadsheetApp.getUi. createMenu returns a Menu builder; addItem maps a visible label to a function name string. addToUi inserts the menu. In a real project call this code from onOpen and define the handler it names. A standalone project cannot use another file’s editor UI merely by opening its ID.
Effect: Adds a menu entry to the bound spreadsheet UI.
function runTask(){SpreadsheetApp.getUi().createMenu("Tools").addItem("Build report","buildReport").addToUi();return "Menu ready";}
function buildReport(){console.log("Connect your report workflow here");}
Google setup: Open a bound Sheet project. Add function onOpen(){runTask();}, save, and reload the Sheet. The menu handler must be a top-level function.
An edit event supplies a Range object. Read its sheet, row and dimensions; act only on the intended tab, body row and column. The example writes Reviewed beside an edited name. Script writes do not recursively cause a simple onEdit event. A manual editor run has no event, so the function returns ignored.
Effect: Marks column C in the row of a single-cell edit to column A.
function runTask(e){if(!e||!e.range)return "ignored";const r=e.range;if(r.getSheet().getName()!=="Tasks"||r.getColumn()!==1||r.getRow()<2||r.getNumRows()!==1||r.getNumColumns()!==1)return "ignored";r.getSheet().getRange(r.getRow(),3).setValue("Reviewed");return "updated";}
Google setup: In a bound Tasks Sheet add function onEdit(e){runTask(e);}. Edit A2 manually. Do not click Run expecting Google to supply e.
openByUrl accepts a spreadsheet URL. Use it when configuration provides the URL rather than only the ID. It does not mean opening a browser tab, and it does not bypass file permissions. A URL to a Drive folder or a document is not a spreadsheet URL.
Effect: Identifies a spreadsheet using its full URL.
function runTask(){const book=SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/book-demo/edit");return book.getId();}
Google setup: Create a standalone project and replace the complete example URL with a test spreadsheet URL you can access.
Drive iterators are not ordinary arrays. Use hasNext before next to consume each File object, then call getName or getId. Limit the inventory to an explicit folder. The fixture order is deterministic for practice; do not rely on Google’s iterator order for a business rule. Sort if order matters.
Effect: Lists names in one folder.
function runTask(){const files=DriveApp.getFolderById("folder-demo").getFiles();const names=[];while(files.hasNext()){names.push(files.next().getName());}return names.sort();}
Google setup: Create a standalone project and a small test folder with two files. Replace folder-demo with its real folder ID.
A File object describes a Drive item. getName supplies its current label and getUrl a link to open it. A title can change or be duplicated, so stable IDs are useful for configuration. An invalid or inaccessible ID can throw; do not interpret that as an empty file.
Effect: Reads file metadata without changing the file.
function runTask(){const file=DriveApp.getFileById("template");return {name:file.getName(),url:file.getUrl()};}
Google setup: Replace template with a file ID from your test Drive. Log the name and URL; do not assume names are unique.
makeCopy returns the new File, not the original. Give the copy a useful name and retain its ID for later steps. Every call creates another copy. A daily workflow needs a saved output ID or another deduplication plan before unattended use. The simulator copies fixture text and metadata only.
Effect: Creates a new file copy in a specified folder.
function runTask(){const source=DriveApp.getFileById("template");const folder=DriveApp.getFolderById("folder-output");const copy=source.makeCopy("Project brief",folder);return copy.getName();}
Google setup: Create a test template and output folder. Replace both IDs. Run once and inspect the created copy before trying repeat behavior.
A folder can create a file from a name, text content and MIME type. This is useful for audit exports or small generated reports. It creates rather than updates. Plan naming and retention deliberately and avoid exporting sensitive records accidentally. The returned File gives an ID and URL for the generated output.
Effect: Creates a text file in the output folder.
function runTask(){const file=DriveApp.getFolderById("folder-output").createFile("audit.txt","Checked 3 tasks",MimeType.PLAIN_TEXT);return file.getName();}
Google setup: Use a dedicated test output folder. Replace folder-output. Open the created text file and check its content.
Create a document, obtain its body, append text, then close saved changes. The method returns a Document object whose ID and URL can be retained. For a newly created single-tab document getBody addresses that initial body. The next workshop shows explicit tab access for an existing multi-tab document.
Effect: Creates a document and adds a paragraph.
function runTask(){const doc=DocumentApp.create("Weekly brief");doc.getBody().appendParagraph("Three tasks reviewed.");doc.saveAndClose();return doc.getId();}
Google setup: Use a standalone test project. Each run creates a new document; retain the returned ID to update that document later.
Modern Google Docs supports tabs. Select a tab before working with its body so your target is explicit. getTabs returns top-level tabs; nested tabs require traversal. This example intentionally selects the first top-level tab. A production workflow may store a specific tab ID instead. Appending twice creates two paragraphs.
Effect: Appends a paragraph to the first top-level document tab.
function runTask(){const doc=DocumentApp.openById("doc-demo");const body=doc.getTabs()[0].asDocumentTab().getBody();body.appendParagraph("Reviewed today.");const text=body.getText();doc.saveAndClose();return text;}
Google setup: Create a test Doc with Starting text in its first tab. Replace doc-demo. Inspect which tab receives the paragraph.
FormApp.create returns a Form. addTextItem creates a question, and its setters configure it. The edit URL is for the form editor, not a promise that responses are published or available to everyone. Inspect publication, responder access and collection settings in Google before distributing a form.
Effect: Creates a form with one required text question.
function runTask(){const form=FormApp.create("Workshop feedback");form.addTextItem().setTitle("What did you learn?").setRequired(true);return form.getId();}
Google setup: Run in a standalone test project. Find the new form in Drive and inspect its question and responder access settings before sharing.
Choose the calendar deliberately and pass Date objects for the start and end instants. ISO strings with explicit offsets reduce ambiguity. Confirm the displayed local time in Google Calendar. Repeated calls create repeated events. This example adds no guests; it does not implement invitations or duplicate detection.
Effect: Adds an event to a selected calendar.
function runTask(){const calendar=CalendarApp.getCalendarById("calendar-demo");if(!calendar)throw new Error("Calendar unavailable");const event=calendar.createEvent("Practice review",new Date("2026-11-02T15:00:00Z"),new Date("2026-11-02T15:30:00Z"));return event.getId();}
Google setup: Create a dedicated test calendar, copy its calendar ID from settings, and replace calendar-demo. Inspect the time after running once.
Either · GmailApp.createDraft(recipient, subject, body)
Returns: GmailDraft
Create a plain-text draft and keep its returned ID for tracking. Creating a draft requires Gmail authorization and changes the mailbox. It is distinct from sending. A queue should record successful draft IDs and reconcile interruptions so retries do not create duplicates. The browser stores a local outbox record only.
Effect: Creates a draft; it does not send it.
function runTask(){const draft=GmailApp.createDraft("learner@example.com","Practice summary","Three tasks were reviewed.");return draft.getId();}
Google setup: Use a permitted test account. Replace the address with an address you control and inspect the draft in Gmail. No send method is used.
MailApp exposes a remaining daily recipient count. Treat it as a current observation, not a permanent allowance or reservation. Quotas and policies can change; other executions may consume them. This workshop only reads a fictional configured count. In a real sending workflow, inspect recipients and handle failures as well as quota availability.
Effect: Reads the currently reported remaining recipient quota.
function runTask(){return MailApp.getRemainingDailyQuota();}
Google setup: Run in a test project and compare the result with current Google quota documentation. This example sends nothing.
Script properties persist beyond one execution and are shared at the script level. Store a file ID so a scheduled job can find its target. Values are strings; parse typed settings deliberately. This is not a secret vault against project editors. Keep configuration setup separate from the scheduled worker.
Effect: Stores and reads configuration as strings.
function runTask(){const properties=PropertiesService.getScriptProperties();properties.setProperty("TARGET_ID","book-demo");return properties.getProperty("TARGET_ID");}
Google setup: Set TARGET_ID to your test spreadsheet ID. Inspect Project Settings → Script properties after running.
UrlFetchApp makes a server-side HTTP request. muteHttpExceptions lets your code inspect an HTTP error response rather than relying on a thrown status error. Check the status before parsing the body as JSON. A successful status alone does not validate the payload schema. Browser practice consumes a fixture response and makes no network request.
Effect: Requests data and reads status and response text.
function runTask(){const response=UrlFetchApp.fetch("https://example.com/data",{muteHttpExceptions:true});const status=response.getResponseCode();if(status!==200)return {status,items:[]};const data=JSON.parse(response.getContentText());return {status,items:data.items};}
Google setup: Replace example.com/data with a permitted test endpoint returning an items array. Do not paste credentials into public examples.
Either · Utilities.formatDate(date, timeZone, format)
Returns: String
Use an explicit timezone and pattern when creating report labels. A formatted string is not the original Date object. Timezones can shift calendar dates near midnight; test those boundaries in Google. The local model supports only UTC and yyyy-MM-dd and rejects other patterns instead of pretending to reproduce the full service.
Effect: Formats a Date into a predictable date label.
function runTask(){return Utilities.formatDate(new Date("2026-11-02T15:00:00Z"),"UTC","yyyy-MM-dd");}
Google setup: Run in either project type. In Google, try a relevant timezone and compare a date close to midnight UTC.
Concurrent executions can collide when writing shared state. Obtain a lock, attempt acquisition with a timeout, and release it in finally. A lock is not acquired simply by requesting the Lock object. The simulator models availability but not real concurrency, waiting or distributed execution.
Effect: Acquires a script lock, writes one log entry, then releases it.
function runTask(){const lock=LockService.getScriptLock();if(!lock.tryLock(1000))return "busy";try{SpreadsheetApp.openById("book-demo").getSheetByName("Report").appendRow(["Locked write"]);return "written";}finally{lock.releaseLock();}}
Google setup: Run on a dedicated Report tab. Inspect failures and confirm the finally block still releases the lock. Real concurrent behavior needs a Google test.
A trigger builder describes when a function should run; create installs it. The handler is supplied as a string and must exist. Running this installer repeatedly creates multiple triggers. Installable triggers run as their creator and consume that account’s permissions and quotas. The browser records a trigger but never schedules work.
Effect: Installs an hourly trigger for the named function.
function runTask(){const trigger=ScriptApp.newTrigger("scheduledReview").timeBased().everyHours(1).create();return trigger.getHandlerFunction();}
function scheduledReview(){console.log("Connect the explicitly configured worker here");}
Google setup: Use a standalone test project. Inspect its Triggers page after one installation. Remove the test trigger when finished; do not repeatedly run the installer.
Returns: Array of the current user’s project triggers
List triggers visible to the current user in the project, match the exact handler and delete just those entries. Do not delete every project trigger as a cleanup shortcut. Another account’s triggers are not automatically visible. Pair a scoped cleanup function with each installable workflow.
Effect: Deletes only triggers with the specified handler.
function runTask(){let removed=0;for(const trigger of ScriptApp.getProjectTriggers()){if(trigger.getHandlerFunction()==="scheduledReview"){ScriptApp.deleteTrigger(trigger);removed++;}}return removed;}
Google setup: Run only in your test project after installing the prior workshop’s trigger. Confirm unrelated handlers remain on the Triggers page.
HtmlService creates an HtmlOutput object; SpreadsheetApp.getUi().showSidebar displays it in the bound editor. This is not a standalone web-app deployment. The browser records the output as text, never rendering arbitrary learner HTML. To call server functions from a real sidebar use google.script.run with success and failure handlers, as described in the official communication guide.
Effect: Displays authored HTML in the bound spreadsheet sidebar.
function runTask(){const output=HtmlService.createHtmlOutput("<h2>Report helper</h2><p>Choose a menu action to build your report.</p>").setTitle("Helper");SpreadsheetApp.getUi().showSidebar(output);return output.getTitle();}
Google setup: Run in a bound spreadsheet from its editor or a menu. A real sidebar is a separate HTML interface; never insert untrusted text as raw HTML.
A cache reduces repeat work; it is not durable configuration. get can return null, including before the requested expiry, so always provide a path to rebuild missing data. Store strings, serializing structured data if needed. The fixture models an expiry timestamp but not Google’s possible early eviction or size limits.
Effect: Stores a short-lived value that may be absent later.
function runTask(){const cache=CacheService.getScriptCache();let value=cache.get("summary");if(value===null){value="3 tasks";cache.put("summary",value,60);}return value;}
Google setup: Use cache for rebuildable summaries. Use PropertiesService for configuration and a durable store for records you cannot afford to lose.