Google Sheets has changed the way we use spreadsheets. They have become fully collaborative, with multiple people able to contribute to one sheet at the same time—independent of location and device! All we need is a browser with an internet connection and we are good to go. While collaborative web-based tools can certainly make our lives easier, sometimes it can be very difficult to find certain solutions to big problems!
For example, a lot of companies manage and transfer data between spreadsheets manually. This might not be a big issue for companies with small amounts of data that is not time sensitive or updated frequently. But as we know, copy/pasting leaves a lot of room for human error and can leave companies at risk of dealing with outdated data.
People are already loving how Sheetgo allows them to connect their spreadsheets to their colleague’s spreadsheets to send data automatically. A lot of people want to know how they can make data transferring a two-way street, but struggle to find an easy solution. Don’t worry, we’ve got you covered! Today we are breaking down how to connect two Google Sheets files.
Why connect two Google Sheets?
Let’s use a production facility that manufactures canned tomatoes as an example. To be able to produce the planned output, the facility personnel needs to work in separate teams, but their efforts need to be synchronized.
Each team has a specific set of tasks they are responsible for. The facility manager needs to monitor each of their activities at regular intervals. We can have that manager look after a centralized database that automatically distributes data to the facility operating teams.
The teams need to have access to the information, but, also be able to edit it. If a team makes edits to their individual spreadsheet information, that has to be reflected back in the central database, or the plant manager and the teams will be working with different data. We can all imagine what kind of problems that could create!
How to create a database with two-way connections
Depending on your needs you can tailor the below framework to create a customized solution for your company! For the purpose of this demonstration, we will assume the production facility has two different teams—A and B. To create two-way connections, we will need a master database file and separate, individual files for each team.
Have a look at the diagram below. Our “Master Database” is made up of four sheets. The “Initial Database“, the “Current Database“, “Team A Database“, and “Team B Database“.
The initial data is imported into the Current Database. Relevant, filtered data flows from this spreadsheet out to the team database spreadsheets. Finally, that data is updated and flows back into the Current Database.
Follow these simple steps to get set up. Add as many teams or spreadsheets as needed!
1) Create an Initial Database
In order for us to create two-way connections between Google sheets, we need to set up a seed database to kickoff the process. Create two tabs in one sheet. Populate your data into the Initial Database sheet, and simply copy this information into the Current Database.
Once you complete the setup, you will notice that the Initial Database will remain pretty much static, while the Current Database sheet keeps regularly updating with most recent information.
2) Transfer team data to the team databases using Sheetgo
After Sheetgo is installed, you can simply click Add-ons → Sheetgo → Start.
You will see the Sheetgo toolbar on the right side of your sheet. Click the plus sign on the bottom of the toolbar to create the first connection. You will be prompted to add a source. Your input source should be the Current Database tab from your Master Database file.
Next, click Settings. I am naming this connection Team A, and filtering the data so that only Team A data flows from the main database into this team database. Users with a free Sheetgo account will need to refresh connection when they want the sheets to update. Premium users can enable automatic updates and set them on a regular schedule. After you customize your settings, click the check mark on the bottom right side of the toolbar.
Sheetgo will automatically import the specified data in the Team A Database. Next, repeat this procedure for Team B, in a separate spreadsheet.
3) Send the data from the Team Databases back to the Master Database
The first set of connections to send data out to the teams are in place. Now, we need a way to send data back to the master database to complete the loop. The process of doing this is pretty similar to step 2!
Open the Master Database, start Sheetgo, and click the plus sign on the toolbar to create another connection. For this connection, you will choose two data sources: Team A and Team B.
Click Settings, and name your connection Current Database. This connection won’t need any filtering, but if you are a Premium user, you can schedule your Automatic updates now. If your spreadsheets use any special fonts or colors, be sure to enable Transfer formatting. Click the check mark on the bottom
Let’s test it
I’ve made a few changes to both of the team databases. Then, I manually refreshed the connection in the Master Database. The corresponding edits flow back from the Team databases into the Master database file.
There you go, you’ve successfully learned how to connect two Google Sheets. If you want to read more about the extended functionalities that Sheetgo has over IMPORTRANGE, check out this blog post.