Recently, I helped a colleague set up an automated script for importing data from Excel files so that they can be formatted on Google Sheets. As the data source did not provide a public API, this colleague needed to download the required data files from the source and manually copy the required information onto a Google Sheet shared with a client.
I saw an opportunity here to simplify the process, so that it looked something like this:
- Download Excel file from data source
- Upload Excel file to Google Drive
- Run a script to automatically update a database on Google Sheets
This meant that you can effectively set up dynamic formulas on the Google Sheets instead of manually copying & pasting data. These three short steps would essentially replicate what an API would be able to achieve by automatically ‘pulling in’ the data.
In this post, we’ll have a quick run through how this would look like for the user. Feel free to have a look at the Google Sheet with this script here.
Setup
Before running the script, we will need to specify the input folder, which is where the Excel files should be uploaded. We will just need the ID of the folder, which is the last part of the folder URL:
To ensure good data practice, I also created an ‘Added’ folder, which is where all the files would be moved and stored after they have been used to update our Google Sheet.
We do the same and get the folder ID for this ‘added’ folder as well.
In our script, we just need to put these folder IDs in the first few lines:
‘Refresh’ Google Sheet with data from Excel
Once we have set up our dependencies, we just need to do the following:
- Upload Excel file to Google Drive folder
- Click ‘refresh data’ button in Google Sheets
Here’s how it would look:
All the files will then be moved to the ‘Added’ folder in case there are further data validations that need to be made:
And there you have it! Quickly automate your data from Excel to Google Sheets with this simple script.
Check out the Google Sheet with the script here.
Summary of Features:
- Excel files that have been added are move to an ‘added’ folder
- The script gets all the data from a specified sheet in each Excel
- An additional column is added to specify the file the data rows were imported from.
- New data is added to the bottom of the database
Sources:
Aiman Fikri 