Import a Drive CSV into a sheet in safe chunks

TL;DR — Import a Google Drive CSV into a Sheet without timing out: parse text, validate headers, and setValues in batches — the inbound counterpart to exporting filtered rows to a dated CSV.

Export guides get the glory; imports fail at 4am with "Exceeded maximum execution time" after writing 8,000 of 40,000 rows. Read the Drive file once, parse to a 2D array, validate headers, then setValues in chunks. Same discipline as batch writes — opposite direction of exporting filtered rows to a dated CSV.

TL;DR

Drive file → string → parseCsvText (or equivalent) → values[][]
Validate headers before touching the destination sheet
Write with setValues in blocks (e.g. 500–2000 rows), not cell-by-cell
Clear or append deliberately; don’t mix both by accident
Log progress so a retry can resume or restart cleanly

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.

Shape of a safe import

function importDriveCsvInChunks(fileId, sheetName, options) {
  options = options || {};
  var chunkSize = options.chunkSize || 1000;
  var requiredHeaders = options.requiredHeaders || [];
  var mode = options.mode || 'replace'; // 'replace' | 'append'

  var text = DriveApp.getFileById(fileId).getBlob().getDataAsString('UTF-8');
  if (text.charCodeAt(0) === 0xfeff) text = text.substring(1); // BOM

  var values = parseCsvText(text);
  if (!values.length) throw new Error('CSV empty');

  var headers = values[0].map(function (h) { return String(h).trim(); });
  requiredHeaders.forEach(function (h) {
    if (headers.indexOf(h) === -1) {
      throw new Error('import: missing header "' + h + '"');
    }
  });

  // Pad jagged rows to header width
  var width = headers.length;
  for (var r = 0; r < values.length; r++) {
    while (values[r].length < width) values[r].push('');
    if (values[r].length > width) values[r] = values[r].slice(0, width);
  }

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  if (!sheet) sheet = ss.insertSheet(sheetName);

  var startRow;
  if (mode === 'replace') {
    sheet.clearContents();
    startRow = 1;
  } else {
    startRow = Math.max(1, sheet.getLastRow() + 1);
    if (startRow === 1) {
      // appending to empty sheet — include header
    } else {
      values = values.slice(1); // skip header when appending under existing
    }
  }

  var written = 0;
  for (var i = 0; i < values.length; i += chunkSize) {
    var block = values.slice(i, i + chunkSize);
    sheet.getRange(startRow + i, 1, block.length, width).setValues(block);
    written += block.length;
    // Optional: SpreadsheetApp.flush(); Utilities.sleep(50);
  }
  return { rows: written, cols: width, mode: mode };
}

parseCsvText is the snippet pattern for quoted fields / embedded newlines — don’t rely on naive .split(',').

Why chunks beat one giant setValues

Huge setValues can still time out on parse + write + formula recalc. Chunking:

Gives you progress logs between blocks
Lets you flush periodically
Makes retries conceivable (advanced: track lastWrittenRow in Properties)

function importWithProgress_(fileId) {
  // … after parse + validate …
  for (var i = 0; i < values.length; i += 1000) {
    var block = values.slice(i, i + 1000);
    sheet.getRange(1 + i, 1, block.length, width).setValues(block);
    console.log('wrote rows', i + 1, '-', i + block.length);
  }
}

Validation that saves support tickets

Before writing:

Required header names (exact strings)
Max row / column sanity checks
Reject empty files and header-only files when data is required

function assertCsvShape_(values, requiredHeaders) {
  if (!values || values.length < 2) {
    throw new Error('CSV needs a header and at least one data row');
  }
  var headers = values[0].map(function (h) { return String(h).trim(); });
  requiredHeaders.forEach(function (h) {
    if (headers.indexOf(h) === -1) throw new Error('missing header: ' + h);
  });
}

Soft Co-Pilot note

NitroGAS can pull in parseCsvText / Drive helpers; Co-Pilot helps wire a menu "Import CSV…" — you still own chunk size and header contracts. Free extension; Co-Pilot optional.

Closing checklist

UTF-8 read (+ BOM strip)
Parse → validate headers → pad rows
setValues in chunks, not per cell
Explicit replace vs append
Progress log or property checkpoint for big files

Happy Coding!