Collaboration is the cornerstone of today’s spreadsheets, and Google Sheets has been evolving around that ideology. There’s a small catch though. Using the out of the box Google Sheets application, we can readily share an entire spreadsheet with another user. But, it is not possible to share a specific subset of information from it. We do have the IMPORTRANGE function which we can use to import specific range from another spreadsheet. But we cannot just do the opposite. The reason is that, currently, there is no native Google Sheets functionality that lets us export a range of data to another spreadsheet.
Why export range to another spreadsheet?
Amanda is one of the support analysts from the team that registers complaints from users of a CRM software application. At the end of the day, her job requires her to report the list of incidents to her management, who will then analyze which part of the application is posing what kind of problems. Here is how her typical issue tracking spreadsheet looks like.
The critical information that her management needs come from columns B, F, and G. The client information is not as important. Therefore Amanda doesn’t need to share the data from the columns A, C, D, and E.
Since there is no direct way to eliminate unnecessary information, to date, Amanda has been doing the following.
- Make a copy of the Issue Tracker spreadsheet
- Remove columns A, C, D and E
- Share the copied spreadsheet with her management in which the remainder of the information available.
While doing that helps the situation, it is a repetitive manual process, which fortunately can be averted by making use of the Sheetgo add-on.
Solution: Automatically export range data using Sheetgo add-on
Sheetgo add-on for Google Sheets boosts the connectivity of spreadsheets to the next level. It can automatically manage all the heavy-lifting that Amanda had to manually do and even more. Sheetgo allows us to perform all these functions. If not already installed, we can get it by clicking the button below.
Sheetgo add-on
With this extremely handy tool, we can configure Google Sheets to automatically export a range of data (and subject to predefined conditions if need be!). Not only that, we can even configure the frequency at which these exports can happen – hourly, daily, weekly or monthly. The export takes place from the current spreadsheet to other spreadsheets – both ours and other users’.
Configuration: Steps to export range automatically with Sheetgo
For the purposes of illustration, we are going to export data range from the Issue Tracker file to Issues – Master Database file. For this to happen, it is a pre-requisite that Amanda’s management grants her write access permissions to the Issues – Master Database file, without which, this automation won’t work.
1. Start Sheetgo and initiate a connection
Now that we have Sheetgo installed on our Google Sheets application, we now open the Issues – Master Database file. To start the add-on, we navigate to the Menu Add-ons > Sheetgo > Start.
Doing so will open the Sheetgo interface on the screen as a sidebar. Click on the green ‘+‘ sign to create the connection with the “Issue Tracker” file.
2. Link destination file with the source sheet we need the data from
Clicking the Select file(s) button leads us to the list of most recently updated files by default. If we don’t see the file we want, we can use the search module to lookup the file within Google Drive. For this example, we select Issues Tracker file as the data source, and then for source sheet Sheet1.
We then arrive at the Settings. Here we can select the frequency of updates in the Automatic update section. We enable this button and leave it to the default option, Daily because Amanda needs to send data from Issue Tracker file to the Issues – Master Database file every day.
Note that, in case the management wants that the data be preserved rather than refreshed every day, Amanda could go with the Append option.
3. Add the filter condition for selective data import, and create a connection
To do this, we scroll down a bit on the sidebar until we see the Filter by section. Enable this button and choose the option filter by Query Language and key in the following statement:
SELECT B, F, G
The Filter by section now shows filter by Query “SELECT B, F, G”.
Finally, to establish a link that can automatically export filtered data, we click the SAVE CONNECTION button. Doing so, we notice that in a few seconds Sheetgo creates a new connection whose status shows “Last update: Just now”.
Congrats!
We see that Sheetgo created a new sheet (name will be same as that of the connection) in the Issues – Master Database file, and accordingly pulls only B, F, and G column data from the Issue Tracker file.
Automatic Updates
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 setting altogether. Shown below is how we navigate to the corresponding settings pane.
Click on the 3 dots next to the connection name (in this case “SG_Sheet1”) > edit (the pencil icon) > Automatic update.
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 on SAVE CHANGES.
Read more about the differences between IMPORTRANGE and Sheetgo functionalities in the following blog post.
