An underused feature of Google Sheets that I find to be a major reason to use the software over excel (yes I said it) is the IMPORTRANGE() Function. It is a function that doesn’t exist in other spreadsheet platforms and is specifically tied to the collaborative cloud-based capabilities that Google Sheets has to offer. Pass a Google Sheet Url ID and a specific range within that sheet to the function and next thing you know, the content of those cells appear in your current sheet...Wow, how amazing, right? Probably not spectacular or useful at first, but when you see how I found use of this function, then you should be able to understand how this function can carry over into your personal reporting to cut down on data input, keep reporting consistent and expand sheets beyond their limits.
To begin, let me explain how you use the IMPORTRANGE() function. This function uses two values, which are the ID of the Google Sheet that will be imported and the cell range within that sheet which should be displayed. This is very straightforward, but the initial use of the function might confuse users because of the immediate “#REF!” output that is displayed upon submitting the cell value. This value happens by default if access to the sheet that is being imported has not been granted previously. If you click on the “#REF!” you will notice a window appear prompting you to allow access to connect the sheets. Once this access is granted, the values will appear for the same column and row sizes as range that is selected.
Please note that there will be errors if cells are filled in within your sheet that will interfere with the imported cell range. This means that you must reserve cells within your sheet to be used by the imported list. If this is not done, then you will continually run into issues.
Now that you have a grasp of how to use this feature, let’s dive into scenarios that would best fit the use of this function.
1) A Don’t Repeat Yourself (DRY) Situation
There will always be a time when a person requests to see a specific measurement from one report applied to another set of data. While a very minor ask, this request involves either copying and pasting values in a new format or re-pulling the data all together, which is a waste of resources and time. Following a programming practice of not repeating yourself in writing code, the use of IMPORTRANGE() allows you to take a request like the example above and port it into another data set with just one cell. You also have the ability to format the range to fit a new reporting format by using the TRANSPOSE() function or calculations like SUMIFS() and AVERAGEIFS(). By applying this practice when creating new reports, you will quickly find yourself thinking about the data in your other reports and how you might be able to port them into new reports going forward.
2) Aligning Raw Data Sources
You should ALWAYS align the data sources in your reports by the criteria used across your organization. This should be a given, but there are cases where specific members of an organization will not have access to the specific data being used or are unfamiliar with the data cleaning process before applying it in a reporting format. When these scenarios happen, questions will arise about data integrity and cause confusion. The best solution to avoiding this problem is to provide a universally accessible Google Sheet with commonly used data points that can be used by others via the IMPORTRANGE() function.
In my past experiences, an use case for this type of solution would be with session level data from Google Analytics. Not all teams are familiar with the platform and in every situation where I have used the data, I have worked with detailed segments and filters to clean the information. From this experience, I have found it easier for me to provide these session related numbers to those who request it rather than have them pull what they think is right. As a result, the easiest way to provide this information is by having a sheet that I had ownership of and maintained and then provided access to it to those who request the data with the IMPORTRANGE() formula to easily port their requested data into their spreadsheet.
3) Reaching the Cell Limit and Connecting Sheets
You might not know this, but Google Sheets limits spreadsheets to a maximum of 2,000,000 cells. I have hit this limit before and I can’t begin to tell you the anxiety that I had developed when thinking about the process of replicating the sheet and then determining a way to connect it to the previous report to ensure consistency in my reporting. However, after considering the DRY principle, I thought about how IMPORTRANGE() would be the perfect solution to my problem. If I created a copy of my maxed out notebook using the same formulas, replicated the format of these workbooks and then used the IMPORTRANGE() function to tie in both sheets, then I would be able to maintain data consistency without the cell limitations and keep the report format unchanged.
Tip for Advanced Users: Think about using INDIRECT() to automate the process of importing new workbooks. I will make sure to talk about this function in a future post.
As I have pointed out with my three examples, the IMPORTRANGE() function that is unique to Google Sheets is extremely powerful in utilizing the platforms cloud-based collaborative functionality. While it is only a two value function, the ability to connect, manipulate and indirectly automate processes makes it more powerful than almost any out of the box spreadsheet function. When you build out your next report, think about the data that is required and if it exists in any other sheets and if it does then try this function and truly learn the power this tool has to offer for your unique reporting scenarios.