Write 00123 to a cell and Sheets stores 123. Write a 16-digit order ID and you get 1.23457E+15, and the last digits are gone for good. writeAsPlainText(range, values, opts) sets the '@' (plain text) number format on the target columns first, then writes, so leading zeros, ZIP codes, phone numbers and long IDs land exactly as you sent them.
When to use
- Importing CSV or API data with ZIP codes, SKUs, account numbers or phone numbers
- Writing IDs longer than 15 digits, which Sheets can't hold as numbers
- Any column where "looks like a number" doesn't mean "is a number"
How to use this snippet
/**
* Format target columns as plain text, then write.
* Order matters: format first, write second.
*
* @param {Range} range top-left cell (or the full block) to write to
* @param {Array[]} values
* @param {{textColumns?: number[]}} [opts] 1-based columns within the block; default all
* @return {Range} the written block
*/
function writeAsPlainText(range, values, opts) {
opts = opts || {};
var rows = values.length;
var cols = rows ? values[0].length : 0;
if (!rows || !cols) return range;
var target = range.offset(0, 0, rows, cols);
var textCols = opts.textColumns || null;
if (!textCols) {
target.setNumberFormat('@');
} else {
textCols.forEach(function (c) {
target.offset(0, c - 1, rows, 1).setNumberFormat('@');
});
}
var out = values.map(function (row) {
return row.map(function (v, i) {
var isText = !textCols || textCols.indexOf(i + 1) !== -1;
return isText && v !== null && v !== '' ? String(v) : v;
});
});
target.setValues(out);
return target;
}
Example
function importCustomers() {
var sh = SpreadsheetApp.getActive().getSheetByName('Customers');
var rows = [
['00123', '02134', 'Ana', 120],
['00456', '07030', 'Ben', 80]
];
// Customer ID and ZIP stay text; Name and Spend keep normal formats
writeAsPlainText(sh.getRange(2, 1), rows, { textColumns: [1, 2] });
}
Tips:
- Values in text columns are converted with
String()before writing. Send IDs as strings from your source. A number above 15 digits has already lost precision in JavaScript. - Text columns won't add up with
SUM. Only mark columns you never do math on. - The format sticks to the cells, so later manual edits stay text too.
Tip: NitroGAS Co-Pilot can find which columns in your import need this. Paste a few sample rows.
Happy Coding!
