Coding level: Intermediate
Duration: 15 minutes
Project type: Automation with a time-driven trigger
Objectives
- Understand what the solution does.
- Understand what the Apps Script services do within the solution.
- Set up the script.
- Run the script.
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 that 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
Session
class 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 script uses the time zone 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.
Prerequisites
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.
Set up the script
- Click the following button to open the Import CSV data
Apps Script project.
Open the project - Click Overview .
- On the overview page, click Make a copy .
Run the script
- In your 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 that the script uses to run successfully.
When prompted, authorize the script. If the OAuth consent screen displays the warning, This app isn't verified, continue by selecting Advanced > Go to {Project Name} (unsafe).
To view the time-driven trigger after setup is complete, 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.
(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 SetupSample.gs file.
- In the function dropdown, select removeSample and click Run. This function deletes the trigger, files, and folder created in the previous section.
Review the code
To review the Apps Script code for this solution, click View source code below:
View source code
Code.gs
SampleData.gs
SetupSample.gs
Utilities.gs
Contributors
This sample is maintained by Google with the help of Google Developer Experts.