Build your own WIP tracker
with tools you already have

// ── CONFIG ──────────────────────────────────────────────
// Paste your Google Sheet ID here (from the URL)
const SHEET_ID = 'YOUR_SHEET_ID_HERE';

// The tab name inside your Sheet
const SHEET_TAB = 'Sheet1';

// How far back to look for emails on first run (adjust if needed)
const DAYS_BACK = 1;
// ────────────────────────────────────────────────────────

function processAO3Updates() {
  const sheet = SpreadsheetApp
    .openById(SHEET_ID)
    .getSheetByName(SHEET_TAB);

  // Search Gmail for AO3 update emails from the last DAYS_BACK days
  const cutoff = new Date();
  cutoff.setDate(cutoff.getDate() - DAYS_BACK);
  const dateStr = Utilities.formatDate(cutoff, 'UTC', 'yyyy/MM/dd');

  const query = `from:do-not-reply@archiveofourown.org after:${dateStr} subject:"posted Chapter"`;
  const threads = GmailApp.search(query);

  if (threads.length === 0) return;

  // Load existing story titles to avoid duplicate rows
  const data = sheet.getDataRange().getValues();
  const existing = {};
  for (let i = 1; i < data.length; i++) {
    if (data[i][0]) existing[data[i][0].toLowerCase()] = i + 1;
  }

  threads.forEach(thread => {
    thread.getMessages().forEach(msg => {
      const subject = msg.getSubject();

      // Parse chapter number and story title from subject line
      // Expected format: [AO3] AuthorName posted Chapter X of Story Title
      const match = subject.match(/posted Chapter (\d+) of (.+)$/i);
      if (!match) return;

      const chapterNum = parseInt(match[1], 10);
      const title = match[2].trim();
      const date = msg.getDate();
      const titleKey = title.toLowerCase();

      if (existing[titleKey]) {
        // Story already exists — update chapter and date
        const row = existing[titleKey];
        const currentChapter = sheet.getRange(row, 2).getValue();
        if (chapterNum > currentChapter) {
          sheet.getRange(row, 2).setValue(chapterNum);
          sheet.getRange(row, 3).setValue(date);
        }
      } else {
        // New story — add a row
        sheet.appendRow([title, chapterNum, date, 0, '', '']);
        existing[titleKey] = sheet.getLastRow();
      }
    });
  });
}