Claude Code Spreadsheet Automation: CSV, Google Sheets API, and Apps Script

Spreadsheet automation looks small until the numbers are wrong. Sales rows, lead forms, ad spend, invoices, and support queues often pass through Excel or Google Sheets before anyone notices a broken import. Claude Code helps here not because it can “think in spreadsheets,” but because it can read a repository, write a focused script, explain the diff, and leave verification evidence.

This guide turns that into a practical workflow. We will start with a local CSV summary, then append leads to Google Sheets with the Sheets API, then use Apps Script to classify sales and inquiry rows. The examples are intentionally plain Node.js and Apps Script so a beginner can copy them into a small project before asking Claude Code to adapt them.

Use this with the Claude Code productivity tips and the verification receipt workflow. For official references, keep the Claude Code docs, Claude Code CLI usage, Google Sheets API Node.js quickstart, Apps Script Sheets guide, and SheetJS docs open while adapting the code.

Design the Data Boundary First

The first decision is not which library to use. It is which table is the source of truth. Spreadsheet automation becomes fragile when the same value can be edited in a CSV export, a Google Sheet, a CRM, and an accounting system. Before asking Claude Code to implement anything, name the input, the normalized middle layer, and the human-facing report.

Layer	Purpose	Example	What to ask Claude Code to build
Raw	Untouched input data	form submissions, payment CSVs, ad exports	import, validation, error rows
Clean	Typed and normalized data	dates, amounts, status names	normalization, dedupe, schema checks
Report	Human-facing output	monthly revenue, lead priority, KPI tables	summaries, chart CSVs, notifications

The common beginner mistake is to write API data directly into the report tab. Report tabs have colors, formulas, manual notes, frozen columns, merged cells, and layouts for humans. Machines prefer stable headers and append-only rows. Keep API writes in Raw or Clean, then let formulas, pivot tables, Looker Studio, or a separate script produce the report.

Example 1: Summarize Sales CSV by Month

Start with a workflow that needs no Google credentials. This gives Claude Code a reproducible baseline and gives reviewers a file they can run locally.

Create data/sales.csv.

date,channel,product,amount,status
2026-05-01,organic,Claude Code Cheatsheet,0,won
2026-05-02,gumroad,Prompt Template Pack,2980,won
2026-05-08,consultation,Team Workshop,120000,won
2026-05-11,gumroad,Prompt Template Pack,2980,refunded
2026-06-01,organic,Claude Code Cheatsheet,0,won
2026-06-02,consultation,Implementation Review,80000,won

Save this as scripts/summarize-sales.mjs.

import { mkdir, readFile, writeFile } from "node:fs/promises";
import path from "node:path";

const inputPath = process.argv[2] ?? "data/sales.csv";
const outputPath = process.argv[3] ?? "out/monthly-summary.csv";

function parseCsvLine(line) {
  const cells = [];
  let current = "";
  let inQuotes = false;

  for (let index = 0; index < line.length; index += 1) {
    const char = line[index];
    const next = line[index + 1];

    if (char === '"' && inQuotes && next === '"') {
      current += '"';
      index += 1;
      continue;
    }

    if (char === '"') {
      inQuotes = !inQuotes;
      continue;
    }

    if (char === "," && !inQuotes) {
      cells.push(current.trim());
      current = "";
      continue;
    }

    current += char;
  }

  cells.push(current.trim());
  return cells;
}

function parseCsv(source) {
  const lines = source.trim().split(/\r?\n/).filter(Boolean);
  const headers = parseCsvLine(lines[0]);

  return lines.slice(1).map((line) => {
    const cells = parseCsvLine(line);
    return Object.fromEntries(headers.map((header, index) => [header, cells[index] ?? ""]));
  });
}

function toMonth(dateValue) {
  const date = new Date(`${dateValue}T00:00:00Z`);
  if (Number.isNaN(date.getTime())) {
    throw new Error(`Invalid date: ${dateValue}`);
  }
  return dateValue.slice(0, 7);
}

const rows = parseCsv(await readFile(inputPath, "utf8"));
const summary = new Map();

for (const row of rows) {
  if (row.status !== "won") continue;

  const amount = Number(row.amount);
  if (!Number.isFinite(amount)) {
    throw new Error(`Invalid amount: ${JSON.stringify(row)}`);
  }

  const key = `${toMonth(row.date)},${row.channel}`;
  const current = summary.get(key) ?? { month: toMonth(row.date), channel: row.channel, deals: 0, revenue: 0 };
  current.deals += 1;
  current.revenue += amount;
  summary.set(key, current);
}

const output = [
  "month,channel,deals,revenue",
  ...[...summary.values()]
    .sort((a, b) => `${a.month}:${a.channel}`.localeCompare(`${b.month}:${b.channel}`))
    .map((row) => `${row.month},${row.channel},${row.deals},${row.revenue}`),
].join("\n");

await mkdir(path.dirname(outputPath), { recursive: true });
await writeFile(outputPath, `${output}\n`, "utf8");

console.log(`Wrote ${outputPath} (${summary.size} groups)`);

Run it.

mkdir -p data out scripts
node scripts/summarize-sales.mjs data/sales.csv out/monthly-summary.csv
cat out/monthly-summary.csv

The important behavior is that bad data fails loudly. A blank amount, malformed date, or unexpected status should not silently become zero revenue. Once this script works, ask Claude Code to add row numbers for validation errors, a rejected-rows file, or tests for refunded rows.

Example 2: Append Leads with the Google Sheets API

For a team workflow, a service account is usually easier to audit than a personal OAuth token. Enable the Sheets API in Google Cloud, create a service account key, and share the target spreadsheet with the service account email. In the sheet, create a Raw tab with headers: createdAt,source,subject,amount,status.

npm install googleapis
export GOOGLE_APPLICATION_CREDENTIALS="$PWD/service-account.json"
export SHEET_ID="your-google-sheet-id"

Create scripts/append-lead-to-sheet.mjs.

import { google } from "googleapis";

const { GOOGLE_APPLICATION_CREDENTIALS, SHEET_ID } = process.env;

if (!GOOGLE_APPLICATION_CREDENTIALS) {
  throw new Error("GOOGLE_APPLICATION_CREDENTIALS is required");
}

if (!SHEET_ID) {
  throw new Error("SHEET_ID is required");
}

const auth = new google.auth.GoogleAuth({
  keyFile: GOOGLE_APPLICATION_CREDENTIALS,
  scopes: ["https://www.googleapis.com/auth/spreadsheets"],
});

const sheets = google.sheets({ version: "v4", auth });

const source = process.argv[2] ?? "web";
const subject = process.argv[3] ?? "Claude Code consultation";
const amount = Number(process.argv[4] ?? 0);

if (!Number.isFinite(amount)) {
  throw new Error(`Invalid amount: ${process.argv[4]}`);
}

await sheets.spreadsheets.values.append({
  spreadsheetId: SHEET_ID,
  range: "Raw!A:E",
  valueInputOption: "USER_ENTERED",
  insertDataOption: "INSERT_ROWS",
  requestBody: {
    values: [[new Date().toISOString(), source, subject, amount, "new"]],
  },
});

console.log("Appended lead row");

Run it after setting credentials.

node scripts/append-lead-to-sheet.mjs newsletter "Spreadsheet automation review" 50000

When Claude Code edits this script, make it preserve the environment variable names, the target range, and the rule that credentials never enter Git. Also ask it to check .gitignore if the repository stores local secrets near the project root.

Example 3: Classify Sales and Inquiry Rows with Apps Script

Apps Script is useful when the workflow lives inside Google Workspace. It can react to form submissions, update Sheets, and send email without a separate server. It also has quotas and trigger rules, so check the official Apps Script quotas before using it for a high-volume funnel.

Paste this into the Apps Script editor and configure an installable form-submit trigger for onFormSubmit.

const SETTINGS = {
  sheetName: "Leads",
  notifyTo: "sales@example.com",
  minAmountForHighPriority: 100000,
};

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Lead Ops")
    .addItem("Rebuild lead status", "rebuildLeadStatus")
    .addToUi();
}

function onFormSubmit(event) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName(SETTINGS.sheetName) || spreadsheet.insertSheet(SETTINGS.sheetName);
  ensureHeader_(sheet);

  const values = event && event.namedValues ? event.namedValues : {};
  const company = first_(values, "Company");
  const email = first_(values, "Email");
  const plan = first_(values, "Plan");
  const budget = Number(first_(values, "Budget") || 0);
  const priority = classifyLead_(plan, budget);

  sheet.appendRow([new Date(), company, email, plan, budget, priority, "new"]);

  if (priority === "high") {
    MailApp.sendEmail({
      to: SETTINGS.notifyTo,
      subject: `High priority lead: ${company}`,
      body: `Company: ${company}\nEmail: ${email}\nPlan: ${plan}\nBudget: ${budget}`,
    });
  }
}

function rebuildLeadStatus() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SETTINGS.sheetName);
  if (!sheet) throw new Error(`Sheet not found: ${SETTINGS.sheetName}`);
  ensureHeader_(sheet);

  const values = sheet.getDataRange().getValues();
  for (let rowIndex = 1; rowIndex < values.length; rowIndex += 1) {
    const row = values[rowIndex];
    const plan = String(row[3] || "");
    const budget = Number(row[4] || 0);
    const priority = classifyLead_(plan, budget);
    sheet.getRange(rowIndex + 1, 6).setValue(priority);
  }
}

function ensureHeader_(sheet) {
  const header = ["createdAt", "company", "email", "plan", "budget", "priority", "status"];
  const current = sheet.getRange(1, 1, 1, header.length).getValues()[0];
  if (current.join("") === "") {
    sheet.getRange(1, 1, 1, header.length).setValues([header]);
    sheet.setFrozenRows(1);
  }
}

function classifyLead_(plan, budget) {
  const normalizedPlan = String(plan).toLowerCase();
  if (budget >= SETTINGS.minAmountForHighPriority || normalizedPlan.includes("team")) {
    return "high";
  }
  if (budget >= 30000) {
    return "medium";
  }
  return "low";
}

function first_(namedValues, key) {
  const value = namedValues[key];
  return Array.isArray(value) ? value[0] || "" : "";
}

Localize the field names to your form. If the form uses Japanese, Spanish, or Korean labels, pass the exact labels to Claude Code and ask it to avoid logging personal information.

Prompt Template for Claude Code

The best prompt is narrow. It tells Claude Code what it may edit, what it must not touch, and how to prove the result.

You are working on spreadsheet automation for this repository.

Goal:
- Import sales CSV rows from data/sales.csv.
- Write a monthly summary to out/monthly-summary.csv.
- Add a Google Sheets append script for the Raw tab.

Scope:
- You may edit scripts/summarize-sales.mjs and scripts/append-lead-to-sheet.mjs.
- You may add small tests or sample CSV files if needed.
- Do not edit content files, product links, analytics, or deployment settings.

Rules:
- Do not commit credentials.
- Use environment variables for SHEET_ID and GOOGLE_APPLICATION_CREDENTIALS.
- Fail loudly on invalid dates, invalid amounts, and missing required columns.
- Keep the code copy-paste runnable with Node.js 20 or later.

Verification:
- Run node --check on every script you edit.
- Run the CSV summary against data/sales.csv.
- For Google Sheets API, verify syntax locally and list the manual credential checks.
- Return changed files, commands run, output summary, and remaining risks.

Use these commands as the verification receipt.

node --check scripts/summarize-sales.mjs
node scripts/summarize-sales.mjs data/sales.csv out/monthly-summary.csv
node --check scripts/append-lead-to-sheet.mjs
git diff -- scripts/summarize-sales.mjs scripts/append-lead-to-sheet.mjs

For teams, move the durable parts into CLAUDE.md and connect them with the Claude Code permissions guide.

Real Use Cases

The first use case is monthly revenue reporting. Combine Gumroad exports, Stripe exports, manual invoices, and free PDF registrations. Count paid wins as revenue, keep free leads as lead volume, and exclude refunds. The valuable part is not the chart; it is the written rule for each status.

The second use case is lead triage. Form rows can be classified by budget, team size, plan, or existing customer domain. Keep the rules explainable. “Team plan or budget over 100,000 yen” is reviewable. “Looks promising” is not.

The third use case is article and ad KPI tracking. Store article slug, publish date, search clicks, CTA clicks, product clicks, and consultation starts in one sheet. Pair this with the Claude Code analytics implementation guide so event names and CTA ids stay consistent.

The fourth use case is pre-invoice checking. Compare delivery logs with invoice CSV rows and write only mismatches to a review sheet. Do not automate sending invoices on day one. Start by making discrepancies visible.

Pitfalls to Avoid

The biggest failure is unstable headers. If Amount, amount, and Revenue all appear, a script can pass while silently skipping rows. Ask Claude Code to list required headers and stop when they are missing.

The second failure is treating Google Sheets like a database. Sheets is excellent for collaboration and review, but not for transactional writes, locking, or large batch processing. Keep payment authority and access control in the real application database.

The third failure is leaking credentials. Service account JSON should never be pasted into prompts, issues, docs, or commits. Tell Claude Code not to read, print, or commit secrets.

The fourth failure is forgetting Apps Script triggers. Pasted code is not enough. Check the installable trigger, execution user, first authorization, mail quota, and error notification path.

The fifth failure is looking only at the final summary. Every report should show rows read, rows excluded, error count, and last updated time. Without those numbers, you cannot tell whether the automation is healthy.

CTA: Turn Scripts Into Operations

Spreadsheet automation becomes reliable when prompts, permissions, and verification are repeatable. Start with the free Claude Code cheatsheet for daily commands and review habits. For reusable prompt packs and CLAUDE.md templates, browse ClaudeCodeLab products.

If a team is using sheets for sales, leads, ads, or invoicing, define the credential boundary and review process before adding more automation. ClaudeCodeLab can help through Claude Code training and consultation.

What Happened When I Tried This

In Masa’s workflow, the biggest improvement came before the Sheets API. Freezing the CSV headers, exclusion rules, and failure behavior made the Claude Code prompt much more precise. Apps Script worked well for lead classification, but vague notification rules created too many emails. The stable approach was to treat spreadsheet integration as a Raw, Clean, Report design problem, then let Claude Code implement and verify the small pieces.