How to Link Data Sets in Different Google Sheets

Imagine having more than one spreadsheet containing data. You need the information from one spreadsheet to interact with the data from another spreadsheet. Luckily, Google Sheets offers two methods to accomplish this. In this article, I’ll share with you these secret tips on how to link data sets in different Google Sheets.

Using IMPORTRANGE

IMPORTRANGE is a spreadsheet function that allows you to connect one spreadsheet to another. First, get the link of the other spreadsheet, then use the IMPORTRANGE function with that link in quotation marks in your spreadsheet. This imports the data from one spreadsheet into a tab of the other spreadsheet.

IMPORTRANGE(spreadsheet_url, range)

It may seem like the data resides in the secondary spreadsheet, but in reality, it is displayed from the first spreadsheet. This means you cannot modify the data in the secondary spreadsheet. It’s a one-way sharing of data, which is a good thing. You don’t want to have the same data in multiple places as it can cause issues. IMPORTRANGE allows you to perform VLOOKUP searches or even sort the data. You just can’t edit the data in the secondary spreadsheet.

Using Google Apps Script

Another option to get data from one Google spreadsheet to another is to code it. Use the “Tools” menu to open the script editor. Set each spreadsheet as a variable.

SpreadsheetApp.openByUrl(spreadsheet_url);

Set the data sheet as a variable and retrieve the data from that sheet.

function importData() {
  var ssOne = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1wwx3d8nIA-SurqSqSVwuFn-A0jQFI5Xqk');
  var ssTwo = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/10xdV8FN6RlkyAglqkGbVmG6cCeMbX242PqJEpJjnif8');
  var sheet = ssTwo.getSheetByName('data');
  var data = sheet.getDataRange().getValues();
}

Now, you have more flexibility to use this data. You can either dump the entire data set as unlinked values into a new tab (similar to IMPORTRANGE, but not dynamic) or simply call specific parts of the data from the other sheet to use in your spreadsheet, which speeds up loading.

Using a Trigger

Tip: Use a trigger to schedule the import of data from the other sheet to ensure that the data is regularly updated. You can also set it up to import data when you call it.

Now that you know these tips, you can easily link data sets in different Google Sheets. Take advantage of this feature to save time and improve your workflow with Google Sheets.

Don’t forget to check out Crawlan.com for more tips and tricks on Google Sheets.