 |
|
How to Import CSV Files into Google Spreadsheets with Google Apps Script – A Complete Guide
Importing CSV files into Google Sheets is a common task for anyone who deals with data collection, business reports, automated exports, or system integration. While Google Sheets provides manual import options, automating the process with Google Apps Script can save time and reduce manual errors, especially if you receive new CSV files regularly via Google Drive, email, or webhooks.
In this detailed blog post, you will learn how to use Google Apps Script to automatically import CSV files into a Google Spreadsheet, either from Google Drive or from an external URL. We will cover both methods and provide robust, customizable scripts.
Here are some scenarios where this automation is useful:
You receive CSV reports daily/weekly and want them auto-loaded into Sheets
You download CSVs from APIs or websites and want to parse them
You want to consolidate CSV files from a shared Google Drive folder
You want to keep historical data from multiple CSV files in one master spreadsheet
Using Google Apps Script allows you to process CSVs:
Without downloading or uploading manually
On a time schedule or trigger
With logic to filter, transform, or map data
This method will read a CSV file stored in your Google Drive and import its contents into a Google Sheet.
Upload your .csv file to any folder in Google Drive.
Make note of its file name or file ID.
Open your Google Sheet.
Click Extensions > Apps Script.
Delete any code and paste the following script:
function importCSVFromDrive() {
const csvFileName = 'your_file_name.csv'; // Name of the CSV file in Drive
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Change sheet name as needed
const files = DriveApp.getFilesByName(csvFileName);
if (!files.hasNext()) {
Logger.log("CSV file not found.");
return;
}
const file = files.next();
const csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
sheet.clearContents(); // Optional: clear existing content
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
Logger.log("CSV imported successfully.");
}
Replace 'your_file_name.csv' with the actual name of your file.
Run the script. Grant permissions if asked.
The content from the CSV will be loaded into the specified sheet.
You can also import CSV data from a remote URL (e.g., GitHub, server endpoint, etc.).
function importCSVFromUrl() {
const csvUrl = 'https://example.com/data.csv'; // Change to actual CSV URL
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const response = UrlFetchApp.fetch(csvUrl);
const csvContent = response.getContentText();
const csvData = Utilities.parseCsv(csvContent);
sheet.clearContents();
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
Logger.log("CSV imported from URL successfully.");
}
Make sure the URL is publicly accessible.
This method is great for GitHub-hosted CSVs, APIs, public export links, etc.
To process all CSV files in a specific Drive folder:
Open the folder and copy its ID from the URL:
https://drive.google.com/drive/folders/FOLDER_ID_HERE
function importAllCSVsFromFolder() {
const folderId = 'YOUR_FOLDER_ID'; // Change this
const folder = DriveApp.getFolderById(folderId);
const files = folder.getFilesByType(MimeType.CSV);
const ss = SpreadsheetApp.getActiveSpreadsheet();
while (files.hasNext()) {
const file = files.next();
const content = file.getBlob().getDataAsString();
const csvData = Utilities.parseCsv(content);
// Create new sheet for each file
const sheetName = file.getName().replace('.csv', '');
let newSheet;
if (ss.getSheetByName(sheetName)) {
ss.deleteSheet(ss.getSheetByName(sheetName));
}
newSheet = ss.insertSheet(sheetName);
newSheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}
Logger.log("All CSV files imported into individual sheets.");
}
Replace 'YOUR_FOLDER_ID' with the folder ID.
This will create a new tab for each CSV file and import data into it.
You can run your import scripts on a schedule using Time-driven Triggers:
Open Apps Script editor.
Click on the clock icon (Triggers).
Click + Add Trigger.
Choose your function (e.g., importCSVFromDrive).
Select the type as Time-driven (e.g., daily, hourly).
Save the trigger.
Now your spreadsheet will update automatically based on new CSV files or URL content.
You can skip the first row:
csvData.slice(1) // Removes header
Use getDataAsString("UTF-8") or "ISO-8859-1" to fix special characters:
const content = file.getBlob().getDataAsString("UTF-8");
Instead of creating new sheets for each CSV, you can append all rows into a master sheet:
masterSheet.getRange(lastRow + 1, 1, csvData.length, csvData[0].length).setValues(csvData);
If the file is not found, double-check the name or ID.
If data isn't populating correctly, inspect with Logger.log(csvData).
Ensure public URLs don't require authentication.
Large files may take time or hit script execution limits (~6 minutes).
Use sheet.clearContents() only if you’re okay with wiping previous data.
Importing CSV files into Google Sheets can be completely automated with Google Apps Script. Whether your data source is a URL, a single file in Google Drive, or a folder with multiple CSVs, you can write a script once and save countless hours of manual importing.
This guide empowers you to:
Create custom workflows for data ingestion
Set up auto-updating dashboards
Build integrations with APIs, CRMs, and external systems
Maintain structured logs from multiple CSV sources
With these powerful tools, your spreadsheet transforms from a manual data entry tool into a live, connected, and intelligent data platform.