When it comes to Google Sheets I am an enthusiastic Novice BUT I have learned a few useful tricks and favorite applications over the years.

What is Importrange?

This function pulls a range of information from one Google Sheet and applies it to another Google Sheet.

For example, when I have students roll dice to attack a boss they can enter the dice roll numbers into their personal Google Sheets which can then be transferred over onto the Boss Sheet.

Student’s Personal Dice Roll Page
The student’s data is transferred over into Column B

Information needed before writing the Importrange Function

To begin the import of data from another sheet you will need several pieces of information.

First – The “location” of spreadsheet that you would like to connect to. This can be found in two ways.

  • The first is to find the the URL address of the and look for the string of letters and numbers within the URL. Its easier just to show you.
  • The second is to cut and paste the entire url address for the target spreadsheet and paste its entirety onto the working spreadsheet.
Notice that the Spreadsheet URL address from the first picture is the same as that found in cell U1

Second – What is the range of data that you would like to transfer from the target spreadsheet to the working spreadsheet. Identify the Target Columns and rows which I think of as an information box. The top left corner of the box will be the first cell number and the bottom right corner will be the second number. All of the data in that box will be transferred into the working document.

Importrange Example

=importrange(spreadsheet_url, range_string)

Example 1: =importrange(“1J8p5T_8gqcuixZ0jn62gD5AHojXRG_9M4-I4J722qCk”,”Scores!B2:B200″)

Example 2: =importrange(U1,”Scores!B2:B200″)

These two examples are connecting my working sheet to the same target sheet. Example 1 is cutting and pasting the URL info while example 2 is referring to the URL that has been pasted in full in cell U1 on the working spreadsheet.

  1. Choose a cell on the working sheet.
  2. Type =importrange to begin the function.
  3. spreadsheet_url is added after the first parenthesis. If it is the string of data from the URL then it needs to be put in Quotation Marks “1J8p5T_8gqcuixZ0jn62gD5AHojXRG_9M4-I4J722qCk“ The quotation marks tell the function where the data begins and ends and that it is the spreadsheet location. OR just type the cell location where you have cut and paste the entire URL address which will not need quotes.
  4. add a comma after the spreadsheet URL
  5. Enter the desired data range. Beginning with the top left cell. add a colon (:), then the bottom right cell.
  6. Close parenthesis.

Clear Space for the data

An error will occur if any cell within importrange data range is not empty. For example, if a 4 by 4 range of cells is imported then the spreadsheet will need to have that 4 by 4 range of cells blank.

Range location

The importrange function will be the top left cell in the import range. It doesn’t matter where the data is in the target spreadsheet, the working sheet will start the information where the importrange function begins.

Giving Permission

The working sheet will need to get permission from the targeted sheet to pull the data. If you own both sheets then simply allow the permission and the documents will be connected. If someone else controls the targeted sheet then that owner will need to grant permission.

Transpose Function

Do you have a horizontal data set that you want to be displayed vertically? simply write =transpose in front of the function.

Example: =transpose(importrange(“1UTCs1oVlOOJQ6nXn5C7Nd-Vaje3KNaAImO7tccVv1Qo”,”grade records!e10:CZ10″))

Classroom Applications

Importrange is great for connecting student spreadsheets to a centralized main sheet. For this reason I like using spreadsheets for Boss Fights. The student’s Dice Rolls, character choices, special attacks, and other customizable information can be completed by the students on their own sheets and automatically update on the Boss Sheet.

Updating leaderboards is also easier with importrange by importing XP Data from a central gradebook style sheet and then do a quick data sort.

Automatically Updated Student Dashboards. I have written about these in the past but if your game has a central scoresheet but you only want students to see their information then the importrange function is a useful tool.