Google Sheets: AI loops without column breakage

Quick automations die when someone inserts a column, renames a header, or sorts the sheet. Position-based writes hit the wrong cells. Formulas spill and you lose a weekend. However, the fix is simple: contracts over columns and ID-based upserts.

Minimal column set for a resilient loop

Create these headers. Names must stay the consistent.

record_uuid
source_fields (comma-separated list of the inputs you send to AI, or keep normal inputs in their own columns)
ai_raw_json
normalized_title
category
tags
confidence
schema_version
model_version
ai_status
ai_error
input_hash
last_processed_at

Add or reorder any other columns as you like. The loop will not care.

The JSON contract

Save this in your project notes and in a hidden tab for easy reference.

{
  "name": "enrichment_v1",
  "required": ["normalized_title", "category", "confidence"],
  "properties": {
    "normalized_title": {"type": "string"},
    "category": {"type": "string"},
    "tags": {"type": "array", "items": {"type": "string"}},
    "confidence": {"type": "number"}
  }
}

Apps Script

Paste this into Extensions -> Apps Script and update CONFIG to match your sheet.

/* === CONFIG === */
const CONFIG = {
  sheetName: 'Data',                 // your tab name
  schemaVersion: 'v1',
  modelName: 'gpt-4o-mini',          // or your provider/model string
  maxPerRun: 200,                    // batch size to avoid quotas
  inputFields: ['title', 'description'], // columns you send to AI
  // Name of the headers to write back into
  outputMap: {
    normalized_title: 'normalized_title',
    category: 'category',
    tags: 'tags',            // will join array with commas
    confidence: 'confidence'
  }
};

/* === MENU === */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('AI Enrich')
    .addItem('Process changed rows', 'processChangedRows')
    .addItem('Force reprocess selected', 'processSelection')
    .addToUi();
}

/* === CORE === */
function processChangedRows() {
  const sh = getSheet();
  const {rows, header, colIndex} = readRows(sh);
  let processed = 0;

  for (const r of rows) {
    if (processed >= CONFIG.maxPerRun) break;
    const recordId = r[header.record_uuid] || setUuid(sh, r.row, colIndex.record_uuid);
    const payload = pickInputs(r, header);
    const hash = sha256(JSON.stringify(payload));

    if (r[header.input_hash] === hash && r[header.ai_status] === 'done') continue;

    try {
      const ai = callAI(payload);                       // returns JS object
      validate(ai);                                     // throws if missing required keys
      writeCell(sh, r.row, colIndex.ai_raw_json, JSON.stringify(ai));
      mapToColumns(sh, r.row, ai, colIndex);
      writeCell(sh, r.row, colIndex.schema_version, CONFIG.schemaVersion);
      writeCell(sh, r.row, colIndex.model_version, CONFIG.modelName);
      writeCell(sh, r.row, colIndex.ai_status, 'done');
      writeCell(sh, r.row, colIndex.input_hash, hash);
      writeCell(sh, r.row, colIndex.last_processed_at, new Date());
    } catch (e) {
      writeCell(sh, r.row, colIndex.ai_status, 'error');
      writeCell(sh, r.row, colIndex.ai_error, String(e).slice(0, 500));
    }
    processed++;
    Utilities.sleep(150); // light backoff
  }
}

function processSelection() {
  const sh = getSheet();
  const range = sh.getActiveRange();
  if (!range) return;
  const {header, colIndex} = readRows(sh, true); // header only
  const startRow = range.getRow();
  const endRow = startRow + range.getNumRows() - 1;

  for (let row = startRow; row <= endRow; row++) {
    const rowObj = rowToObject(sh, row, header);
    const recordId = rowObj.record_uuid || setUuid(sh, row, colIndex.record_uuid);
    const payload = pickInputs(rowObj, header);
    const hash = sha256(JSON.stringify(payload));

    try {
      const ai = callAI(payload);
      validate(ai);
      writeCell(sh, row, colIndex.ai_raw_json, JSON.stringify(ai));
      mapToColumns(sh, row, ai, colIndex);
      writeCell(sh, row, colIndex.schema_version, CONFIG.schemaVersion);
      writeCell(sh, row, colIndex.model_version, CONFIG.modelName);
      writeCell(sh, row, colIndex.ai_status, 'done');
      writeCell(sh, row, colIndex.input_hash, hash);
      writeCell(sh, row, colIndex.last_processed_at, new Date());
    } catch (e) {
      writeCell(sh, row, colIndex.ai_status, 'error');
      writeCell(sh, row, colIndex.ai_error, String(e).slice(0, 500));
    }
  }
}

/* === HELPERS === */
function getSheet() {
  const sh = SpreadsheetApp.getActive().getSheetByName(CONFIG.sheetName);
  if (!sh) throw new Error(`Sheet "${CONFIG.sheetName}" not found`);
  return sh;
}

function readRows(sh, headerOnly=false) {
  const values = sh.getDataRange().getValues();
  if (values.length < 2) throw new Error('No data rows');
  const headerRow = values[0].map(h => String(h).trim());
  const header = {};
  const colIndex = {};
  headerRow.forEach((name, i) => {
    header[name] = i;
    colIndex[name] = i + 1; // 1-based for setValue
  });

  ['record_uuid','ai_raw_json','schema_version','model_version','ai_status','ai_error','input_hash','last_processed_at']
    .forEach(h => { if (!(h in header)) throw new Error(`Missing header: ${h}`); });

  if (headerOnly) return {header, colIndex};

  const rows = [];
  for (let i = 1; i < values.length; i++) {
    const rowArr = values[i];
    const obj = {};
    headerRow.forEach((name, idx) => obj[name] = rowArr[idx]);
    obj.row = i + 1;
    rows.push(obj);
  }
  return {rows, header, colIndex};
}

function rowToObject(sh, row, header) {
  const rowArr = sh.getRange(row, 1, 1, Object.keys(header).length).getValues()[0];
  const obj = {};
  Object.keys(header).forEach(h => obj[h] = rowArr[header[h]]);
  obj.row = row;
  return obj;
}

function setUuid(sh, row, col) {
  const id = Utilities.getUuid();
  sh.getRange(row, col).setValue(id);
  return id;
}

function pickInputs(r, header) {
  const input = {};
  CONFIG.inputFields.forEach(f => {
    if (!(f in header)) throw new Error(`Missing input field: ${f}`);
    input[f] = r[header[f]];
  });
  return input;
}

function mapToColumns(sh, row, ai, colIndex) {
  Object.entries(CONFIG.outputMap).forEach(([jsonKey, headerName]) => {
    const col = colIndex[headerName];
    if (!col) return; // skip unmapped columns
    const val = Array.isArray(ai[jsonKey]) ? ai[jsonKey].join(', ') : ai[jsonKey];
    writeCell(sh, row, col, val);
  });
}

function writeCell(sh, row, col, val) {
  sh.getRange(row, col).setValue(val);
}

function validate(ai) {
  const req = ['normalized_title','category','confidence'];
  for (const k of req) {
    if (!(k in ai)) throw new Error(`Schema validation failed. Missing ${k}`);
  }
}

function sha256(s) {
  const bytes = Utilities.newBlob(s).getBytes();
  const hash = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, bytes);
  return hash.map(b => (b + 256) % 256).map(n => n.toString(16).padStart(2, '0')).join('');
}

/* === AI CALL (replace with your provider) === */
function callAI(payload) {
  // Example with a generic endpoint that enforces a JSON schema server-side.
  const url = 'https://api.your-llm.com/v1/enrich';
  const body = {
    system: 'Return JSON only that matches enrichment_v1.',
    user: payload,
    schema: {
      name: 'enrichment_v1',
      strict: true,
      properties: {
        normalized_title: {type:'string'},
        category: {type:'string'},
        tags: {type:'array', items:{type:'string'}},
        confidence: {type:'number'}
      },
      required: ['normalized_title','category','confidence']
    }
  };

  // Replace headers and auth for your provider.
  const res = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(body),
    muteHttpExceptions: true,
    headers: { Authorization: 'Bearer YOUR_API_KEY' }
  });

  if (res.getResponseCode() >= 300) {
    throw new Error(`AI request failed: ${res.getResponseCode()} ${res.getContentText()}`);
  }
  let out = JSON.parse(res.getContentText());
  // Ensure we return a plain object with expected keys.
  return {
    normalized_title: out.normalized_title,
    category: out.category,
    tags: out.tags || [],
    confidence: out.confidence
  };
}

What this script does

Looks up columns by header name each run.
Generates a UUID if missing.
Hashes the input fields and skips rows that have not changed.
Calls your AI with a schema.
Writes raw JSON into ai_raw_json.
Maps to typed columns.
Tracks versions, status, error, and timestamps.

Create a time-driven trigger (Edit -> Current project’s triggers) to run processChangedRows every 10 or 15 minutes, or run it from the custom menu.

Batching and recovery

Keep maxPerRun modest to respect quotas.
If anything fails, the row keeps ai_status = error with a short reason in ai_error. Filter that column to re-run only the broken rows.
For bulk replays, clear input_hash for the rows you want to force.

Guardrails for Google Sheets

Avoid row numbers for targeting. Always upsert by record_uuid.
Keep formulas out of the AI write-back columns. Use values only.
Hide ai_raw_json from casual users to reduce noise.
If you change the JSON contract, bump schema_version and roll out in one sheet copy before you touch production data.

Chief Digital Officer & Author | Bridging IT, Marketing & Business