Keep formula columns when the script appends rows

TL;DR — Stop appendRow and wide setValues from stomping your formula columns: write only data columns via a header map + allowlist, then check nothing got clobbered — plus a soft NitroGAS note for writeBlockLeavingFormulaCols.

Your import appends ten new orders. Column D used to say =C2*1.2. Now rows 42–51 say nothing — or worse, a hard-coded number from whatever your script put in that slot. Nobody notices until the monthly total is off.

The culprit is almost always appendRow or one wide setValues. Both write every column in the row, including the ones a human (or past you) filled with formulas. The fix isn’t “be careful” — it’s writing only the columns that are data, and leaving the rest alone.

TL;DR

appendRow([...]) and full-width setValues overwrite formula columns with whatever you pass — even ''
Keep an allowlist of data headers; everything not on it is hands-off
Map headers → columns at run time, then write one block per contiguous run of data columns
After the write, do a quick “did we clobber anything?” check on the formula columns
Soft helper name: writeBlockLeavingFormulaCols (snippet + NitroGAS catalog)

Using the Apps Script editor a lot? NitroGAS drops free themes & snippets right into script.google.com — optional Co-Pilot when you want a boost.

Why `appendRow` bites

appendRow takes one array and writes it left-to-right from column A. If your sheet is:

then appendRow([date, email, plan, '', '', source]) writes blanks over D and E in the new row. Passing null doesn’t help either. There’s no “skip this cell” value.

Wide setValues has the same problem at scale: one rectangle, every cell replaced. It’s the right tool for speed — you just need to aim it at the right columns.

If you already map columns by header, not by letter, you’re most of the way there.

What not to do

Don’t hard-code “formula columns are D and E” — someone will insert a column next week
Don’t read the formulas, then write them back with the data (it works until someone edits a formula mid-run)
Don’t write one cell at a time to dodge the problem — you’ll hit timeouts on real imports
Don’t rely on a blocklist of formula columns; new formula columns should be safe by default

Workflow

1. List the headers your script owns (the allowlist): Date, Email, Plan, Source
2. Read the header row once → { header: column }
3. Sort allowlisted columns, group neighbours into runs (A:C, F)
4. setValues once per run, starting at the next empty row
5. Check the formula columns in the new rows still hold formulas

Write only the data columns

// The script owns these columns. Anything else (formulas, notes) is off limits.
var ORDER_DATA_HEADERS = ['Date', 'Email', 'Plan', 'Source'];

function appendOrders_(rows) {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Orders');
  // Header map + allowlist + one setValues per contiguous run of columns
  return writeBlockLeavingFormulaCols(sheet, rows, ORDER_DATA_HEADERS);
}

function importOrders() {
  var rows = fetchNewOrders_(); // [{ Date, Email, Plan, Source }, ...]
  if (!rows.length) return;
  var result = appendOrders_(rows);
  checkFormulasSurvived_(result.startRow, result.numRows);
}

The full helper is in the writeBlockLeavingFormulaCols snippet. The core idea fits in a few lines:

// cols = allowlisted headers resolved to column numbers, sorted
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] });
});
// A:C is one run, F is another — D and E are never in a range we write

Two setValues calls instead of one is a rounding error. Ten thousand setValue calls is not.

Formulas in new rows

Writing around formula columns keeps existing formulas safe, but new rows still need a formula in D and E. Two clean options:

ARRAYFORMULA in the header row (e.g. ={"Price"; ARRAYFORMULA(IF(C2:C="",,C2:C*1.2))}) — new rows are covered automatically, and your script never has to think about it.
Copy the formula down after the write — sheet.getRange(startRow - 1, 4, 1, 2).copyTo(sheet.getRange(startRow, 4, numRows, 2)).

Heads-up: pre-filled formulas (or an ARRAYFORMULA returning "") can push getLastRow() below your real data. If that happens, work out the start row from a data column (like Email) and pass startRow explicitly.

“Did we clobber anything?” check

Cheap insurance: after the write, confirm the formula columns in the new rows still hold formulas. If not, fail loud before the numbers travel anywhere.

var FORMULA_HEADERS = ['Price', 'Tax'];

function checkFormulasSurvived_(startRow, numRows) {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Orders');
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  var problems = [];

  FORMULA_HEADERS.forEach(function (h) {
    var col = headers.indexOf(h) + 1;
    if (!col) return problems.push('missing header ' + h);
    // Check the header row too (ARRAYFORMULA lives there) plus the rows we just wrote
    var formulas = sheet.getRange(startRow, col, numRows, 1).getFormulas();
    var headerFormula = sheet.getRange(1, col).getFormula();
    formulas.forEach(function (r, i) {
      if (!r[0] && !headerFormula) problems.push(h + ' row ' + (startRow + i) + ' has no formula');
    });
  });

  if (problems.length) {
    throw new Error('Formula check failed: ' + problems.slice(0, 5).join('; '));
  }
}

Throwing here means your nightly failure email fires and the problem gets fixed the same day, not at month end.

Failure modes worth naming

Header typo — 'E-mail' vs 'Email'. The helper throws “header not found” instead of silently writing nothing. Good.
Duplicate headers — two “Notes” columns; the first match wins. Rename one.
Someone adds a data column — it’s not on the allowlist, so it stays empty until you add it. Annoying, but safe.
Protected formula columns — fine; you never write them. If the whole sheet is protected, run the script as the owner.
ARRAYFORMULA spills into data columns — if a formula tries to spill into a cell you write, it shows #REF!. Keep array formulas in their own columns.

Soft Co-Pilot note

NitroGAS puts writeBlockLeavingFormulaCols a search away in the editor. If you paste your header row, Co-Pilot can sketch the allowlist and the “did we clobber anything?” check for you. You still decide which columns are data. The core extension is free; Co-Pilot is a pass you grab when you need it.

Closing checklist

No appendRow or full-width setValues on sheets with formula columns
Data headers allowlisted in one constant
Columns resolved by header at run time, not by letter
New rows get formulas (ARRAYFORMULA or copy-down)
Post-write formula check throws on a clobber

Happy Coding!