Google Sheets needs no introduction. The Cloud-based spreadsheets application made our work lives so easy. Previously, organizing and sharing the information files was a problem to reckon with. But, we can access Google Sheets from anywhere and on any device.
We can even share the files with other users with appropriate access controls, who can then work on them either to use the existing information or add to it. Thereby, taking collaborative efforts to the next level. But not everything within the application is seamless and certainly, there is room for further advancements.
While we can use the IMPORTRANGE formula to link Google Sheets data automatically, it is not as advanced and flexible as the solution we are going to try here. Therefore a great alternative to link data is to follow the step-by-step instructions on how to link Google Sheets.
Why should Google Sheets link?
Data exchange between colleagues on a day to day basis can get tiring and time-consuming when done manually. Looking for ways to automate this process will help them save more valuable time over time. An example of a use case is that of Rafael.
Rafael works as a data analyst in a processed-food supply company that uses Google Sheets for their spreadsheet needs. Kirk is a research assistant who reports to Rafael. Rafael’s job requires him to analyze the end-customer survey data he receives from Kirk. Afterwards, Rafael draws insights and shares them with his management. There are two different spreadsheet related tasks in this entire process
- Rafael needs to access the Customer Survey Data file from Kirk.
- He also needs to share the Customer Insights file to his management.
In any other time, it would have to be that Rafael needs to exchange these files over emails. It can get tiring for Rafael if he were to do this information exchange manually every single day. Consequently, if there is any way if this can be done on auto-pilot, he is up for it.
Solution: Automate the process with Sheetgo
Thanks to Sheetgo, we, fortunately, have a much simpler and automatic alternative that makes information exchange happen without manual intervention. This add-on for Google Sheets highly boosts the connectivity between spreadsheets. It can automatically manage all the heavy-lifting that Rafael had to manually do (explained above). If we don’t have it yet, we can get it by clicking the button below.
Rafael usually uses a temporary file called Staging to import the data, perform analysis, and once done, uses the same file to export his insights.
I. How to link Google Sheets automatically by creating an import connection?
This is the first leg of the process in which we will link Google Sheets data automatically, wherein Rafael imports the survey data from Kirk. Please note that it is a pre-requisite that Kirk grant Rafael necessary read access permissions for this link to work.
Having installed the Sheetgo add-on, a new Google Sheets will automatically be opened. This will be our Staging file, so name it accordingly.
While your “Staging” file is open, navigate to the Menu Add-ons > Sheetgo > Start.
This will automatically open a sidebar on the right side of your screen, as seen on the image below. Click on the green + button to start creating the connection.
Clicking the Select file(s) button leads us to the list of most recent files by default. If you don’t immediately find the required file, navigate to the search bar to look it up in your Google Drive. Find and select the Customer Survey Data as your Data source. Kirk shared this file with Rafael, hence he can see this on his Google Drive.
Select Sheet1 as source tab. This is where we want to import the data from.
We then arrive at the Settings tab. Here you can adjust the frequency settings to automate the import on a scheduled basis. Enable the Automatic updates button and leave it at ‘Daily‘, which is the default value. we will also change the connection name to Survey_Data_Import to avoid any confusion.
Finally, to establish a connection that allows us to automatically import data from the Customer Survey Analysis file to the Staging file, click on SAVE CONNECTION.
Staging file after importing survey data:
Now, Rafael will derive the insights as to how each food category is doing in terms of customer satisfaction. Here’s the result of his analysis. He will use the Sheet1 of Staging file to generate insights for the purposes of convenience, as shown below.
Staging file after deriving insights:
II. How to link data automatically by creating an export connection?
The final step for Rafael is to export these data insights to his management. To do so, the steps are similar to the ones above that explained how to import to the Staging file. For this connection to work, on the Customer Insights file, they have granted edit access permissions to Rafael for him to be able to set up an export link that will bring the data from his Staging file.
- While the Google Sheets file Customer Insights is open, navigate to the menu Add-ons > Sheetgo > Start.
- Click on the green + button to create a connection with the “Staging” file.
- Click on the Select file(s) button and choose the Staging file as your Data source. Select the Sheet1 for source sheet. This is where the data is at that will be exported to the Customer Insights file.
- Just like our first connection, in the Settings section, enable the Automatic updates button and leave it to the default value Daily. Change the connection name to Insights_Export.
- Finally, to establish the connection between the Staging and the Customer Insights files, click on SAVE CONNECTION.
Now, Rafael automatically receives the Customer Survey Data and also automatically sends his insights to the management.
Customer Insights file that management sees:
We just created an automated Google Sheets link between 3 different spreadsheets.
How to schedule automatic updates?
The data import and export keeps happening automatically and periodically once the appropriate connections are in place. Please note that we can change the frequency with which these happen or even turn off the automatic updates 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 either “Survey_Data_Import” or “Insights_Export”) > 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 the SAVE CHANGES button.