Stuffing JSON or Images into a Sheet Cell Will Break It

A single cell holds at most 50,000 characters. Turn a photo or signature into a data URL (data:image/png;base64,...) and drop it into a record row, and one image alone runs tens of KB to a few MB: past the limit instantly. The save silently truncates or fails.

Why it matters

When a field-inspection app tries to store several photos plus a signature in one row and hits the wall, the whole record entered that day is lost. The error isn’t obvious, so the cause takes time to find. The cost is “a second trip to the site.”

The fix: separate the binary, keep only a path

Store metadata and a file ID in the record row. Keep the binary as a file in Drive and leave just the fileId in the cell.

// Bad: data URL straight into the record -> over 50,000 chars
record.photo = "data:image/jpeg;base64,/9j/4AAQ...";

// Good: file in Drive, only id + metadata in the cell
function savePhoto(base64, mime) {
  const blob = Utilities.newBlob(Utilities.base64Decode(base64), mime, "photo.jpg");
  const file = DriveApp.getFolderById(FOLDER_ID).createFile(blob);
  return { fileId: file.getId(), bytes: blob.getBytes().length };
}

Keep the binary (data URL) as export-only state used when rendering on screen, and never put it in the storage model.

Two pitfalls that ride along

1) Formula injection. Input starting with =, +, -, or @ is read as a formula by Sheets. Prefix a ' before saving to neutralize it.

function escapeFormula(v) {
  return /^[=+\-@]/.test(String(v)) ? "'" + v : v;
}

2) JSON serialization. JSON.stringify() does not safely restore Date, Map, Set, undefined, or NaN. Store dates as ISO strings, or declare an explicit replacer/reviver.

Deeper: Properties has the same trap

PropertiesService caps each value at 9KB. It’s a store for a cursor, small config, or an idempotency key: not a database. Any state that can exceed 9KB belongs in a Sheet, a Drive JSON blob, CacheService, or an external DB.