Previous
Next

3. Visulising your Data

3.4. Googlesheets Function: IMPORTRANGE

Using Google Sheets: Importing Data with =IMPORTRANGE

This guide explains how to use the IMPORTRANGE function to bring data from one Google Sheet into another. This is incredibly useful for consolidating information, sharing specific data sets without granting access to the entire source sheet, and creating dynamic reports.

What is =IMPORTRANGE?

IMPORTRANGE allows you to pull data from a different Google Sheet into your current sheet. The imported data is live; if the source sheet changes, your sheet will automatically update. However, you cannot directly edit the imported data in the destination sheet. Changes must be made in the original source sheet.

Syntax:

=IMPORTRANGE("spreadsheet_url", "range")

Let's break down each part:

  1. =IMPORTRANGE: This is the function name that tells Google Sheets you want to import data.
  2. "spreadsheet_url": This is the full URL of the source Google Sheet (the one containing the data you want). Make sure to enclose the URL in double quotes. You can find the URL in your browser's address bar when you have the source sheet open. It will look something like this: 
    "https://docs.google.com/spreadsheets/d/[some_long_alphanumeric_code]/edit#gid=0"

    Crucially, you must include the entire URL, including the https://... part.

  3. "range": This specifies the cells you want to import. Use standard A1 notation (e.g., "A1:D10", "Sheet1!A1:B5", "Sheet2!C2:C"). Enclose the range in double quotes. For example:
    • "A1:D10" imports data from cells A1 to D10.
    • "Sheet1!A1:B5" imports data from cells A1 to B5 on the sheet named "Sheet1". This is important if your source sheet has multiple tabs.
    • "C2:C" imports all data from column C, starting from C2.

Step-by-Step Example:

  1. Open the Source Sheet: Open the Google Sheet that contains the data you want to import.
  2. Copy the URL: Copy the entire URL from your browser's address bar.
  3. Open the Destination Sheet: Open the Google Sheet where you want to import the data.
  4. Enter the Formula: In the cell where you want the imported data to begin, type =IMPORTRANGE("spreadsheet_url", "range"), replacing "spreadsheet_url" with the URL you copied and "range" with the desired cell range. For example: 
    =IMPORTRANGE("https://docs.google.com/spreadsheets/d/[some_long_alphanumeric_code]/edit#gid=0", "Sheet1!A1:C20")
  5. Authorize Access: The first time you use IMPORTRANGE with a new source sheet, you'll need to grant permission. A message will appear asking you to "Allow access." Click the "Allow access" button. This authorization is a one-time process for each source sheet.
  6. Data Appears: Once authorized, the data from the specified range in the source sheet will appear in your destination sheet.

Important Considerations:

    • Authorization: You only need to authorize access once per source spreadsheet.
    • Updates: The imported data will automatically update whenever the source sheet is changed.
    • Errors: If you see an error, double-check the URL and range. Also, ensure that you have the necessary permissions to access the source sheet (either you own it or it has been shared with you).
    • Multiple Ranges: To import multiple non-contiguous ranges, you can use curly braces {}. For example: =IMPORTRANGE("url", "{Sheet1!A1:B10, Sheet1!D1:E10}")
    • Sheet Names: Remember to include the sheet name in the range if you are not importing from the first sheet in the source spreadsheet.

 

Ways you might want to use this:

Consolidating Pupil Attainment Data

A teacher with multiple classes (e.g., S1, S2, S3) uses separate spreadsheets for each class, recording CfE levels, assessment results, etc. They create a master spreadsheet and use IMPORTRANGE to pull data from each class spreadsheet for consolidated tracking and reporting.

Tracking Pupil Progress Across Subjects

A Guidance Teacher tracks pupil progress across subjects. Each subject teacher has their own spreadsheet. The Guidance Teacher uses IMPORTRANGE to import pupil data (CfE level, assessment scores, teacher comments) into a central spreadsheet for a holistic view of pupil development.

Creating a Performance Overview

A teacher or Principal Teacher creates an overview of class/year group performance (CfE levels, average scores, progress). Raw data is in separate class spreadsheets. IMPORTRANGE imports the data into the overview spreadsheet for calculating and visualising performance metrics.

Sharing Data with Parents

A teacher shares a pupil's progress report with parents. The full class spreadsheet is confidential. The teacher creates a separate spreadsheet, using IMPORTRANGE to pull only the specific pupil's information, which is then shared with parents.

Collaborating on Pupil Data

Teachers in a faculty collaborate on pupil data. One teacher maintains the master spreadsheet. Others use IMPORTRANGE to import specific data ranges into their own spreadsheets for analysis, without needing direct access to the master sheet.

Generating Reports

A teacher or Principal Teacher generates reports for the SMT or Education Scotland (attainment data, progress, interventions). Raw data is in their spreadsheet. IMPORTRANGE imports the data into a reporting spreadsheet for calculating report statistics.

Tracking Attendance

A teacher combines attendance data (from SEEMIS) with other data. IMPORTRANGE brings attendance information into a spreadsheet alongside attainment data, enabling analysis of the relationship between attendance and attainment.

Tracking ASN Information

A teacher or support for learning teacher maintains a spreadsheet with ASN information. IMPORTRANGE imports relevant ASN information into class tracking spreadsheets for teachers to easily access and consider when planning learning and teaching.

 

You may want to watch this tutorial

 

 

Previous
Next