Coding level: Intermediate
Duration: 15 minutes
Project type: Automation with a time-driven trigger
About this solution
Automatically import data from CSV files into one Google Sheets spreadsheet. If you work with multiple CSV files that are similarly structured, you can use this solution to centralize the data in Sheets.
How it works
The script runs daily on a time-driven trigger. It iterates through CSV files in a designated folder and adds the data from each file to a spreadsheet. By default, the script removes the header row of each CSV dataset before adding the data to the last row of the sheet. The script sends a summary email listing the files that were imported and moves the files to a different folder to prevent duplicate processing.
The script also includes functions that set up sample CSV files to demo this solution.
Apps Script services
This solution uses the following services:
- Script Service: Creates the time-driven trigger.
- Drive Service: Gets the folders the script uses to store processed and unprocessed CSV files and creates them if they don't exist. Gets the Apps Script project URL to include in the summary email.
- Spreadsheet Service: Gets the spreadsheet where the script adds the data from each CSV file.
- Base Service: Uses the
Sessionclass to get the user's email address and the script's time zone.
- The user is based on who runs the script. Since the script runs on a time-driven trigger, the user is defined as the person that created the trigger.
- The time zone is used to add the date and time that the script ran to the summary email.
- Utilities Service: Parses each CSV file into an array. Formats the date that the script adds to the summary email.
- Mail Service: Sends the summary email after data from CSV files are imported into the spreadsheet.
Before you begin
To use this sample, you need the following prerequisites:
- A Google Account (Google Workspace accounts might require administrator approval)
- A web browser with access to the internet
Step 1: Set up the Apps Script project
- Click the button below to open the Import CSV data Apps Script project.
Open the project
- Click Overview .
- At the top-right, click Make a copy .
Step 2: Run a demo
- In the copied Apps Script project, go to the SetupSample.gs file.
- In the function dropdown, select setupSample and click Run. This function creates the time-driven trigger, the CSV files, the spreadsheet, and the folder the script uses to run successfully.
- When prompted, authorize the script.
- To view the time-driven trigger once setup is complete, at the left, click Triggers .
- To view the created files, open the [Apps Script sample] Import CSVs folder in Google Drive.
- Switch back to the Apps Script project and in the editor, go to the Code.gs file.
- In the function dropdown, select updateApplicationSheet and click Run.
- To view the summary email with a link to the spreadsheet with imported data, check your email inbox.
Step 3: (Optional) Reset the solution
You can reset the solution to update it with your own data or retry the demo.
- In the Apps Script project, go to the sampleSetup.gs file.
- In the function dropdown, select removeSample and click Run. This function deletes the trigger, files, and folder created in the previous section.
This sample is maintained by Google with the help of Google Developer Experts.