You imported a CSV of support notes. One row started with =HYPERLINK(...) (or just =1+1, or a phone number typed as +15551212). Sheets happily evaluated it. Now your Log tab has a live formula, a broken #REF!, or worse — a clickable link someone else planted. escapeLeadingFormula(value) returns a Sheets-safe form: if a string starts with =, +, -, @, or a tab, it prefixes a single quote so Sheets stores plain text. Non-strings pass through unchanged.
When to use
- Writing user / Form / CSV / API text into cells with
setValue/setValues - Building Log / Status / Notes columns from untrusted input
- Any import path where a leading
=would become a live formula
How to use this snippet
/**
* Make a value safe to write into a Sheets cell without becoming a formula.
* Strings starting with = + - @ or a tab get a leading apostrophe.
* null/undefined and non-strings (numbers, Dates, booleans) pass through.
*
* @param {*} value
* @return {*}
*/
function escapeLeadingFormula(value) {
if (value === null || value === undefined) return value;
if (typeof value !== 'string') return value;
// Sheets formula / signed-number prefixes. Tab is a known CSV-injection vector.
if (/^[=+\-@\t]/.test(value)) return "'" + value;
return value;
}
Example
function writeImportNotes(sheet, rows) {
// rows: [{ note: '=1+1' }, { note: '+15551212' }, { note: 'all good' }]
var block = rows.map(function (r) {
return [escapeLeadingFormula(r.note)];
});
sheet.getRange(2, 3, block.length, 1).setValues(block);
// Cells show =1+1 and +15551212 as text. No formulas. No auto-number coercion.
}
// Map a whole object of string fields before setValues
function escapeRowStrings_(row, keys) {
var out = {};
Object.keys(row).forEach(function (k) { out[k] = row[k]; });
(keys || Object.keys(row)).forEach(function (k) {
out[k] = escapeLeadingFormula(row[k]);
});
return out;
}
Tips:
- The leading
'is a Sheets display marker — the cell shows the text without the quote, andgetValue()returns the string without it.getDisplayValue()matches what you see. - Real numbers should stay numbers. Pass
Number(x)(or leave a numeric type) when you want math; only escape strings you intend as text. - Escaping
+/-means phone numbers and signed amounts typed as strings stay text. If you need a real number, coerce before writing. - Pair this with an allowlist of columns on import: escape the free-text ones, leave formula / numeric columns alone.
- This is defense in depth for your writes. It does not stop a human from typing a formula into the cell later.
Tip: NitroGAS Co-Pilot can wrap an existing setValues import path so every free-text column runs through escapeLeadingFormula before it hits the sheet.
Happy Coding!
