Answers / Question 12

Import every CSV in a Google Drive folder into one sheet

An Apps Script that reads every CSV in a Drive folder, keeps a single header row, skips blank lines and writes everything to one tab in a single call.

The problem

Exports arrive as separate CSV files in a Drive folder, and you want them combined into one tab automatically rather than importing each file by hand.

Inspired by a public question on Stack Overflow. The problem above is described in our own words and the solution below is ours.

The fix

Copy the folder ID (the long code at the end of the folder's URL) into folderId.
Paste the code into Extensions → Apps Script in the destination spreadsheet.
Run mergeCsvs and approve. It rebuilds the Merged tab each time.

Code.gsApps Script

// Combine every CSV in one Drive folder into a single tab, keeping one header row.
const CSV_MERGE = {
  folderId: 'PASTE_FOLDER_ID',   // the long ID at the end of the folder's URL
  sheetName: 'Merged'
};

function combineCsvRows(tables) {
  let header = null;
  const out = [];
  tables.forEach(function (rows) {
    if (!rows.length) return;
    if (!header) { header = rows[0]; out.push(header); }
    for (let i = 1; i < rows.length; i++) {                         // skip each file's own header
      if (rows[i].some(function (c) { return String(c).trim() !== ''; })) out.push(rows[i]);
    }
  });
  const width = out.reduce(function (m, r) { return Math.max(m, r.length); }, 0);
  return out.map(function (r) { return r.concat(Array(width - r.length).fill('')); });   // setValues needs a rectangle
}

function mergeCsvs() {
  const files = DriveApp.getFolderById(CSV_MERGE.folderId).getFilesByType(MimeType.CSV);
  const tables = [];
  while (files.hasNext()) tables.push(Utilities.parseCsv(files.next().getBlob().getDataAsString()));
  const out = combineCsvRows(tables);
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName(CSV_MERGE.sheetName) || ss.insertSheet(CSV_MERGE.sheetName);
  sh.clearContents();
  if (out.length) sh.getRange(1, 1, out.length, out[0].length).setValues(out);
}

Two bugs this avoids

setValues needs a perfect rectangle. If one file has an extra column, the whole write fails, so the script pads short rows.
The 4th argument of getRange(row, col, numRows, numColumns) is the number of columns. Passing the row count there is a very common cause of “the number of columns in the data does not match” errors.

Good to know

This assumes every file has the same columns in the same order. If they don't, the merge CSV recipe matches columns by header name instead.
Drive doesn't promise any file order. Sort the result afterwards if order matters.
To run it automatically, add a time-driven trigger for mergeCsvs (clock icon → Add trigger).

The script's logic is unit-tested against a simulated spreadsheet before publishing. Always try new automations on a copy of your sheet first.