Generate realistic test data for your scripts

Testing a Northwind automation against an empty sheet tells you nothing. You need rows that look like the real thing — varied industries, plausible email addresses, a spread of retainer values — so the script you are building has something believable to chew on. Typing twenty fake clients by hand is dull, and the data always ends up too tidy to expose the edge cases that matter.

This script asks Claude to invent the rows for you. You tell it how many records you want, it returns a structured JSON array, and the script drops that straight into a test spreadsheet. The result is throwaway data that behaves like production data — enough variety to catch the bugs a row of “Test Test / [email protected]” never would.

What you’ll need

• A throwaway Google Sheet to hold the generated rows. The first tab needs a header row matching the schema: Name, Email, Contact, Monthly retainer, Status.
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.
• Nothing else — the script writes directly into the existing tab.

The script

// The throwaway spreadsheet that holds generated test rows.
const TEST_CLIENTS_SHEET_ID = '1abcTestClientsId';

// Default number of rows to generate when none is passed in.
const DEFAULT_ROW_COUNT = 20;

// The five columns each generated row maps onto, in order.
const SCHEMA_FIELDS = ['name', 'email', 'contact', 'monthlyRetainer', 'status'];

/**
 * Asks Claude for a batch of realistic Northwind client rows and writes
 * them into the test spreadsheet, starting at row 2.
 *
 * @param {number} n How many rows to generate. Defaults to DEFAULT_ROW_COUNT.
 */
function generateClients(n = DEFAULT_ROW_COUNT) {
  // 1. Build a prompt that pins the output to a strict JSON schema.
  //    A fixed shape is what makes the reply safe to parse.
  const prompt =
    `Generate ${n} realistic Northwind client rows as a JSON array. ` +
    'Return ONLY the array — no prose, no markdown. ' +
    'Each object: {name, email, contact, monthlyRetainer, status}. ' +
    'Use varied industries, realistic email domains, retainers between ' +
    '500 and 8000, and a status of "active", "paused", or "churned".';

  // 2. Sonnet handles the generation; strip any code fence, then parse.
  const reply = callClaude(prompt, 'claude-sonnet-4-6', 3000);
  const rows = JSON.parse(stripFences(reply));

  if (!rows.length) {
    Logger.log('Claude returned no rows — nothing to write.');
    return;
  }

  // 3. Map each object onto the column order the sheet expects.
  const values = rows.map((r) => SCHEMA_FIELDS.map((field) => r[field]));

  // 4. Write the whole batch in one call, below the header row.
  const sheet = SpreadsheetApp.openById(TEST_CLIENTS_SHEET_ID).getSheets()[0];
  sheet.getRange(2, 1, values.length, SCHEMA_FIELDS.length).setValues(values);
  Logger.log('Wrote ' + values.length + ' test rows.');
}

/**
 * Claude occasionally wraps JSON in a ```json code fence. Strip it so
 * JSON.parse never chokes on the markdown.
 */
function stripFences(text) {
  return text.replace(/```(?:json)?/g, '').trim();
}

/**
 * Minimal Anthropic API call. The key lives in Script Properties — it
 * is never pasted into the code.
 */
function callClaude(prompt, model = 'claude-haiku-4-5-20251001', maxTokens = 400) {
  const key = PropertiesService.getScriptProperties()
    .getProperty('ANTHROPIC_API_KEY');
  const res = UrlFetchApp.fetch('https://api.anthropic.com/v1/messages', {
    method: 'post',
    contentType: 'application/json',
    headers: { 'x-api-key': key, 'anthropic-version': '2023-06-01' },
    payload: JSON.stringify({
      model,
      max_tokens: maxTokens,
      messages: [{ role: 'user', content: prompt }],
    }),
    muteHttpExceptions: true,
  });
  return JSON.parse(res.getContentText()).content[0].text.trim();
}

How it works

1. generateClients takes a row count — 20 by default — and builds a prompt that asks for a JSON array with a fixed schema and sensible value ranges.
2. It calls Claude Sonnet, which produces more varied, believable data than a smaller model. stripFences removes any code fence, then JSON.parse turns the reply into real objects.
3. If the reply is empty, it logs a message and stops rather than writing an empty range.
4. It maps each object onto the column order in SCHEMA_FIELDS, so the field order never has to match the order Claude happened to use.
5. It writes the whole batch in a single setValues call, starting at row 2 so the header row is left intact.

Example run

Call generateClients(4) and the test sheet fills with rows like these:

Name	Email	Contact	Monthly retainer	Status
Harbour & Vine Bakery	[email protected]	Priya Mistry	1200	active
Tidewater Logistics	[email protected]	Sam Okonkwo	4800	active
Aster Dental Care	[email protected]	Dr Lena Frost	950	paused
Pinewood Roofing Ltd	[email protected]	Tom Beckett	2300	churned

The industries differ, the retainers span a useful range, and there is a churned client in the mix — exactly the kind of spread that flags bugs a uniform test set would hide.

Run it

This is an on-demand job you run while building or debugging another script:

1. In the Apps Script editor, open the function picker, select generateClients and click Run.
2. Approve the authorisation prompt the first time.
3. To generate a different number of rows, call it from another function — for example generateClients(50) — since the Run button always uses the default.

Watch out for

• This data is invented, not anonymised. Never treat a generated email address as safe to mail — the domains are plausible but may belong to real people.
• The script writes from row 2 down and does not clear first. Run it twice and the second batch overwrites the first only where the ranges overlap; clear the sheet by hand if you want a clean slate.
• Large batches need headroom. If you raise n well past 50, raise the max_tokens argument too, or Claude will return a truncated array that fails to parse.
• Generated values follow the ranges you ask for. If your real data has quirks — negative balances, missing emails, oddly long names — spell those out in the prompt, or your tests will miss those cases.