CSV Import for Google Sheets

A Google Apps Script that automatically syncs CSV files from a Google Drive folder into a Google Sheet. Features a progress dialog, duplicate detection, and optional cleanup of removed files.

Features

• Auto-import on open – Imports new CSV files every time you open the spreadsheet
• Recursive folder search – Searches subfolders with regex pattern filtering
• Progress dialog – Shows which file is being processed in real-time
• Duplicate detection – Skips files that have already been imported
• Sync deletions – Optionally removes rows when source CSV files are deleted
• Selective columns – Import all columns or only the ones you need
• Header preservation – Protect rows at the top of your sheet from being overwritten
• Atomic writes – All changes are written in a single operation, triggering formula recalculation only once

File Structure

├── Code.gs              # Main script logic
└── ProgressDialog.html  # Modal dialog UI

Setup

1. Create the Apps Script Project

1. Open your Google Sheet
2. Go to Extensions → Apps Script
3. Delete any existing code in Code.gs
4. Copy the contents of Code.gs from this repo and paste it

2. Create the Dialog File

1. In the Apps Script editor, click + next to "Files"
2. Select HTML
3. Name it exactly: ProgressDialog (without .html extension)
4. Copy the contents of ProgressDialog.html from this repo and paste it

3. Configure

Edit the CONFIG object at the top of Code.gs:

const CONFIG = {
  CSV_FOLDER_ID: 'your-folder-id-here',
  CSV_FILE_REGEX: '\\.csv$',
  CSV_START_ROW: 2,
  CSV_DELIMITER: ',',
  CSV_COLS_TO_INCLUDE: [1, 2, 4, 5, 10, 12],
  CSV_LOCALE: 'en_US',
  SHEET_NAME: 'Data',
  SHEET_START_ROW: 2,
  SYNC_DELETIONS: true,
};

Option	Description
CSV_FOLDER_ID	Google Drive folder ID containing your CSV files. Find it in the folder's URL: drive.google.com/drive/folders/[FOLDER_ID]
CSV_FILE_REGEX	Regex pattern to filter which CSV files to import. Matches against the relative path from the root folder. See examples below.
CSV_START_ROW	Row number to start reading from in each CSV file (1-indexed). Set to 2 to skip a header row, 3 to skip both header and second row, 1 to import all rows.
CSV_DELIMITER	Character separating values in your CSV files: ',', ';', or '\t'
CSV_COLS_TO_INCLUDE	Array of column numbers to import (1 = first column, 2 = second column, etc). Set to null or [] to import all columns.
CSV_LOCALE	Locale code matching your CSV files' number format (e.g., 'en_US' for period as decimal separator, 'sv_SE' for comma). If this differs from the spreadsheet's locale, the spreadsheet locale is temporarily switched during import to ensure correct parsing of numbers, percentages, and currencies. A warning is shown in the dialog when this happens.
SHEET_NAME	Name of the sheet tab where data will be imported. Case-sensitive.
SHEET_START_ROW	First row in the sheet where data will be written. Use this to preserve header rows or other content at the top. For example, set to 2 to keep row 1 for headers, or 7 to preserve rows 1–6.
SYNC_DELETIONS	Set true to remove rows when their source CSV is deleted from the folder.

CSV_FILE_REGEX Examples

Pattern	Description
\\.csv$	All CSV files in all folders (default)
^[^/]*\\.csv$	Only CSV files in the root folder (no subfolders)
Diagram\\.csv$	Files ending with "Diagram.csv"
^[^/]+/Diagram\\.csv$	Files named exactly "Diagram.csv" in immediate subfolders only
/Reports/	Files in any folder named "Reports"
^2024/	Files in subfolders starting with "2024"
^Project1/.*\\.csv$	All CSV files under the "Project1" folder

Note: Column A in your sheet is reserved for the source file path (relative to the root folder, e.g., Reports/2024/data.csv). Your CSV data starts in column B.

4. Save the Project

Press Ctrl+S (Windows) or Cmd+S (Mac) and wait for "Project saved" confirmation.

5. Create the Trigger

Simple triggers can't show modal dialogs due to Google's security restrictions. You need an installable trigger:

1. In the Apps Script editor, click the clock icon in the left sidebar
2. Click + Add Trigger (bottom right)
3. Configure:
   • Choose which function to run: onOpenTrigger
   • Choose which deployment: Head
   • Select event source: From spreadsheet
   • Select event type: On open
4. Click Save
5. Authorize when prompted

6. Authorize Drive Access

The first time you use the script, you need to authorize access to Google Drive:

1. In the Apps Script editor, select importNewCSVFiles from the function dropdown
2. Click Run
3. If prompted, click through the authorization dialog and grant permissions
4. You only need to do this once per spreadsheet

7. Test

Reload your spreadsheet (F5 or Ctrl+R / Cmd+R).

The import dialog should appear automatically. Note that it may take a few seconds after page load.

How It Works

1. On spreadsheet open, the trigger checks if the spreadsheet's locale differs from CSV_LOCALE
2. If different, the locale is switched to CSV_LOCALE (this reloads the page)
3. After reload (or if no switch was needed), a modal dialog is displayed
4. If locale was changed, a warning is shown explaining how to restore it
5. The dialog clears any stale status, then starts the import process
6. The dialog polls for status updates while import runs
7. The script recursively scans the configured Drive folder and subfolders for CSV files matching CSV_FILE_REGEX
8. Existing data is loaded into memory
9. If SYNC_DELETIONS is enabled, rows from deleted files are filtered out (in memory)
10. New CSV files are parsed and added to the data (in memory)
11. All data is written to the sheet in a single atomic operation
12. Formula recalculation triggers once, after all data is in place
13. The dialog shows a summary and a Close button
14. When the user clicks Close, the original locale is restored (triggering another page reload)

Performance

The script is optimized for large datasets:

• Single atomic write – All changes (deletions + additions) are combined and written in one setValues() call, ensuring formulas recalculate only once
• In-memory processing – Data filtering and merging happens in memory, minimizing API calls
• Batch operations – No loops with individual cell writes

Limitations

• Memory: Very large sheets (approaching 10 million cells) may cause memory issues
• Execution time: Google Apps Script has a 6-minute timeout
• Permissions: The script needs access to Google Drive and Sheets