// Sheets · Apps Script

Split comma-separated values into rows in Google Sheets.

A Google Apps Script that reads a column of comma-separated values and expands each list into individual rows, using a single setValues call so it stays fast past thousands of rows.

“I have a column where each cell holds multiple comma-separated items and I need each item on its own row without doing it by hand.”

The script

copy · paste · trigger

splitToRows.gs

Apps Script

// Splits column A (comma-separated) into individual rows on a new sheet.
// Run once from the Apps Script editor; output lands on "Expanded".
function splitToRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var src = ss.getActiveSheet();
  var data = src.getRange(2, 1, src.getLastRow() - 1, 1).getValues();

  var out = [];
  for (var i = 0; i < data.length; i++) {
    var cell = String(data[i][0]);
    var parts = cell.split(',');
    for (var j = 0; j < parts.length; j++) {
      var trimmed = parts[j].trim();
      if (trimmed !== '') {
        out.push([trimmed]);
      }
    }
  }

  var dest = ss.getSheetByName('Expanded') || ss.insertSheet('Expanded');
  dest.clearContents();
  if (out.length > 0) {
    dest.getRange(1, 1, out.length, 1).setValues(out);
  }
}

Generate yours

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

Walkthrough

Why building a 2D array first matters

The instinct when expanding rows is to call insertRowAfter inside the loop — it feels direct. The problem is that every insertRowAfter is a separate API call to Sheets' backend, and Apps Script has a hard quota of about 6 minutes of execution per run. With 500 source rows averaging 4 values each, you're looking at 2,000 insertRowAfter calls. That's when the script starts timing out. The first time I hit this, I had a product catalog import that ran fine in testing (50 rows) and died silently in production (800 rows) — no error surfaced, just a truncated sheet.

The fix is to treat the Sheets API as a write-once surface. Read everything in one getValues call, build the full output array in plain JavaScript memory, then write it back in one setValues call. JavaScript array operations have no quota cost; only the two Sheets calls count against your limits.

The script above does exactly this. getRange(2, 1, src.getLastRow() - 1, 1).getValues() reads column A from row 2 to the end in a single round-trip. The nested loop builds out, a flat 2D array where every element is a one-item array (the shape setValues expects). Then one dest.getRange(...).setValues(out) writes the whole thing.

Handling the edge cases that catch people out

Two gotchas show up in almost every real dataset. First, whitespace: cells typed by hand often have spaces after the commas ("apples, oranges, pears"). The trim() call on each part handles this, so the output rows won't have leading spaces silently corrupting lookups or VLOOKUP matches later.

Second, empty trailing values: a cell ending with a comma ("apples, oranges,") produces an empty string as the last split result. The if (trimmed !== '') guard skips those. Without it you end up with blank rows scattered through the output, which is hard to debug after the fact.

The String() cast on data[i][0] is a small but load-bearing detail. If the column contains numeric-looking values (product codes like "12345") or booleans, getValues() returns them as JavaScript numbers or booleans, and calling .split() on a non-string throws a runtime error. Casting to String first makes the script safe across mixed column contents.

Running the script and adapting it to your sheet

Open Extensions > Apps Script from your spreadsheet, paste the function, and hit Run. The first run will ask for permission to access your spreadsheet — that's the OAuth consent for the SpreadsheetApp scope. After you accept, the output appears on a tab called Expanded, which the script creates if it doesn't exist and clears on each subsequent run.

Two things to change for your own sheet: if your data starts at row 1 (no header), change the getRange arguments to (1, 1, src.getLastRow(), 1). If your values are in a column other than A, change the second argument (column index, 1-based) — column B is 2, column C is 3, and so on.

If you need to carry sibling columns (say, keep a name from column B next to each expanded value), extend getValues to read both columns at once with getRange(2, 1, src.getLastRow() - 1, 2), then push [trimmed, data[i][1]] into out. The setValues call needs the column count updated to match: getRange(1, 1, out.length, 2).

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

Can I run this on a sheet with tens of thousands of rows?: Yes. The single getValues and setValues calls each count as one API operation regardless of row count. The practical ceiling is the 6-minute execution limit, which at 50,000 rows with a JavaScript loop running in V8 you won't approach. The old insertRowAfter-in-a-loop pattern is what hits that wall.
My comma-separated values use a different delimiter — semicolons or pipes. How do I change it?: Replace the ',' in cell.split(',') with your actual delimiter: ';' for semicolons, '|' for pipes. The rest of the script is delimiter-agnostic.
The script runs but the Expanded sheet is empty. What went wrong?: The most common cause is that getLastRow() returns 1 when column A has no data below row 1, making getLastRow() - 1 equal to 0, which getRange rejects silently. Check that your source data is actually in column A and starts at row 2 (or adjust the range as described above). A second cause: if every cell in the column is blank or contains only commas, the trim-and-empty-check filters everything out.
Will re-running the script duplicate the output?: No. dest.clearContents() runs before setValues on every execution, so the Expanded sheet is always a fresh write of the current source data.

// one good script a week