Whenever an external system drops a fresh CSV export into a Drive folder, importCsvFromDrive can pull that file straight into a spreadsheet without anyone downloading it locally or copy-pasting rows by hand.
Manually opening a CSV, selecting all the data, and pasting it into Sheets is slow and error-prone, especially with special characters or commas embedded inside quoted fields that a naive paste can misinterpret entirely.
This example fetches the file by its Drive ID, decodes it as UTF-8 text, and hands the raw content to Utilities.parseCsv, which correctly handles quoted fields and embedded commas the way a real CSV parser should.
You'll finish with an Imported Orders sheet that refreshes completely from the latest CSV on demand, plus an Import Log tab recording exactly when each import ran and how many rows it brought in.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Item | Location | Purpose |
|---|---|---|
| fileId | Script constant | The Drive file ID of the CSV to import |
| Imported Orders | Sheet | Destination sheet - fully cleared and repopulated on each import |
| Import Log | Sheet | Append-only log of import timestamp, filename, and row count |
What it does
The function fetches a specific Drive file by ID, reads its contents as a UTF-8 string, and parses that string into a 2D array of rows and columns using Utilities.parseCsv. It then clears the destination sheet completely and writes the parsed data back in a single setValues call, before logging the import.
- clearContents() wipes the destination sheet before every import, so stale rows never linger
- Utilities.parseCsv correctly handles quoted fields containing commas, unlike a naive split(',')
- Every import is logged with a timestamp, filename, and row count for traceability
Prerequisites
The script's owner needs view access to the Drive file referenced by fileId, and the CSV should use a consistent encoding - UTF-8 is assumed here, which covers the vast majority of exports from modern systems.
- A valid Drive file ID for the CSV to import
- A destination sheet named 'Imported Orders'
- A logging sheet named 'Import Log' with an appendRow-friendly structure
- Drive read permission and Sheets write permission granted to the script
Walkthrough
file.getBlob().getDataAsString('UTF-8') is what actually reads the file's bytes and decodes them into a JavaScript string; leaving off the encoding argument would fall back to a default that can mangle non-ASCII characters in some files.
Utilities.parseCsv returns rows as a nested array with the header row included as rows[0], which is why numColumns is derived from rows[0].length rather than a hard-coded column count - the import automatically adapts if the CSV's column count changes between exports.
Edge cases
A CSV using a delimiter other than a comma, like a semicolon-separated export common in some European locales, will not parse correctly with the default Utilities.parseCsv call, since it assumes commas unless told otherwise.
- Files larger than a few million cells can exceed Sheets' maximum cell count and fail on setValues
- Encoding mismatches (e.g., a file actually saved as Latin-1) can produce garbled special characters
- A CSV missing its header row would shift setFontWeight('bold') onto real data instead of headers
Testing
Run the import against a small test CSV with known contents, then manually verify that every row landed in the correct cell and that the header row is bolded and frozen as expected.
- Test with a CSV containing a comma inside a quoted field to confirm parseCsv handles it correctly
- Test with an empty CSV file to confirm the early return prevents a crash
- Check the Import Log after two consecutive imports to confirm both runs are recorded distinctly
Hardening
Clearing the entire destination sheet on every import is destructive by design, so before running this against a live sheet that other people are actively viewing or referencing, consider whether a staging sheet followed by a manual swap is safer.
- Import into a staging sheet first, then copy to the live sheet only after a manual or automated sanity check
- Validate the parsed row count against an expected minimum before committing to clearContents()
- Wrap the whole import in a try/catch so a malformed file doesn't leave the destination sheet cleared with nothing written back
Variations
The same DriveApp and parseCsv combination extends naturally to importing from a fixed folder rather than a fixed file ID, automatically picking up whichever CSV was most recently added.
- Search a specific Drive folder for the most recently modified CSV instead of hard-coding a file ID
- Append new rows instead of clearing the sheet, for incremental rather than full-refresh imports
- Trigger the import automatically on a schedule if the source file is refreshed by another system on a predictable cadence
Full code: importCsvFromDrive()
The function trusts Utilities.parseCsv to handle the tricky parts of CSV parsing correctly, rather than attempting a manual split on commas that would break on quoted fields.
function importCsvFromDrive() {
var fileId = '1AbCDeFGhIJKlmNoPQRstuVWxyz1234567890';
var file = DriveApp.getFileById(fileId);
var csvContent = file.getBlob().getDataAsString('UTF-8');
var rows = Utilities.parseCsv(csvContent);
if (rows.length === 0) return;
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Imported Orders');
sheet.clearContents();
var numColumns = rows[0].length;
var range = sheet.getRange(1, 1, rows.length, numColumns);
range.setValues(rows);
sheet.getRange(1, 1, 1, numColumns).setFontWeight('bold');
sheet.setFrozenRows(1);
var logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Import Log');
logSheet.appendRow([new Date(), file.getName(), rows.length - 1 + ' rows imported']);
}- Line 3: Fetches the Drive file object for the CSV using its unique file ID.
- Line 4: Reads the file's raw bytes and decodes them as a UTF-8 string.
- Line 6: Parses the CSV text into a 2D array of rows, correctly handling quoted commas.
- Line 10: Clears every existing value on the destination sheet before writing the fresh import.
- Line 14: Writes the entire parsed dataset back into the sheet in a single setValues call.
- Line 17: Freezes the header row so it stays visible while scrolling through imported data.
- Line 20: Appends a log entry recording when the import ran and how many data rows it brought in.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before running the CSV import
- 1fileId confirmed to point at the correct, current CSV file
- 2Destination sheet name matches 'Imported Orders' exactly
- 3Import Log sheet exists and is ready to receive appendRow calls
- 4Encoding of the source CSV confirmed as UTF-8
- 5Considered staging the import before overwriting live data
- 6Row count sanity-checked against an expected range for this export
Frequently asked questions
A simple comma split breaks as soon as any field contains a comma inside quotes, which is extremely common in real CSV exports (addresses, descriptions, names with suffixes); parseCsv correctly respects quoting rules that a naive split ignores entirely.
DriveApp.getFileById will throw an exception when the file no longer exists, which will surface as a failed execution in the Apps Script logs rather than silently importing stale or empty data.
Yes - fetch it with UrlFetchApp.fetch(url).getContentText() instead of reading a Drive blob, then pass that text into Utilities.parseCsv exactly the same way.
No - parseCsv returns everything as strings, but setValues can let Sheets auto-convert numeric-looking strings into numbers, which strips leading zeros; format the destination column as plain text before importing if that matters.
Compare each parsed row against what's already in the destination sheet (for example by a unique order ID column) and use appendRow only for rows that aren't already present, rather than calling clearContents at all.
Practical limits come from Apps Script's six-minute execution time and Sheets' roughly ten million cell limit per spreadsheet; very large CSVs may need to be chunked or imported into multiple sheets.