appendRow and one wide setValues write every column — including the ones holding =VLOOKUP(…) or =IF(…). One run and your formula columns are now hard-coded blanks. writeBlockLeavingFormulaCols(sheet, rows, dataHeaders, opts) writes object rows into only the headers you allowlist, one setValues per contiguous column run, and never touches the columns in between.
When to use
- Import / sync jobs that append rows to a sheet with calculated columns
- Sheets where teammates add formula columns you didn’t plan for
- Anywhere you currently build a full-width row array “and hope the blanks are fine”
How to use this snippet
/**
* Write object rows into ONLY the allowlisted (data) columns.
* Formula columns between them are never written, so formulas survive.
* Writes one setValues per contiguous run of columns.
*
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet
* @param {Object[]} rows e.g. [{ Email: 'a@b.com', Plan: 'Pro' }]
* @param {string[]} dataHeaders headers that are safe to write (no formulas)
* @param {{headerRow?: number, headerMap?: Object, startRow?: number}=} opts
* headerRow row holding headers (default 1)
* headerMap { 'Email': 2, ... } 1-based columns; read from the sheet if omitted
* startRow first row to write (default: first empty row after getLastRow())
* @return {{startRow: number, numRows: number, runs: Object[]}}
*/
function writeBlockLeavingFormulaCols(sheet, rows, dataHeaders, opts) {
opts = opts || {};
if (!rows || !rows.length) {
return { startRow: null, numRows: 0, runs: [] };
}
var headerRow = opts.headerRow || 1;
// 1. Header name -> column number. Read the header row once if no map was passed.
var map = opts.headerMap;
if (!map) {
map = {};
var headers = sheet.getRange(headerRow, 1, 1, sheet.getLastColumn()).getValues()[0];
headers.forEach(function (h, i) {
var key = String(h).trim();
if (key && !map[key]) map[key] = i + 1; // first match wins on duplicate headers
});
}
// 2. Resolve the allowlist to columns. A typo should fail loud, not write nothing.
var cols = dataHeaders
.filter(function (h, i) { return dataHeaders.indexOf(h) === i; }) // drop duplicates
.map(function (h) {
if (!map[h]) {
throw new Error('writeBlockLeavingFormulaCols: header not found: ' + h);
}
return { header: h, col: map[h] };
})
.sort(function (a, b) { return a.col - b.col; });
// 3. Group side-by-side columns into runs, e.g. A:C and F:G (skipping formula cols D:E).
var runs = [];
cols.forEach(function (c) {
var last = runs[runs.length - 1];
if (last && c.col === last.startCol + last.headers.length) {
last.headers.push(c.header);
} else {
runs.push({ startCol: c.col, headers: [c.header] });
}
});
// 4. Write each run as one rectangular block. Missing keys become blank cells.
var startRow = opts.startRow || Math.max(sheet.getLastRow(), headerRow) + 1;
runs.forEach(function (run) {
var values = rows.map(function (obj) {
return run.headers.map(function (h) {
return obj[h] == null ? '' : obj[h];
});
});
sheet.getRange(startRow, run.startCol, rows.length, run.headers.length).setValues(values);
});
return { startRow: startRow, numRows: rows.length, runs: runs };
}
Example
// Orders sheet: A Date | B Email | C Plan | D Price (formula) | E Tax (formula) | F Source
var ORDER_DATA_HEADERS = ['Date', 'Email', 'Plan', 'Source'];
function appendNewOrders() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Orders');
var rows = [
{ Date: new Date(), Email: 'sam@example.com', Plan: 'Pro', Source: 'Stripe' },
{ Date: new Date(), Email: 'lee@example.com', Plan: 'Basic', Source: 'Manual' }
];
var result = writeBlockLeavingFormulaCols(sheet, rows, ORDER_DATA_HEADERS);
Logger.log('Wrote %s rows from row %s in %s runs', result.numRows, result.startRow, result.runs.length);
// Two setValues calls: A:C and F. Columns D:E are never touched.
}
Tips:
- Formula columns filled down ahead of time (or an ARRAYFORMULA) push
getLastRow()down — passopts.startRowfrom a key column’s last filled row instead. - Keep the allowlist next to the sheet’s purpose (a constant at the top of the file) so a new formula column is “safe by default”: it’s not on the list, so it’s never written.
- If the formulas need to extend to new rows, use an
ARRAYFORMULAin the header row or copy the formula down after the write.
Tip: NitroGAS Co-Pilot can sketch the dataHeaders allowlist if you paste your header row and say which columns are formulas.
Happy Coding!
