When personal computers were invented, one of the prime reasons that made them appealing was spreadsheets application. And today, it is almost impossible to imagine our work without spreadsheets. Initially, they were mostly used by a single user in the offline mode. With Google Sheets, the transition is made to online-multi-user mode. Certainly, life is made a lot easier with such advancements. One of the most beneficial uses of it offers is collaboration. We can store them online and manage them across the team using appropriate access controls. Even though we have come a long way with this innovation front, there is still room for improvement. For instance, we can’t export from Google Sheets yet. Let alone automatically exporting multiple data sets to multiple worksheets at the same time.
Why export from Google Sheets?
Veronica is a computer operator at a local high school. Every week the school organizes paper-based tests across all grades to gauge students’ performance. Students respond to these tests on OMR based answer sheets. Once the test is done, Veronica receives the response sheets from all the students and runs them through her software. Doing this will generate the scores of each student and consolidate them into a CSV file, which looks like this:
For the benefits we discussed at the beginning of this post, she stores this CSV on her Google Drive. Because she can access it using Google Sheets application from anywhere. So, every time Veronica has a new CSV file, she copies the data onto Google Sheets.
Now, at the end of this process, each class teacher needs to access his/her pupils’ scores. To illustrate this, let us consider the scores of students across three grades – 3, 4 and 5. Garrett, Henry, and Theresa are the class teachers respectively. Garrett would be interested in scores of 3rd-grade scores, and he doesn’t really need scores from other grades. Same is the case with Henry and Theresa. Manually segregating data and sharing it accordingly with the required stakeholders is an option, but not an ideal one. More so, when she has to do it with higher frequency and when there are many grades to deal with.
Solution: automate the process with Sheetgo
The Sheetgo add-on for Google Sheets elevates the connectivity between spreadsheets to the next level. With this we can import, export, merge, or stack data. It can automatically manage all the heavy-lifting that Veronica had to otherwise manually do. We can install it on our Google Sheets by clicking the button below:
Sheetgo add-on
Once done, we can configure to export from Google Sheets multiple data sets subject to the predefined frequency and filter conditions. The export takes place from the current spreadsheet to other spreadsheets – both ours and other users’.
Configuration: Steps to export from Google Sheets
For the purposes of demonstration, we will use four spreadsheets as listed below.
- All Grades – Scores
- Third Grade – Scores
- Fourth Grade – Scores
- Fifth Grade – Scores
We will try and configure auto-export functionality from All Grades – Scores file to the next three files. But before that, Veronica imports the scores data using the procedure explained in this post. Here’s how it looks after importing the data from the CSV file using the Sheetgo Add-on.
Please note that the files corresponding to third, fourth, and fifth-grade files are external files (i.e. other users shared them with edit access control).
We will try with the Third Grade – Scores file first. Below is the sequence of steps to do so.
1. Start Sheetgo and initiate an export link connection
If not already running, navigate to Add-ons > Sheetgo > Start, while we are on the All Grades – Scores file, to start the Sheetgo add-on. Doing so will open the Sheetgo sidebar as similar to the one shown in the second snapshot. Hover the mouse pointer over the green circle with a plus, and click on the Export option.
2. Link source sheet with the destination file that we need to send the data to
Click the Connect option. This will lead us to the list of most recently updated files by default. If we don’t see the file we want, we can look up the file within Google Drive using the search feature. For this example, we select Third Grade – Scores file for ‘Destination spreadsheet’, and then for ‘source sheet’ choose Student_Test_Scores.csv.
We then arrive at the settings screen. Here we can select the frequency of updates in the Automatic update section. We change it to Weekly because Veronica updates the CSV every week. To avoid any confusion with the upcoming connections, let us change the connection name to Third_Grade_Scores.
3. Add the filter condition for selective data export, and create a connection
To do this, we may have to scroll down a bit on the sidebar until we see Filter by section. By default, it shows “No active filter”. Click on edit to add a condition. From the previous image, we realize that the Grade field is in column B. Since we need to create a filter based on Grade, we ensure it is “Column B: Grade” in the first drop-down. And, we make sure that the second and third options are “Number is equal to” and “3” respectively. Then we click on OK.
The Filter by section now shows “Column B number is equal to 3”. Finally, to establish the link that can export multiple data sets, we click CONNECT button.
Doing so, we notice that in a few seconds Sheetgo creates a new connection whose status shows “Last update: Just now”.
Congratulations! We’ve just exported a filtered list of scores specific to the third grade! Let’s check the Third Grade – Scores file to see if the data has flown in.
Yes, it did! We see that Sheetgo creates a new sheet (name will be same as that of the connection), and accordingly puts only Third Grade data in it.
Create other connections
We repeat the above steps to create connections for other files, in order to export multiple data sets, but with a couple of differences.
- We choose Fourth Grade – Scores and Fifth Grade – Scores files to export fourth and fifth-grade specific data.
- The filter criteria for the fourth and fifth-grade files in the Filter by section are “Column B number is equal to 4” and “Column B number is equal to 5” respectively.
Once done, here’s how the list of connections looks like.
Following are the screenshots of the other files with updated grade-specific data.
Thus, we have configured the Sheetgo add-on to export multiple data sets, on a weekly basis, subject to specific grade filter criteria. With this setup in place, all that Veronica has to do now is just update the CSV file. And, she doesn’t have to worry about any other stuff.
