Zerf home

3 · Apps Script, step by step

The Apps Script is plain code, no AI. It does the checks that must never fail: are the numbers valid, who receives what, and when a routine starts.

Setup, once

  1. Open your copy of the Monday report sheet > Extensions > Apps Script. The copy comes with the script.
  2. If the script is empty, paste the code at the end of this note. Save.
  3. In setupProperties, replace each PASTE_… with the ID of your own folder or file: the part of its web address after /folders/ or /d/.
  4. Run setupProperties. Accept the permissions. It saves your Drive IDs.
  5. Project settings > Script properties: add the 4 it lists as missing (the rates routine web address and token, and the emails of the demo account).
  6. Triggers > Add trigger: importReport, buildDecksAndDrafts and draftRateNotes every 15 minutes, and checkReportArrived on Mondays between 8 and 9.

What each function does

Function In plain words
setupProperties Run once. Fills in the Drive IDs and tells you what is still missing.
importReport Every 15 min. Takes the newest report from Outputs, checks every number, copies it to Validated, fills the sheet, and starts the rates review if a night is below 63%.
fireRoutine Starts a Claude routine by calling its web address with its token.
buildDecksAndDrafts Every 15 min. Takes the deck text from Outbox, checks the owner, copies the Slides template, replaces the placeholders and creates the Gmail draft.
draftRateNotes Every 15 min. Turns a rate note from Notes into a Gmail draft for the revenue manager.
checkReportArrived Mondays 8 to 9. If there is no report, a person gets an alert.

The code

/**
 * Glue between Google Workspace and the Claude Code routines.
 * Deterministic code: no AI runs here. Every check that must never fail lives here,
 * outside the agent: report validation, who receives what, and when a routine fires.
 *
 * Script properties (Project settings > Script properties):
 *   OUTPUTS_FOLDER_ID, VALIDATED_FOLDER_ID, OUTBOX_FOLDER_ID, NOTES_FOLDER_ID, PROCESSED_FOLDER_ID, CONFIG_FILE_ID (owners.json),
 *   SLIDES_TEMPLATE_ID, RATES_REVIEW_FIRE_URL, RATES_REVIEW_TOKEN,
 *   ALERT_EMAIL, REVENUE_MANAGER_EMAIL
 *
 * Installable triggers (Triggers > Add trigger):
 *   importReport            time-driven, every 15 minutes
 *   buildDecksAndDrafts     time-driven, every 15 minutes
 *   draftRateNotes          time-driven, every 15 minutes
 *   checkReportArrived      time-driven, Mondays between 8 and 9 (Apps Script picks the minute)
 *
 * First time: paste your Drive IDs into setupProperties() and run it once.
 * Then add by hand the secrets and emails it lists as missing (fire URLs, tokens, emails).
 *
 * For retakes: delete the "fired:*", "imported:*" and "rejected:*" script properties.
 */
const PROPS = PropertiesService.getScriptProperties();
const OCCUPANCY_THRESHOLD = 0.63; // mirrors config/thresholds.json (occupancy_on_books). Change both together.
const REPORT_NAME = /^report-\d{4}-\d{2}\.csv$/; // report-<year>-<week>.csv
const RATES_NOTE_NAME = /^rates-[\w-]+\.txt$/; // rates-<property_id>-<week_start>.txt
const REQUIRED_COLUMNS = ['property_id', 'week_start', 'date', 'occupancy_on_books'];

function owners() {
  return JSON.parse(DriveApp.getFileById(PROPS.getProperty('CONFIG_FILE_ID')).getBlob().getDataAsString()).owners;
}

function alert(subject, body) {
  MailApp.sendEmail(PROPS.getProperty('ALERT_EMAIL'), subject, body);
}

/** Newest report file by name (the week is in the name), or null. */
function newestReport() {
  const files = DriveApp.getFolderById(PROPS.getProperty('OUTPUTS_FOLDER_ID')).getFiles();
  let newest = null;
  while (files.hasNext()) {
    const f = files.next();
    if (REPORT_NAME.test(f.getName()) && f.getMimeType() === MimeType.CSV && (!newest || f.getName() > newest.getName())) newest = f;
  }
  return newest;
}

/** True for a real calendar date written as YYYY-MM-DD. */
function isDate(text) {
  if (!/^\d{4}-\d{2}-\d{2}$/.test(text)) return false;
  const d = new Date(text + 'T00:00:00Z');
  return !isNaN(d.getTime()) && d.toISOString().slice(0, 10) === text;
}

/** Problems in the report rows: unknown property, occupancy outside 0..1, bad dates. Empty list = valid. */
function reportProblems(rows) {
  const header = rows[0] || [];
  const missing = REQUIRED_COLUMNS.filter((name) => header.indexOf(name) < 0);
  if (missing.length) return [`missing columns: ${missing.join(', ')}`];
  if (rows.length < 2) return ['no data rows'];
  const col = (name) => header.indexOf(name);
  const known = new Set(owners().flatMap((o) => o.property_ids));
  const problems = [];
  rows.slice(1).forEach((r, i) => {
    const line = i + 2;
    const occupancy = r[col('occupancy_on_books')];
    if (!known.has(r[col('property_id')])) problems.push(`row ${line}: unknown property`);
    if (occupancy === '' || !(Number(occupancy) >= 0 && Number(occupancy) <= 1)) problems.push(`row ${line}: invalid occupancy`);
    if (!isDate(r[col('date')])) problems.push(`row ${line}: invalid date`);
    if (!isDate(r[col('week_start')])) problems.push(`row ${line}: invalid week_start`);
  });
  return problems;
}

/** The JSON next to the CSV must say the same: same dates, same occupancy. Returns the JSON file or an error text. */
function companionJson(csvFile, rows) {
  const name = csvFile.getName().replace(/\.csv$/, '.json');
  const found = DriveApp.getFolderById(PROPS.getProperty('OUTPUTS_FOLDER_ID')).getFilesByName(name);
  if (!found.hasNext()) return { error: `${name} is missing next to the CSV` };
  const file = found.next();
  let report;
  try {
    report = JSON.parse(file.getBlob().getDataAsString());
  } catch (err) {
    return { error: `${name} is not valid JSON (${err.message})` };
  }
  const header = rows[0];
  const byDate = Object.fromEntries((report.rows || []).map((r) => [r.date, r.occupancy_on_books]));
  const different = rows.slice(1).filter((r) => byDate[r[header.indexOf('date')]] !== Number(r[header.indexOf('occupancy_on_books')]));
  if (different.length || Object.keys(byDate).length !== rows.length - 1) return { error: `${name} and the CSV do not match` };
  return { file };
}

/** Validates the report outside the agent, imports it and checks the threshold. */
function importReport() {
  const file = newestReport();
  if (!file) return;
  const name = file.getName();
  if (PROPS.getProperty('imported:' + name) || PROPS.getProperty('rejected:' + name)) return;
  const rows = Utilities.parseCsv(file.getBlob().getDataAsString()).filter((r) => r.join('') !== '');
  const problems = reportProblems(rows);
  const json = problems.length ? null : companionJson(file, rows);
  if (json && json.error) problems.push(json.error);
  if (problems.length) {
    PROPS.setProperty('rejected:' + name, new Date().toISOString()); // one alert per file, not one per trigger run
    alert('Report rejected', `${name}: ${problems.slice(0, 10).join('; ')}. Nothing was imported.`);
    return;
  }
  // Only files that pass this check reach the folder the next routines read: the CSV and its JSON.
  const validated = DriveApp.getFolderById(PROPS.getProperty('VALIDATED_FOLDER_ID'));
  file.makeCopy(name, validated);
  json.file.makeCopy(json.file.getName(), validated);
  const header = rows[0];
  const col = (n) => header.indexOf(n);
  const sheet = SpreadsheetApp.getActive().getSheetByName('Report');
  sheet.clearContents();
  sheet.getRange(1, 1, rows.length, header.length).setValues(rows);
  PROPS.setProperty('imported:' + name, new Date().toISOString());

  // Forward dates below the threshold, grouped by property and week.
  const below = {};
  rows.slice(1).forEach((r) => {
    const occupancy = Number(r[col('occupancy_on_books')]);
    if (occupancy < OCCUPANCY_THRESHOLD) {
      const key = `${r[col('property_id')]}|${r[col('week_start')]}`;
      (below[key] = below[key] || []).push(`${r[col('date')]} (${Math.round(occupancy * 1000) / 10}%)`);
    }
  });
  Object.entries(below).forEach(([key, dates]) => {
    const [propertyId, weekStart] = key.split('|');
    const guard = `fired:${propertyId}:${weekStart}`;
    if (PROPS.getProperty(guard)) return;
    const text = `property_id=${propertyId}; week_start=${weekStart}; dates_below_threshold=${dates.join(', ')}`;
    if (fireRoutine(PROPS.getProperty('RATES_REVIEW_FIRE_URL'), PROPS.getProperty('RATES_REVIEW_TOKEN'), text)) {
      PROPS.setProperty(guard, new Date().toISOString()); // once per property and week
    }
  });
}

/** Starts a routine through its API trigger. Returns true if it started. Failures go to the alert email. */
function fireRoutine(url, token, text) {
  if (!url || !token) {
    alert('Routine not configured', 'A routine fire URL or token is missing in the script properties.');
    return false;
  }
  let res;
  try {
    res = UrlFetchApp.fetch(url, {
      method: 'post',
      contentType: 'application/json',
      headers: {
        Authorization: 'Bearer ' + token,
        'anthropic-version': '2023-06-01',
        'anthropic-beta': 'experimental-cc-routine-2026-04-01', // optional
      },
      payload: JSON.stringify({ text: text }),
      muteHttpExceptions: true,
    });
  } catch (err) {
    alert('Routine did not start', `The request failed: ${err.message}`);
    return false;
  }
  if (res.getResponseCode() >= 300) {
    alert('Routine did not start', `HTTP ${res.getResponseCode()}: ${res.getContentText()}`);
    return false;
  }
  return true;
}

/**
 * Turns each deck-<owner_id>.json in the outbox into a Google Slides deck and a Gmail draft.
 * The recipient comes from owners.json, never from the agent's output.
 */
function buildDecksAndDrafts() {
  const outbox = DriveApp.getFolderById(PROPS.getProperty('OUTBOX_FOLDER_ID'));
  const processed = DriveApp.getFolderById(PROPS.getProperty('PROCESSED_FOLDER_ID'));
  const byId = Object.fromEntries(owners().map((o) => [o.owner_id, o]));
  const files = outbox.getFiles();
  while (files.hasNext()) {
    const file = files.next();
    if (!/^deck-[\w-]+\.json$/.test(file.getName())) continue;
    const deck = JSON.parse(file.getBlob().getDataAsString());
    const owner = byId[deck.owner_id];
    const foreign = (deck.property_ids || []).filter((p) => !owner || !owner.property_ids.includes(p));
    // Read the text itself: block the deck if it names a hotel that belongs to another owner.
    const text = JSON.stringify(deck.placeholders || {}) + ' ' + (deck.body || '');
    const others = owners().filter((o) => o.owner_id !== deck.owner_id)
      .flatMap((o) => [...o.property_ids, ...(o.property_names || [])]);
    const named = others.filter((h) => text.includes(h));
    if (!owner || foreign.length || named.length) {
      alert('Draft blocked', `${file.getName()}: unknown owner, or hotels that are not theirs (${[...foreign, ...named].join(', ')}).`);
      file.moveTo(processed);
      continue;
    }
    const copy = DriveApp.getFileById(PROPS.getProperty('SLIDES_TEMPLATE_ID')).makeCopy(`Weekly report · ${owner.name} · ${deck.week_start}`);
    const pres = SlidesApp.openById(copy.getId());
    Object.entries(deck.placeholders).forEach(([key, text]) => pres.replaceAllText(`{{${key}}}`, text));
    pres.saveAndClose();
    GmailApp.createDraft(owner.email, deck.subject, `${deck.body}\n\n${copy.getUrl()}`);
    file.moveTo(processed);
  }
}

/** Turns each rates note (rates-*.txt) into a Gmail draft for the revenue manager. */
function draftRateNotes() {
  const notes = DriveApp.getFolderById(PROPS.getProperty('NOTES_FOLDER_ID'));
  const processed = DriveApp.getFolderById(PROPS.getProperty('PROCESSED_FOLDER_ID'));
  const files = notes.getFiles();
  while (files.hasNext()) {
    const file = files.next();
    if (!RATES_NOTE_NAME.test(file.getName())) continue;
    GmailApp.createDraft(PROPS.getProperty('REVENUE_MANAGER_EMAIL'), `Rates review: ${file.getName()}`, file.getBlob().getDataAsString());
    file.moveTo(processed);
  }
}

/** Watchdog: if Monday's report is missing in the morning, a person is told. */
function checkReportArrived() {
  const file = newestReport();
  const fresh = file && Date.now() - file.getLastUpdated().getTime() < 3 * 60 * 60 * 1000;
  if (!fresh) alert('The Monday report did not arrive', 'The 6:07 routine did not produce a new report. Check the alert note in Outputs, then press "Run now" on the routine.');
}


/**
 * One-time setup. Fills the script properties with the Drive IDs of your
 * demo folders, and lists what is still missing.
 * Secrets (fire URLs and tokens) and emails are never written in code: add them by hand.
 */
function setupProperties() {
  PROPS.setProperties({
    OUTPUTS_FOLDER_ID: 'PASTE_OUTPUTS_FOLDER_ID',
    VALIDATED_FOLDER_ID: 'PASTE_VALIDATED_FOLDER_ID',
    OUTBOX_FOLDER_ID: 'PASTE_OUTBOX_FOLDER_ID',
    NOTES_FOLDER_ID: 'PASTE_NOTES_FOLDER_ID',
    PROCESSED_FOLDER_ID: 'PASTE_PROCESSED_FOLDER_ID',
    CONFIG_FILE_ID: 'PASTE_OWNERS_JSON_FILE_ID', // owners.json inside Config
    SLIDES_TEMPLATE_ID: 'PASTE_YOUR_DECK_TEMPLATE_COPY_ID',
  });
  const needed = ['RATES_REVIEW_FIRE_URL', 'RATES_REVIEW_TOKEN', 'ALERT_EMAIL', 'REVENUE_MANAGER_EMAIL'];
  const missing = needed.filter((key) => !PROPS.getProperty(key));
  Logger.log(missing.length ? `Add these by hand in Project settings > Script properties: ${missing.join(', ')}` : 'All properties are set.');
}