Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Linking Data Sets in Different Google Sheets

Linking Two Google Sheets
Linking Data Sets in Different Google Sheets

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

 

 

 

 

© 2024 All Rights Reserved.

💥 FREE OTIS WORKSHOP

Join Alice Keeler, Thursday Mar 21st or register to gain access to the recording.
Create a free OTIS account.

Join Alice Keeler for this session as we learn how to revolutionize your assessment strategies. We will dive into the essentials of crafting high-quality rubrics that go beyond traditional scoring methods to offer rich, meaningful feedback. We’ll learn about the components of an effective rubric and how to mathematically ensure the accuracy of your assessments.

Exit this pop up by pressing escape or clicking anywhere off the pop up.