Linking Two Google Sheets

Updating One Google Sheets Spreadsheet From Another

You have more than one spreadsheet of data. You need the information from one spreadsheet to interact with the data in another spreadsheet. There are 2 ways to do this in Google Sheets. Copying and pasting from one spreadsheet to another has the problem that if the data in the other spreadsheet is updated then you are using old data in the related spreadsheet.

IMPORTRANGE

IMPORTRANGE is a spreadsheet function that allows you to connect one spreadsheet to another. Get the link to the other spreadsheet and put it in quotations in your spreadsheet using the IMPORTRANGE function. This puts the data from one spreadsheet into a tab in the other spreadsheet.

IMPORTRANGE(spreadsheet_url, range_string)

Note that it looks like the data is living in the secondary spreadsheet but really it is displayed from the first sheet. This means you can not edit the data in the secondary sheet. This is one directional sharing of data. This is good, you do not want the same data in multiple places, that causes trouble. IMPORTRANGE allows you to do a VLOOKUP or even sort the data. You just can not edit the data in the secondary spreadsheet.

Google Apps Script

Another option for obtaining data from one Google Sheet to another is coding it. Use the Tools menu to open the Script Editor. There define each spreadsheet with a variable.

SpreadsheetApp.openByUrl()

Google Apps Script Two Spreadsheets

Define the sheet with the data with a variable and call up the data on 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 as to what you want to do with that data. You can drop the whole data set as unlinked values into a new tab (similar to IMPORTRANGE but without it being dynamic) or just call up specific pieces of data from the other sheet to utilize in your spreadsheet, making it faster to load.

Trigger

Tip to use a trigger to schedule when to import the data from the other sheet so as to ensure that data is updated regularly. You could instead code it to import data when you call it up.

Edit menu current project triggers

 

 

 

 

WP Twitter Auto Publish Powered By : XYZScripts.com
%d bloggers like this: