Linking Multiple Google Sheet

Most people in operations use spreadsheets for smoothly run their processes, such as budget planning at the year end. They also deal with multiple teams or organizations and hence in order to maintain they need to have a separate spreadsheet for each organization. While this solves the security problem, it makes it difficult to get a consolidated view of the data across the teams.

Google sheets users with advanced skills tend to use the IMPORTRANGE feature which allows importing data from one spreadsheet into another. However, this can pose certain performance challenges.

First, let's take a concrete example. Say a product operation person is working with her counterpart in the finance department and trying to provide the budget across multiple product teams. She would work with the finance person and come up with a template of a spreadsheet that contains all the information that needs to be provided by individual product teams. This information can contain such information as the quarterly head-count requirements, travel expenses, training and other R&D expenses. All of these are organized into multiple sheets within a single spreadsheet in the template. Then she would copy the template and create one spreadsheet for each team and name them appropriately, such as "Team 1 FY24 budget planning", "Team 2 FY24 budget planning" and so on. Then she would provide edit access to to the approprate people to each of the spreadsheets (and hopefully doesn't do it incorrectly that would let one team to view the planning of the other).

Then in order to have a consolidated view of the data, she would have to create yet another spreadsheet and then use the IMPORTRANGE function to import data from each of the other spreadsheets. Given the complexity of the templates each capturing multiple pieces of information such as head-count and travel expenses, the IMPORTRANGE function may have to be used multiple times for each spreadsheet.

While this approach works, the one big challenge is the performance. Google itself says on their support page

"Reference your data on the same spreadsheet you work on. This is faster than Import functions, such as:

IMPORTRANGE

IMPORTDATA

IMPORTXML

IMPORTHTML"

As such there is no easy way to workaround this. As complex processes are executed using Google Sheets together with complex reporting requirements, the end result can be a disaster.

Our product, Sheets BI provides the ability to decouple the data entry requirements from data analysis and reporting requirements. Sheets BI has the ability to download data from any Google Sheets that a user has access to (readonly is fine) and use that data to create powerful reports and dashboards that are computed right within the browser and the data never leaves the browser. Users can create powerful visualizations such as pivot tables and advanced charts and display them into a dashboard and slice and dice the data.