// Sheets · Apps Script

Transpose a range with a script in Google Sheets.

How to transpose a range in Google Sheets using Apps Script, including how to pad jagged rows so setValues never throws an 'incorrect range width' error.

“I want to rotate rows into columns (or vice versa) programmatically so I can automate a report layout without doing it by hand every time.”

The script

copy · paste · trigger
transposeRange.gs
Apps Script
// Reads A1:C4, transposes rows<->cols, writes to E1
function transposeRange() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var source = sheet.getRange('A1:C4').getValues();

  // Pad rows so every row is the same length before transposing
  var colCount = source.reduce(function(max, row) {
    return Math.max(max, row.length);
  }, 0);
  var padded = source.map(function(row) {
    while (row.length < colCount) row.push('');
    return row;
  });

  // Build transposed array: [row][col] becomes [col][row]
  var transposed = padded[0].map(function(_, c) {
    return padded.map(function(row) { return row[c]; });
  });

  var dest = sheet.getRange(1, 5, transposed.length, transposed[0].length);
  dest.setValues(transposed);
}
Generate yours

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

Walkthrough

What getValues actually hands you

SpreadsheetApp.getRange('A1:C4').getValues() returns a two-dimensional JavaScript array: an outer array of rows, each containing an inner array of cell values. Row 0 is your header, row 3 is your last data row. The shape is always [rowCount][colCount] for a well-formed rectangular range.

That 'well-formed' qualifier is where people get burned. If you built the source array yourself by pushing rows of uneven length — common when you're assembling data from an API or a reduce — the inner arrays can have different lengths. Apps Script's setValues call validates the destination range dimensions against every inner array before writing a single cell. One short row and the whole write fails with 'The number of columns in the data does not match the number of columns in the range.' The fix is to pad before you transpose, not after.

The padding step and why it goes first

The script finds the widest row with a reduce pass, then walks every row and appends empty strings until it matches that width. Empty string is the right sentinel here: null and undefined both serialize oddly in Sheets, and a number like 0 would corrupt numeric data downstream.

I keep this padding utility in a shared utils file across every project that touches Apps Script data assembly. The first time I hit the incorrect-range-width error it took twenty minutes to trace back to one row that was one element short because a source API returned an empty array for a missing field. Padding defensively costs nothing; debugging it at 11pm costs a lot.

Only after padding does the actual transpose happen. The idiom is padded[0].map(function(_, c) { return padded.map(function(row) { return row[c]; }); }). Read it as: for each column index c in the first row, collect that column across all rows. That produces a new array where the original columns are now the rows.

Sizing the destination range correctly

sheet.getRange(1, 5, transposed.length, transposed[0].length) sizes the write range to exactly the transposed output: row 1, column 5 (E), with height equal to the number of transposed rows and width equal to the number of transposed columns.

The critical habit is to derive those dimensions from the transposed array itself, not from the source range. If you hardcode getRange('E1:H3') and then the source grows by a column, the write range is wrong and setValues throws again. Derive, don't hardcode.

If you need to clear the destination area before writing, call dest.clearContent() before setValues. Otherwise stale values from a previous run that had a larger output will persist outside the newly written area.

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
Why does TRANSPOSE() in a cell not work for my use case?
The built-in TRANSPOSE formula is read-only and recalculates on every sheet change. If you need to write the transposed data to a fixed range, pipe it into another system, or trigger the transpose on a schedule, a script is the right tool. The formula also can't pad jagged input.
Can I transpose to a different sheet instead of a different column?
Yes. Replace sheet.getRange(1, 5, ...) with SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Output').getRange(1, 1, ...). Everything else stays the same.
What happens if my source range has merged cells?
getValues() reads merged cells as the value in the top-left cell of the merge; every other cell in the merge returns an empty string. Transposing that is safe, but the destination will not reproduce the merge — only the values.
The script runs but writes one extra blank row at the bottom. Why?
The source range probably includes a trailing empty row. Trim the range address to match your actual data, or filter out all-empty rows from the source array before padding: source = source.filter(function(row) { return row.some(function(v) { return v !== ''; }); });

Related scripts