50 More Google Apps Script Snippets (201–250)

by

Real-World Automation Patterns for Google Workspace

https://github.com/lsvekis/Apps-Script-Code-Snippets/tree/main/google-apps-script-50-more-snippets-201-250

Google Apps Script becomes truly powerful when you move beyond basic examples and start building real automation systems.

That’s exactly what this collection delivers.

This set of 50 additional Google Apps Script snippets (201–250) focuses on practical, reusable building blocks you can plug directly into production workflows inside:

  • Google Sheets
  • Google Drive
  • Google Docs
  • Google Slides
  • Gmail
  • Google Calendar
  • Google Forms
  • Web Apps & API endpoints
  • Utilities (locking, retries, properties, performance)

These aren’t demo-only examples.
They’re workflow components.

Why These Snippets Matter

Most tutorials show isolated functions.

But real automation requires:

  • Configuration handling
  • Logging
  • Error protection
  • Secure storage
  • Batch-safe execution
  • Trigger awareness
  • Export pipelines
  • Email delivery systems
  • Folder management patterns

This set moves toward system-level thinking.

What’s Inside (By Category)

📊 Sheets Automation (201–216, 235–245)

These snippets focus on building robust spreadsheet systems:

Highlights:

  • Automatic custom menus with onOpen()
  • Config sheet creation + key-value lookups
  • Safe row appending for performance
  • File-level spreadsheet backups
  • Multi-column deduplication
  • Trim and cleanup tools
  • Status dropdown + conditional formatting automation
  • Archiving completed rows
  • Export to PDF + email delivery
  • Freeze formulas to values
  • Auto-numbering rows
  • Runtime logging
  • Template reset patterns

Why This Matters:

Spreadsheets are often used as lightweight databases.
These snippets help you:

  • Prevent messy data
  • Standardize workflows
  • Protect critical rows
  • Build reporting pipelines
  • Automate exports

📁 Drive Workflow Utilities (217–220, 237, 241, 248)

Drive automation becomes powerful when you:

  • Automatically create folder structures
  • Rename and standardize files
  • Detect duplicates
  • Share folders programmatically
  • Convert Docs to PDFs
  • Copy files between directories

These snippets support:

  • Client onboarding folder setup
  • Report packaging
  • Cleanup operations
  • Controlled file distribution

📝 Docs & Slides Generation (221–224)

These snippets allow you to:

  • Generate templated Docs
  • Replace placeholders dynamically
  • Convert lists into bullet formatting
  • Create slide decks from structured arrays

Use cases include:

  • Auto report generation
  • Presentation skeleton builders
  • Training deck automation
  • AI-generated content formatting pipelines

📬 Gmail Automation (225–226, 242)

Email workflows are critical for automation.

This set includes:

  • Send emails from sheet rows
  • Attachment extraction pipelines
  • Auto replies for flagged emails

These patterns help you build:

  • Notification systems
  • Intake automation
  • Lightweight CRM workflows

📅 Calendar Automation (227–228, 243)

Included examples:

  • Recurring event creation
  • Listing daily events
  • Creating events directly from sheet rows

These are powerful for:

  • Scheduling from trackers
  • Time blocking systems
  • Programmatic meeting management

📝 Forms Automation (229–230)

  • Auto-generate Google Forms
  • Send confirmation emails on form submission

This is useful for:

  • Intake systems
  • Surveys
  • Event registration automation

🌐 Web App & API Utilities (233–234)

These snippets move into API-level patterns:

  • API key validation
  • Retry wrappers for UrlFetch
  • Secured doGet endpoints

These are foundational pieces for:

  • Internal APIs
  • Microservice-style Apps Script web apps
  • Reliable external integrations

⚙️ System Utilities & Best Practices (231–232, 239, 249–250)

These are critical for scaling Apps Script safely:

  • Store secrets in Script Properties
  • Prevent overlapping execution with LockService
  • Maintain run history logs
  • Generate ISO timestamps
  • Measure runtime performance

These are what separate hobby scripts from production systems.

Patterns You’ll Notice

This collection emphasizes:

1️⃣ Safety

  • LockService to prevent collisions
  • PropertiesService for secure storage
  • Retry logic for unstable APIs

2️⃣ Performance

  • Batch writing instead of repeated appendRow()
  • Controlled export patterns
  • Lightweight logging

3️⃣ Reusability

  • Config-driven logic
  • Folder utilities
  • Template resets

4️⃣ Automation Pipelines

Example system flow:

Sheet → Generate Doc → Convert to PDF → Save to Drive → Email link → Log execution

Several snippets combine naturally into full pipelines.

Example: Full Reporting Flow

Using just snippets from this collection, you can:

  1. Clean sheet data (212)
  2. Archive completed rows (216)
  3. Generate PDF export (235)
  4. Email report link (236)
  5. Log execution (239)
  6. Prevent overlapping runs (232)

That’s a complete automated reporting engine.

Who This Is For

This collection is ideal for:

  • Google Workspace administrators
  • Operations managers
  • Developers building internal tools
  • Educators teaching Apps Script
  • Automation enthusiasts
  • Anyone replacing repetitive manual processes

What Makes This Different

Instead of random examples, this set focuses on:

✔ Real workflows
✔ Production-friendly patterns
✔ Automation architecture
✔ Scalable building blocks
✔ Practical integrations

It’s designed to help you build systems, not just scripts.

How to Use This Collection

  1. Start with Config + Logging snippets
  2. Add business logic
  3. Protect execution with LockService
  4. Export outputs (PDF, CSV, Drive)
  5. Deliver results (Email / API)
  6. Monitor via Run History

You now have a lightweight automation framework.

What’s Next?

If this collection is useful, future expansions could include:

  • Advanced Dashboard Systems
  • BigQuery integrations
  • Admin SDK automations
  • Enterprise Drive governance tools
  • Full API frameworks for Apps Script
  • AI-integrated Google Workspace pipelines

Final Thoughts

Google Apps Script is one of the most underutilized automation platforms available.

With the right reusable building blocks, you can transform:

  • Spreadsheets into workflow engines
  • Docs into templated report generators
  • Drive into structured file pipelines
  • Gmail into automated notification systems
  • Calendar into scheduling infrastructure

These 50 additional snippets move closer to building real automation ecosystems.

If you’re serious about automating Google Workspace, this is the next layer.

201) Sheets: Add onOpen() menu automatically

What it does: Adds a custom menu every time the spreadsheet opens.
How it works: onOpen() runs automatically and builds a UI menu.
When to use: Make tools discoverable for users.
Gotchas: Must be container-bound to a Sheet.

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Toolkit')
    .addItem('Run Daily Cleanup', 'ex212_dailyCleanup')
    .addItem('Export Sheet to PDF', 'ex235_exportActiveSheetAsPdf')
    .addToUi();
}

202) Sheets: Create a “Config” sheet if missing

What it does: Ensures a Config tab exists with default keys.
How it works: Checks by name, creates sheet, writes defaults.
When to use: Any script that needs settings.
Gotchas: Avoid overwriting if it already exists.

function ex202_ensureConfigSheet() {
  const ss = SpreadsheetApp.getActive();
  let sh = ss.getSheetByName('Config');
  if (!sh) sh = ss.insertSheet('Config');
  if (sh.getLastRow() === 0) {
    sh.getRange(1,1,4,2).setValues([
      ['key','value'],
      ['ownerEmail', Session.getActiveUser().getEmail()],
      ['timezone', ss.getSpreadsheetTimeZone()],
      ['version','1.0']
    ]);
  }
}

203) Sheets: Read config value by key

What it does: Looks up a config value from the Config sheet.
How it works: Reads table, builds a map, returns value.
When to use: Reusable settings access.
Gotchas: Requires Config sheet.

function ex203_getConfigValue(key) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Config');
  if (!sh) throw new Error('Missing Config sheet');
  const data = sh.getDataRange().getValues();
  const map = Object.fromEntries(data.slice(1).map(r => [String(r[0]), r[1]]));
  return map[key];
}

204) Sheets: Append row safely (batch-friendly)

What it does: Adds a row to the bottom without appendRow() overhead.
How it works: Finds next row + setValues().
When to use: Logging + performance.
Gotchas: Don’t forget 2D array format.

function ex204_fastAppendRow(values) {
  const sh = SpreadsheetApp.getActiveSheet();
  const row = sh.getLastRow() + 1;
  sh.getRange(row, 1, 1, values.length).setValues([values]);
}

205) Sheets: Create a timestamped backup copy (file-level)

What it does: Makes a Drive copy of the spreadsheet file.
How it works: Uses DriveApp on the spreadsheet file ID.
When to use: Before major transformations.
Gotchas: Requires Drive auth.

function ex205_backupSpreadsheetFile() {
  const ss = SpreadsheetApp.getActive();
  const file = DriveApp.getFileById(ss.getId());
  const name = ss.getName() + ' Backup ' + Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), 'yyyyMMdd-HHmm');
  const copy = file.makeCopy(name);
  Logger.log(copy.getUrl());
}

206) Sheets: Deduplicate by multiple columns (A + B)

What it does: Removes duplicates using more than one key column.
How it works: removeDuplicates([1,2]).
When to use: Contacts, imports, merged lists.
Gotchas: Operates on the range it’s called on.

function ex206_removeDuplicatesAB() {
  const sh = SpreadsheetApp.getActiveSheet();
  sh.getDataRange().removeDuplicates([1,2]);
}

207) Sheets: Trim only selected range

What it does: Cleans whitespace in selected range.
How it works: Reads active range → transforms strings → writes back.
When to use: Manual cleanup tool.
Gotchas: Doesn’t touch non-strings.

function ex207_trimSelection() {
  const r = SpreadsheetApp.getActiveRange();
  const out = r.getValues().map(row => row.map(v =>
    typeof v === 'string' ? v.trim().replace(/\s+/g,' ') : v
  ));
  r.setValues(out);
}

208) Sheets: Convert “Yes/No” text to checkboxes

What it does: Turns Yes/No into TRUE/FALSE + checkboxes.
How it works: Maps values then insertCheckboxes().
When to use: Standardizing imported survey/task data.
Gotchas: Case variations handled.

function ex208_yesNoToCheckboxes() {
  const sh = SpreadsheetApp.getActiveSheet();
  const r = sh.getActiveRange();
  const out = r.getValues().map(row => row.map(v => {
    const s = String(v || '').toLowerCase().trim();
    if (['yes','y','true','1'].includes(s)) return true;
    if (['no','n','false','0'].includes(s)) return false;
    return '';
  }));
  r.setValues(out);
  r.insertCheckboxes();
}

209) Sheets: Set header formatting quickly

What it does: Styles row 1 as a header.
How it works: Applies font/bold/freeze.
When to use: After generating a new report sheet.
Gotchas: Adjust column count if needed.

function ex209_formatHeaderRow() {
  const sh = SpreadsheetApp.getActiveSheet();
  const lastCol = Math.max(1, sh.getLastColumn());
  const header = sh.getRange(1,1,1,lastCol);
  header.setFontWeight('bold').setBackground('#f3f4f6');
  sh.setFrozenRows(1);
}

210) Sheets: Create a “Status” dropdown + color formatting

What it does: Adds status dropdown and color rules.
How it works: Data validation + conditional formats.
When to use: Workflow trackers.
Gotchas: Rules add onto existing ones.

function ex210_statusDropdownAndColors() {
  const sh = SpreadsheetApp.getActiveSheet();
  const range = sh.getRange('C2:C');
  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(['New','In Progress','Blocked','Done'], true)
    .setAllowInvalid(false).build();
  range.setDataValidation(rule);  const rules = sh.getConditionalFormatRules();
  const mk = (text, bg) => SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo(text).setBackground(bg).setRanges([range]).build();
  sh.setConditionalFormatRules([...rules, mk('New','#e5e7eb'), mk('In Progress','#dbeafe'), mk('Blocked','#fee2e2'), mk('Done','#dcfce7')]);
}

211) Sheets: “Last updated” cell stamp

What it does: Writes current timestamp into a cell (e.g., A1).
How it works: Sets Date value.
When to use: Reports, dashboards.
Gotchas: Use sheet timezone for display formatting.

function ex211_stampLastUpdated() {
  const sh = SpreadsheetApp.getActiveSheet();
  sh.getRange('A1').setValue('Last updated: ' + new Date());
}

212) Sheets: Daily cleanup (example menu action)

What it does: Trims strings, removes empty rows at bottom, formats header.
How it works: Combines multiple utilities.
When to use: Daily report prep.
Gotchas: Only cleans used range.

function ex212_dailyCleanup() {
  ex209_formatHeaderRow();
  const sh = SpreadsheetApp.getActiveSheet();
  const rng = sh.getDataRange();
  const vals = rng.getValues().map(row => row.map(v => typeof v === 'string' ? v.trim() : v));
  rng.setValues(vals);
}

213) Sheets: Email the active sheet as CSV attachment

What it does: Converts sheet data to CSV and emails it.
How it works: Builds CSV string → blob → GmailApp.sendEmail.
When to use: Automated report delivery.
Gotchas: Email quotas apply.

function ex213_emailSheetAsCsv() {
  const sh = SpreadsheetApp.getActiveSheet();
  const values = sh.getDataRange().getValues();
  const csv = values.map(r => r.map(v => `"${String(v).replace(/"/g,'""')}"`).join(',')).join('\n');
  const blob = Utilities.newBlob(csv, 'text/csv', sh.getName() + '.csv');
  GmailApp.sendEmail(Session.getActiveUser().getEmail(), 'CSV Export: ' + sh.getName(), 'Attached.', {attachments:[blob]});
}

214) Sheets: Create a “Logs” sheet and log messages

What it does: Writes logs to a Logs tab.
How it works: Ensures sheet exists then appends.
When to use: Debugging production scripts.
Gotchas: Batch logs for heavy usage.

function ex214_log(message) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Logs') || ss.insertSheet('Logs');
  sh.appendRow([new Date(), Session.getActiveUser().getEmail(), message]);
}

215) Sheets: Protect header row only

What it does: Protects row 1 so headers can’t be edited.
How it works: Creates range protection.
When to use: Shared sheets.
Gotchas: Protection permissions depend on domain/user.

function ex215_protectHeaderRow() {
  const sh = SpreadsheetApp.getActiveSheet();
  const p = sh.getRange('1:1').protect().setDescription('Protect headers');
  p.removeEditors(p.getEditors());
}

216) Sheets: Create “Archive” sheet and move completed rows

What it does: Moves rows where Status = Done to an Archive sheet.
How it works: Filters data in memory, rewrites both sheets.
When to use: Keeping main tracker clean.
Gotchas: Assumes headers in row 1 and Status in column C.

function ex216_archiveDoneRows() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const arch = ss.getSheetByName('Archive') || ss.insertSheet('Archive');
  const data = sh.getDataRange().getValues();
  const header = data[0];
  const keep = [header];
  const moved = [];
  for (let i=1;i<data.length;i++) {
    if (String(data[i][2]).trim().toLowerCase() === 'done') moved.push(data[i]);
    else keep.push(data[i]);
  }
  sh.clearContents();
  sh.getRange(1,1,keep.length,keep[0].length).setValues(keep);
  if (arch.getLastRow() === 0) arch.getRange(1,1,1,header.length).setValues([header]);
  if (moved.length) arch.getRange(arch.getLastRow()+1,1,moved.length,header.length).setValues(moved);
}

217) Drive: Create folder if missing (by name under root)

What it does: Finds folder by name or creates it.
How it works: Searches Drive folders.
When to use: Stable output folders.
Gotchas: Drive can have duplicates—this picks first match.

function ex217_getOrCreateFolderByName(name) {
  const it = DriveApp.getFoldersByName(name);
  return it.hasNext() ? it.next() : DriveApp.createFolder(name);
}

218) Drive: Save a text report file (overwrites if exists)

What it does: Creates/updates a report file in a folder.
How it works: Searches by name, updates content or creates new.
When to use: “Latest report” artifacts.
Gotchas: For big content, consider Docs instead.

function ex218_upsertTextFile(folderId, fileName, content) {
  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFilesByName(fileName);
  if (files.hasNext()) {
    files.next().setContent(content);
  } else {
    folder.createFile(fileName, content, MimeType.PLAIN_TEXT);
  }
}

219) Drive: Copy all files from Folder A → Folder B

What it does: Duplicates all files (not subfolders).
How it works: Iterates blobs and makeCopy.
When to use: Packaging deliverables.
Gotchas: Doesn’t recurse subfolders.

function ex219_copyFolderFiles(sourceFolderId, targetFolderId) {
  const src = DriveApp.getFolderById(sourceFolderId);
  const dst = DriveApp.getFolderById(targetFolderId);
  const it = src.getFiles();
  while (it.hasNext()) it.next().makeCopy(dst);
}

220) Drive: Rename files with prefix

What it does: Adds a prefix to every file name in a folder.
How it works: Loops files and sets new names.
When to use: Standardizing exports (e.g., “ClientX_”).
Gotchas: May create very long names.

function ex220_prefixFileNames(folderId, prefix) {
  const folder = DriveApp.getFolderById(folderId);
  const it = folder.getFiles();
  while (it.hasNext()) {
    const f = it.next();
    if (!f.getName().startsWith(prefix)) f.setName(prefix + f.getName());
  }
}

221) Docs: Create doc from a simple template string

What it does: Generates a doc with heading + sections.
How it works: Uses DocumentApp.create and appends paragraphs.
When to use: Auto reports.
Gotchas: Styling is basic unless you enhance.

function ex221_createSimpleReportDoc() {
  const doc = DocumentApp.create('Simple Report ' + Date.now());
  const b = doc.getBody();
  b.appendParagraph('Report').setHeading(DocumentApp.ParagraphHeading.HEADING1);
  b.appendParagraph('Generated: ' + new Date());
  b.appendParagraph('Summary:').setHeading(DocumentApp.ParagraphHeading.HEADING2);
  b.appendParagraph('Add details here...');
  doc.saveAndClose();
  Logger.log(doc.getUrl());
}

222) Docs: Replace placeholders in active doc

What it does: Replaces tokens like {{DATE}} and {{NAME}}.
How it works: replaceText across body.
When to use: Mail-merge style docs.
Gotchas: replaceText uses regex—keep placeholders simple.

function ex222_replacePlaceholdersInActiveDoc() {
  const doc = DocumentApp.getActiveDocument();
  const body = doc.getBody();
  body.replaceText('{{DATE}}', new Date().toDateString());
  body.replaceText('{{NAME}}', Session.getActiveUser().getEmail());
  doc.saveAndClose();
}

223) Docs: Turn lines into bullet list

What it does: Converts each line of text into bullets.
How it works: Splits text and appends bullet paragraphs.
When to use: Transforming imported lists.
Gotchas: Creates a new doc.

function ex223_linesToBullets() {
  const lines = 'One\nTwo\nThree'.split('\n');
  const doc = DocumentApp.create('Bullets ' + Date.now());
  const b = doc.getBody();
  lines.forEach(t => b.appendListItem(t).setGlyphType(DocumentApp.GlyphType.BULLET));
  Logger.log(doc.getUrl());
}

224) Slides: Create a slide deck from an array of sections

What it does: Builds a deck with title + body bullets for each section.
How it works: Adds slides and fills placeholders.
When to use: Fast outline-to-deck generation.
Gotchas: Placeholder types depend on layout.

function ex224_deckFromSections() {
  const sections = [
    {title:'Intro', bullets:['Goal','Context']},
    {title:'Plan', bullets:['Step 1','Step 2','Step 3']},
  ];
  const pres = SlidesApp.create('Sections Deck ' + Date.now());
  pres.getSlides()[0].remove();
  sections.forEach(s => {
    const slide = pres.appendSlide(SlidesApp.PredefinedLayout.TITLE_AND_BODY);
    slide.getPlaceholder(SlidesApp.PlaceholderType.TITLE).asShape().getText().setText(s.title);
    slide.getPlaceholder(SlidesApp.PlaceholderType.BODY).asShape().getText().setText('• ' + s.bullets.join('\n• '));
  });
  Logger.log(pres.getUrl());
}

225) Gmail: Send email to all rows with “Send = TRUE”

What it does: Sends templated emails based on sheet rows.
How it works: Reads rows, checks a flag, sends, then marks as sent.
When to use: Simple outreach or notifications.
Gotchas: Quotas apply; test with a few rows first.

function ex225_sendEmailsFromSheet() {
  const sh = SpreadsheetApp.getActiveSheet();
  const data = sh.getDataRange().getValues();
  const header = data[0].map(String);
  const emailCol = header.indexOf('Email');
  const sendCol = header.indexOf('Send');
  const sentCol = header.indexOf('SentAt');
  if (emailCol === -1 || sendCol === -1 || sentCol === -1) throw new Error('Need columns: Email, Send, SentAt');  for (let r=1;r<data.length;r++) {
    if (data[r][sendCol] === true && !data[r][sentCol]) {
      const to = data[r][emailCol];
      GmailApp.sendEmail(to, 'Hello', 'This is an automated message.');
      sh.getRange(r+1, sentCol+1).setValue(new Date());
    }
  }
}

226) Gmail: Save attachments from matching emails to Drive

What it does: Downloads attachments from Gmail threads into a Drive folder.
How it works: Searches Gmail, loops messages, saves blobs.
When to use: Auto intake pipelines.
Gotchas: Duplicate filenames possible.

function ex226_saveAttachmentsToFolder() {
  const folderId = 'PASTE_FOLDER_ID';
  const folder = DriveApp.getFolderById(folderId);
  const threads = GmailApp.search('has:attachment newer_than:7d', 0, 10);
  threads.forEach(t => t.getMessages().forEach(m => {
    m.getAttachments().forEach(a => folder.createFile(a.copyBlob()).setName(a.getName()));
  }));
}

227) Calendar: Create a weekly recurring event

What it does: Creates a recurring meeting weekly for N weeks.
How it works: Uses recurrence rule.
When to use: Scheduling routines.
Gotchas: Recurrence APIs can vary; test start/end times.

function ex227_weeklyRecurringEvent() {
  const cal = CalendarApp.getDefaultCalendar();
  const start = new Date(Date.now() + 24*3600*1000);
  const end = new Date(start.getTime() + 30*60000);
  const rule = CalendarApp.newRecurrence().addWeeklyRule().times(8);
  cal.createEventSeries('Weekly Check-in', start, end, rule);
}

228) Calendar: Find free/busy window (simple)

What it does: Lists today’s events so you can spot free gaps.
How it works: Gets events between day start/end.
When to use: Scheduling helpers.
Gotchas: This is not true “free/busy API,” but practical.

function ex228_listTodaysEvents() {
  const cal = CalendarApp.getDefaultCalendar();
  const start = new Date(); start.setHours(0,0,0,0);
  const end = new Date(); end.setHours(23,59,59,999);
  const events = cal.getEvents(start, end);
  events.forEach(e => Logger.log(`${e.getTitle()} | ${e.getStartTime()} - ${e.getEndTime()}`));
}

229) Forms: Create a Google Form automatically

What it does: Creates a form with basic questions.
How it works: FormApp.create() then adds items.
When to use: Provisioning standard forms for teams.
Gotchas: Requires Forms permission.

function ex229_createForm() {
  const form = FormApp.create('Intake Form ' + Date.now());
  form.addTextItem().setTitle('Name').setRequired(true);
  form.addTextItem().setTitle('Email').setRequired(true);
  form.addMultipleChoiceItem().setTitle('Priority').setChoices([
    form.createChoice('Low'), form.createChoice('Medium'), form.createChoice('High')
  ]);
  Logger.log(form.getEditUrl());
}

230) Forms: Send confirmation email on form submit (installable trigger)

What it does: Emails a confirmation when a form is submitted.
How it works: Reads event response, extracts email answer.
When to use: Intake confirmations.
Gotchas: Needs an installable trigger + consistent question title.

function ex230_onFormSubmit(e) {
  const resp = e.response;
  const items = resp.getItemResponses();
  const email = items.find(ir => ir.getItem().getTitle() === 'Email')?.getResponse();
  if (email) GmailApp.sendEmail(email, 'We got your submission', 'Thanks! We received your form.');
}

231) Utilities: Store API key in Script Properties

What it does: Saves secrets in properties rather than code.
How it works: PropertiesService.getScriptProperties().
When to use: Keys/tokens/config.
Gotchas: Don’t log secrets.

function ex231_setApiKey() {
  PropertiesService.getScriptProperties().setProperty('API_KEY', 'PASTE_KEY');
}
function ex231_getApiKey() {
  return PropertiesService.getScriptProperties().getProperty('API_KEY');
}

232) Utilities: Strong lock to prevent overlapping runs

What it does: Prevents concurrent executions.
How it works: Uses LockService.getScriptLock().
When to use: Triggers/web apps shared by many users.
Gotchas: Always release in finally.

function ex232_withLockDemo() {
  const lock = LockService.getScriptLock();
  lock.waitLock(30000);
  try {
    // critical section
    Logger.log('Running safely...');
  } finally {
    lock.releaseLock();
  }
}

233) Web App: Basic API key check

What it does: Protects a route using a simple API key parameter.
How it works: Compares e.parameter.key to stored property.
When to use: Lightweight internal APIs.
Gotchas: For serious security, use OAuth/identity checks.

function ex233_doGetSecured(e) {
  const key = e.parameter.key || '';
  const expected = PropertiesService.getScriptProperties().getProperty('API_KEY');
  if (!expected || key !== expected) {
    return ContentService.createTextOutput(JSON.stringify({ok:false, error:'unauthorized'}))
      .setMimeType(ContentService.MimeType.JSON);
  }
  return ContentService.createTextOutput(JSON.stringify({ok:true, time:new Date().toISOString()}))
    .setMimeType(ContentService.MimeType.JSON);
}

234) UrlFetch: Add timeout + retry wrapper

What it does: Fetches a URL with retries.
How it works: Catches errors, retries N times.
When to use: Unreliable APIs.
Gotchas: Don’t hammer APIs; keep retries small.

function ex234_fetchWithRetry(url, attempts) {
  for (let i=1;i<=attempts;i++) {
    try {
      return UrlFetchApp.fetch(url, {muteHttpExceptions:true});
    } catch (err) {
      if (i === attempts) throw err;
      Utilities.sleep(500 * i);
    }
  }
}

235) Sheets: Export active sheet as PDF to Drive

What it does: Exports the spreadsheet as PDF and saves in Drive.
How it works: Uses export endpoint + OAuth token.
When to use: Printable report automation.
Gotchas: Exports the whole file by default unless you add sheet params.

function ex235_exportActiveSheetAsPdf() {
  const ss = SpreadsheetApp.getActive();
  const url = 'https://docs.google.com/spreadsheets/d/' + ss.getId() + '/export?format=pdf';
  const token = ScriptApp.getOAuthToken();
  const resp = UrlFetchApp.fetch(url, {headers:{Authorization:'Bearer ' + token}});
  const file = DriveApp.createFile(resp.getBlob().setName(ss.getName() + '.pdf'));
  Logger.log(file.getUrl());
}

236) Sheets: Export as PDF and email link

What it does: Creates PDF export and emails the Drive link.
How it works: Calls 235 + GmailApp.
When to use: Weekly report emails.
Gotchas: Email quotas.

function ex236_exportPdfAndEmailLink() {
  const ss = SpreadsheetApp.getActive();
  const url = 'https://docs.google.com/spreadsheets/d/' + ss.getId() + '/export?format=pdf';
  const token = ScriptApp.getOAuthToken();
  const resp = UrlFetchApp.fetch(url, {headers:{Authorization:'Bearer ' + token}});
  const pdf = DriveApp.createFile(resp.getBlob().setName(ss.getName() + '.pdf'));
  GmailApp.sendEmail(Session.getActiveUser().getEmail(), 'PDF Export Ready', 'Link: ' + pdf.getUrl());
}

237) Drive: Convert Doc to PDF and store in folder

What it does: Saves a PDF version of a Google Doc into a folder.
How it works: getAs(PDF) blob + createFile in folder.
When to use: Finalizing docs.
Gotchas: Some Docs content may render differently in PDF.

function ex237_docToPdfInFolder(docId, folderId) {
  const folder = DriveApp.getFolderById(folderId);
  const file = DriveApp.getFileById(docId);
  const pdfBlob = file.getBlob().getAs(MimeType.PDF).setName(file.getName() + '.pdf');
  const pdf = folder.createFile(pdfBlob);
  Logger.log(pdf.getUrl());
}

238) Sheets: Import JSON from URL into sheet

What it does: Fetches JSON array and writes to sheet (keys as headers).
How it works: UrlFetch + parse + dynamic headers.
When to use: Simple API-to-sheet pipelines.
Gotchas: JSON shape must be consistent.

function ex238_importJsonToSheet() {
  const url = 'https://httpbin.org/json';
  const resp = UrlFetchApp.fetch(url);
  const obj = JSON.parse(resp.getContentText());  // Example expects obj.slideshow or adapt for your API
  const sh = SpreadsheetApp.getActiveSheet();
  sh.clearContents();
  sh.getRange('A1').setValue(JSON.stringify(obj, null, 2));
}

239) Sheets: Create a “Run History” log per execution

What it does: Stores run metadata each time it runs.
How it works: Appends timestamp + runtime info to a sheet.
When to use: Production monitoring.
Gotchas: Keep it lightweight.

function ex239_logRunHistory(name) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Run History') || ss.insertSheet('Run History');
  if (sh.getLastRow() === 0) sh.appendRow(['Time','User','Job']);
  sh.appendRow([new Date(), Session.getActiveUser().getEmail(), name]);
}

240) Sheets: Quick “Reset input area” tool

What it does: Clears only an input range (not whole sheet).
How it works: Calls clearContent() on a defined range.
When to use: Templates with protected formulas elsewhere.
Gotchas: Make sure range matches your design.

function ex240_clearInputArea() {
  const sh = SpreadsheetApp.getActiveSheet();
  sh.getRange('B2:E100').clearContent();
}

241) Drive: Share a folder with an editor

What it does: Adds an editor to a folder.
How it works: addEditor(email) on folder.
When to use: Provisioning client/team access.
Gotchas: Domain policies may restrict external sharing.

function ex241_shareFolderEditor(folderId, email) {
  DriveApp.getFolderById(folderId).addEditor(email);
}

242) Gmail: Auto-reply to starred emails (basic)

What it does: Replies to starred inbox threads with a canned response.
How it works: Searches Gmail, replies to last message.
When to use: Simple acknowledgment workflows.
Gotchas: Be careful—can spam if misused.

function ex242_replyToStarred() {
  const threads = GmailApp.search('is:starred in:inbox', 0, 10);
  threads.forEach(t => {
    const msg = t.getMessages().pop();
    msg.reply('Thanks — I saw this and will follow up soon.');
    t.unstar();
  });
}

243) Calendar: Create event from sheet row

What it does: Turns a row into a calendar event.
How it works: Reads values, parses date/time, creates event.
When to use: Scheduling from trackers.
Gotchas: Date parsing needs consistent format.

function ex243_createEventFromActiveRow() {
  const sh = SpreadsheetApp.getActiveSheet();
  const row = sh.getActiveRange().getRow();
  const [title, startStr, endStr] = sh.getRange(row, 1, 1, 3).getValues()[0];
  const start = new Date(startStr);
  const end = new Date(endStr);
  CalendarApp.getDefaultCalendar().createEvent(String(title), start, end);
}

244) Sheets: Auto-number rows with a prefix

What it does: Writes IDs like TASK-0001, TASK-0002…
How it works: Loops rows and sets formatted string.
When to use: Trackers needing stable IDs.
Gotchas: Re-running will overwrite.

function ex244_numberRowsWithPrefix() {
  const sh = SpreadsheetApp.getActiveSheet();
  const last = sh.getLastRow();
  const out = [];
  for (let i=2;i<=last;i++) out.push([`TASK-${String(i-1).padStart(4,'0')}`]);
  if (out.length) sh.getRange(2,1,out.length,1).setValues(out);
}

245) Sheets: Convert range to “values only” (freeze)

What it does: Replaces formulas with their current values.
How it works: copyTo with contentsOnly.
When to use: Snapshot exports.
Gotchas: Irreversible without undo.

function ex245_freezeSelectionValues() {
  const r = SpreadsheetApp.getActiveRange();
  r.copyTo(r, {contentsOnly:true});
}

246) Sheets: Create a custom function =UUID()

What it does: Returns a new UUID in a cell formula.
How it works: Custom functions can return values.
When to use: Quick unique IDs.
Gotchas: Custom functions may recalc and change.

function UUID() {
  return Utilities.getUuid();
}

247) Sheets: Custom function =TITLECASE(text)

What it does: Converts input text to Title Case.
How it works: String transform.
When to use: Cleanup inside formulas.
Gotchas: Locale-specific name casing still tricky.

function TITLECASE(text) {
  return String(text || '').toLowerCase().replace(/\b\w/g, c => c.toUpperCase());
}

248) Drive: Find duplicate files by name inside a folder

What it does: Logs file names that appear more than once.
How it works: Counts names while iterating.
When to use: Cleanup and audits.
Gotchas: Drive allows duplicates; this only reports.

function ex248_findDuplicateNamesInFolder() {
  const folderId = 'PASTE_FOLDER_ID';
  const folder = DriveApp.getFolderById(folderId);
  const it = folder.getFiles();
  const counts = {};
  while (it.hasNext()) {
    const name = it.next().getName();
    counts[name] = (counts[name] || 0) + 1;
  }
  Object.entries(counts).filter(([,c]) => c > 1).forEach(([n,c]) => Logger.log(`${n} x${c}`));
}

249) Utilities: Create an ISO timestamp string

What it does: Returns ISO timestamp like 2026-03-03T16:05:00Z.
How it works: Uses toISOString().
When to use: Logging, API payloads.
Gotchas: ISO is UTC by default.

function ex249_isoNow() {
  return new Date().toISOString();
}

250) Utilities: Measure runtime of a function

What it does: Times how long a function takes and logs milliseconds.
How it works: Date.now() delta.
When to use: Performance tuning.
Gotchas: Script execution quotas still apply.

function ex250_timedRun() {
  const start = Date.now();
  // do work here
  Utilities.sleep(200);
  const ms = Date.now() - start;
  Logger.log('Runtime ms: ' + ms);
}