Spreadsheets of today are built with a prime purpose – location independent collaboration. And, Google Sheets has been leading this innovative front. We can store these spreadsheets online and manage them across the team using appropriate access controls. While they have covered a lot of ground with these advancements, not everything is hassle-free yet. For instance there isn’t an easy approach to automatically import CSV data to Google Sheets.
Why import from CSV to Google Sheets?
Grace is a sports analyst and her job requires her to gather insights from data sets. She travels a lot to gather data on the field. Due to the nature of her job, she needs to use different devices to collect data while on the run. Sometimes an iPad. At times, her Thinkpad. And, once she is done with field work, she uses a heavy-duty desktop at her office. Given the convenience of accessibility from anywhere and any device, she uses Google Sheets to store her data.
However, she has associates who extensively work on statistical software tools like Matlab and R. These statisticians, for the interest of other technically less advanced employees, share the outputs from these tools in a CSV or TSV file format. Grace usually receives individual data sets from them, and she strings the pieces together. To date, their approach has been the following:
- The associates send CSV files over email.
- Grace downloads them onto her computer.
- Imports the downloaded CSV spreadsheets to her Google Sheets.
That is certainly a time taking process (accounting for possible communication delays), and needs manual intervention every time. Therefore, with lot of other important things going on, she’d rather prefer not worrying about repeating this process day-in day-out. But, how can she accomplish that?
Solution: Automate the CSV data import with Sheetgo
For this use case, the configuration is going to be a simple two-step procedure.
- Install Google Drive and store the CSV file on the Google Drive.
- Create a connection between the uploaded CSV file and Google Sheets file, using Sheetgo add-on.
While, the first step obviates the need for the associates sending CSV files through emails, it is a must that we have the file on our Google Drive with read access permissions. The second step automates the downloading and importing rituals that Grace regularly does.
I. Upload CSV file to Google Drive
David is Grace’s associate. He just got an important data set ready to share with Grace, who will plug this alongside her existing data. Here’s how the CSV file looks like.
Now, going back to our example, all that David has to do is drop the above mentioned CSV file onto the Google Drive folder on his computer! And, David can grant Grace access to this file by doing this: Right click on the file name > Choose ‘Google Drive’ > Click ‘Share’.
On the resulting pop-up window, key in Grace’s email and hit the ‘Send‘ button.
II. Create a connection using Sheetgo add-on and automatically import CSV data.
Now that Grace has all the dependent tasks done, she will go through the following sequence of steps to establish a connection and automatically import CSV data that David shared.
- Open the Google Sheets file that we want to import data to.
- If the Sheetgo add-on isn’t installed already, we can click the button below to install Sheetgo in our Google Sheets application.
- Go to menu Add-ons > Sheetgo > Start.
- Click on the green + button and select the “Import data” option
- Choose “Connect“.
- Find and select the required CSV file from our Google Drive.
- We may choose to adjust the frequency settings (in the Automatic update section) to automate the import on a scheduled basis, but for now we leave it as is.
- Finally, we click on the “Connect” button.
The data import keeps happening automatically and periodically once the appropriate connection is in place. Please note that we can change the frequency with which these data imports happen or even turn off the automatic update altogether. Shown below is how we navigate to the corresponding settings pane.
Connection name (in this case “SG_Major League Ball Players.csv”) > edit (the pencil icon) > Automatic update > edit.
Click on the green slider button to turn the automatic update off. Instead, we can keep the automatic update active, and change the scheduling frequency and execution time as required. Once done, click OK and then on SAVE button.
Keeping the connection active
We have accomplished the automation all the way from CSV file upload to Google Sheet importing data using Sheetgo add-on (please see the first snapshot). But, how do we make sure Sheetgo always fetches current information? It is very simple! If David needs to make any changes to the file, he can open the corresponding file directly from the Google Drive folder on his computer and proceed. Once done, he can save the file in the same folder. Doing this will ensure the changes get synchronized with the Google Drive. And therefore, the existing Sheetgo connection will automatically import CSV data which is the latest.