Sheets cells top out around 50k characters. Dump a full err.stack, UrlFetch body, or JSON blob with setValue and the run throws mid-flight — often after the useful work already finished. truncateForCell(text, maxLen) caps the string (default 50000) and appends a clear …[truncated] suffix so Log tabs stay writable.
When to use
- Writing exceptions / stack traces to a Log sheet
- Caching API response snippets in a cell
- Any
setValue/appendRowpath that might see unbounded text
How to use this snippet
/**
* Cap a string for SpreadsheetApp setValue (~50k/cell).
* null/undefined → ''. Appends …[truncated] when clipped.
* @param {*} text
* @param {number=} maxLen default 50000
* @return {string}
*/
function truncateForCell(text, maxLen) {
if (maxLen == null || maxLen === '') maxLen = 50000;
maxLen = Number(maxLen);
if (text == null) return '';
var s = String(text);
if (!isFinite(maxLen) || maxLen <= 0) return s;
if (s.length <= maxLen) return s;
var suffix = '…[truncated]';
var keep = Math.max(0, maxLen - suffix.length);
return s.slice(0, keep) + suffix;
}
Example
function logError_(sheet, err) {
var msg = err && err.message ? err.message : String(err);
var stack = err && err.stack ? err.stack : '';
sheet.appendRow([
new Date(),
truncateForCell(msg, 2000),
truncateForCell(stack, 50000)
]);
}
function stashPayload_(cell, payload) {
cell.setValue(truncateForCell(JSON.stringify(payload)));
}
Tips:
- Keep a tighter cap (1–2k) for “message” columns humans skim; reserve 50k for dump columns.
null/undefinedbecome''— don’t stringify them into"null".- Truncation is for cells, not for Drive files — park huge payloads in Drive when you need the whole thing.
Tip: NitroGAS Co-Pilot can wrap Log / error setValue paths with truncateForCell so a chatty stack trace can’t abort the rest of the batch.
Happy Coding!
