Spreadsheet upon spreadsheet: Torn Apart, but Forever Linked #
When using Google Sheets, there is a function embedded that will allow you to link specific parts of some spreadsheets together. This can be really useful for incorporating multiple datasets from different spreadsheets, and incorporating them into one.
However, trouble can arise when one spreadsheet gets deleted, or the user that owns one of the spreadsheets gets deleted. It’s important to be able to keep track of where the different parts of the data are kept.
GAT+, our powerful Audit Tool for Google Workspace, can help you find these spreadsheets and ensure that they are not lost with its scheduled reporting feature.
First off, let’s explore how the spreadsheet import feature itself works with the following example. If you are already familiar with this process, skip to Part 2.
Part 1: Linking Spreadsheets together #
Both Olivia and the members of her team have a shared To-Do list. Olivia also has her own personal To Do list. In this example, we will incorporate Olivia’s To Do list spreadsheet into the shared spreadsheet that her team uses , so that the tasks Olivia adds are automatically reflected on her teams To Do list.
Step 1: Prepare Your Source Spreadsheet
-
Open the Google Sheets file that contains the data you want to import (the source spreadsheet – Olivia’s To Do list).
-
Copy the entire URL from your browser’s address bar. You’ll need this URL for the formula.
From this spreadsheet, we will highlight the range of cells that we want to import. In this case, it’s just some of the direct information on the To Do list we want. So we highlight only that information for our range.
The only other thing we need to note here is the sheet name.
Now, let’s open up another spreadsheet, this time it’s the Teams’ To Do list.
Step 2: Using the ‘Import Range’ function
We need to link the two together. Start by typing =IMPORTRANGE into the selected cell
The syntax for the IMPORTRANGE function is:
=IMPORTRANGE("spreadsheet_url", "range_string")
-
"spreadsheet_url": The URL you copied from the source spreadsheet, in quotation marks. -
"range_string": The specific sheet and cell range you want to import - The
"range_string"is comprised of the sheet name and the range, separated by an exclamation mark, enclosed inside quotation marks"SheetName!A1:B10"
Our finished formula looks like this:
When we press the ‘Enter’ key, a new message appears.
Click Allow access, and you should see the data from the source spreadsheet (Olivia’s To Do List), reflected in the shared spreadsheet (The Team’s To Do list).
When you do click ‘Allow access’ something else notable happens in the background. An event that these two spreadsheets have been linked is registered.
GAT+ will notice that this event was created, and it can isolate all the files that have this event logged against them. We will find out how to uncover those events and find which spreadsheets have been linked together next.
Part 2: Find the Google Spreadsheet in GAT+ #
Part 3: Don’t lose your linked spreadsheets with the scheduled report #
The list above gives us all the spreadsheets that are linked and the file ID’s. But you might like to note these so that we don’t lose them going forward.
Go back to your filter menu, and select the ‘Scheduled’ checkbox.
This will deliver a report on a set frequency to have an up to date list of spreadsheets where this function is used and updated.
You can have this list automatically upload to your GAT folder in your My Drive, and you can receive an email about it too.
This will give you time to act. Once you see that there are documents linked, and they are in a users My Drive, you can either ask the user to move them to a shared drive, (to ensure they are not deleted) or you can filter for these files by the File ID in GAT+, should they happend to get moved or misplaced.
Fun Fact: the Google Admin console only keeps your file events for the previous 180 days, but the file events log can is retained indefinitely, as long as you hold a GAT Labs subscription.
You can find more information on creating a scheduled report here.
