Utilities.parseCsv is fine for tidy files. Real Drive drops include quoted commas, embedded newlines, and "" escapes. parseCsvText turns a CSV string into a 2D values array — the inbound counterpart to writeValuesAsCsvToDrive / sheet-values-to-csv.
What you'll need
- A CSV string (Drive blob
.getDataAsString(), UrlFetch body, or pasted text) - A destination that accepts 2D arrays (
setValues, validation, chunked import) - Awareness of encoding (prefer UTF-8 when reading Drive)
How to use this snippet
/**
* Parse CSV text into a 2D values array. Handles quotes, commas, and newlines.
* @param {string} text
* @param {{delimiter?: string}=} options
* @return {string[][]}
*/
function parseCsvText(text, options) {
options = options || {};
var delim = options.delimiter != null ? options.delimiter : ',';
var rows = [];
var row = [];
var field = '';
var i = 0;
var inQuotes = false;
text = String(text == null ? '' : text);
while (i < text.length) {
var ch = text.charAt(i);
if (inQuotes) {
if (ch === '"') {
if (text.charAt(i + 1) === '"') {
field += '"';
i += 2;
continue;
}
inQuotes = false;
i++;
continue;
}
field += ch;
i++;
continue;
}
if (ch === '"') {
inQuotes = true;
i++;
continue;
}
if (ch === delim) {
row.push(field);
field = '';
i++;
continue;
}
if (ch === '\r') {
i++;
continue;
}
if (ch === '\n') {
row.push(field);
rows.push(row);
row = [];
field = '';
i++;
continue;
}
field += ch;
i++;
}
if (field.length || row.length) {
row.push(field);
rows.push(row);
}
return rows;
}
Example
function importDriveCsvPreview(fileId) {
var text = DriveApp.getFileById(fileId).getBlob().getDataAsString('UTF-8');
var values = parseCsvText(text);
if (!values.length) throw new Error('empty CSV');
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Import');
sheet.clearContents();
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
return values.length;
}
Tips:
- Normalize jagged rows before
setValues(pad short rows to header width). - Strip a UTF-8 BOM (
\uFEFF) fromtextif the first header looks weird. - For huge files, parse once then write in batches — see the chunked import guide when it ships.
- Opposite direction: sheet → CSV helpers already on the site.
Tip: ask NitroGAS Co-Pilot to wire parseCsvText into a menu "Import CSV from Drive" — keep validation (headers, required cols) in your own code.
Happy Coding!
