What if my table has multiple key columns, not just one?

Change the inner loop to start at the index after your last key column (e.g., c = 3 if columns A, B, C are all keys), and push all key values into the output array: [data[r][0], data[r][1], data[r][2], headers[c], data[r][c]]. Update the setValues column count to match.

How do I skip blank value cells in the output?

Add an if check inside the inner loop: if (data[r][c] === '' || data[r][c] === null) continue; This keeps the Long sheet clean when your source table is sparse.

Will this handle dates and numbers correctly, or will they turn into strings?

getValues returns raw JavaScript values — numbers stay numbers, Date objects stay Dates, booleans stay booleans. setValues writes them back as-is, so Sheets formats them according to the destination cell format. If dates look wrong after the copy, apply the date format to the Value column manually.

Can I run this on a schedule so the Long sheet updates automatically?

Yes. In Apps Script, go to Triggers (the clock icon), add a time-driven trigger pointing to unpivotTable, and set whatever interval you need — hourly, daily, on spreadsheet open. The function is idempotent, so repeated runs just overwrite the output cleanly.

// Sheets · Apps Script

Unpivot a wide table into long rows in Google Sheets.

Turn a wide pivot table into a tidy long format in Google Sheets using Apps Script — one output row per value, with columns for key, category, and value.

“I have a wide table with one row per entity and a column per month (or category), and I need to reshape it into a long format with one row per measurement so I can filter, chart, or import it properly.”

The script

copy · paste · trigger

unpivot.gs

Apps Script

// Unpivot: wide table -> long rows (key | category | value)
// Source sheet: row 1 = headers, col A = key, cols B+ = value columns
// Output sheet named "Long" is created or cleared automatically
function unpivotTable() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var src = ss.getActiveSheet();
  var data = src.getDataRange().getValues();
  var headers = data[0];
  var out = [];
  out.push(["Key", "Category", "Value"]);
  for (var r = 1; r < data.length; r++) {
    var key = data[r][0];
    for (var c = 1; c < headers.length; c++) {
      out.push([key, headers[c], data[r][c]]);
    }
  }
  var dest = ss.getSheetByName("Long") || ss.insertSheet("Long");
  dest.clearContents();
  dest.getRange(1, 1, out.length, 3).setValues(out);
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why formulas won't get you there

Sheets has TRANSPOSE, FLATTEN, and a handful of array tricks, but none of them can emit a three-column (key, category, value) tuple for every cell in a range without knowing the exact column count at formula-write time. The moment your source table gains a column — a new month, a new region — the formula silently stops covering it. That's the bug that shows up in exports three weeks later.

Apps Script sidesteps this entirely. It reads the header row once, then loops over every data row and emits one output row per value column. The header count drives the loop, so new columns get picked up automatically the next time you run it.

How the script is structured

The outer loop walks rows 1 through the end of the data (row 0 is the header). The inner loop walks columns 1 through headers.length — column 0 is the key field, everything to the right is a category. Each iteration pushes a three-element array onto the output buffer: the key from column A, the header string from row 0, and the cell value.

Batching the output into a 2D array and writing it with a single setValues call is the right shape here. Writing row-by-row inside a loop is the first performance mistake I see when people port this from Excel VBA — it hammers the Sheets API with one call per cell and hits quota errors fast on anything over a few hundred rows.

The destination sheet is found or created with getSheetByName combined with insertSheet. clearContents before writing means re-running the function is safe; you won't accumulate duplicate rows.

Running it and wiring it to a button

Open Extensions > Apps Script, paste the function, save, then run unpivotTable once to grant the spreadsheet permission. The output sheet "Long" appears with three columns: Key, Category, Value.

If you want a one-click button in the sheet itself, draw an Insert > Drawing (any shape), click the three-dot menu on it, choose Assign script, and type unpivotTable. That's enough for a team workflow where non-technical users need to refresh the long-format table after the source data updates.

If the key column is not column A in your sheet, change data[r][0] and adjust the inner loop's start index accordingly. I keep a short comment at the top of the file naming which column is the key — it saves confusion when someone else touches it six months later.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

What if my table has multiple key columns, not just one?: Change the inner loop to start at the index after your last key column (e.g., c = 3 if columns A, B, C are all keys), and push all key values into the output array: [data[r][0], data[r][1], data[r][2], headers[c], data[r][c]]. Update the setValues column count to match.
How do I skip blank value cells in the output?: Add an if check inside the inner loop: if (data[r][c] === '' || data[r][c] === null) continue; This keeps the Long sheet clean when your source table is sparse.
Will this handle dates and numbers correctly, or will they turn into strings?: getValues returns raw JavaScript values — numbers stay numbers, Date objects stay Dates, booleans stay booleans. setValues writes them back as-is, so Sheets formats them according to the destination cell format. If dates look wrong after the copy, apply the date format to the Value column manually.
Can I run this on a schedule so the Long sheet updates automatically?: Yes. In Apps Script, go to Triggers (the clock icon), add a time-driven trigger pointing to unpivotTable, and set whatever interval you need — hourly, daily, on spreadsheet open. The function is idempotent, so repeated runs just overwrite the output cleanly.

// one good script a week