The IMPORTRANGE function allows you to point to another file and extract data from a defined range.
The syntax for the IMPORTRANGE function is:
- spreadsheet_url – The URL of the spreadsheet from where data will be imported. The value for spreadsheet_url must either be enclosed in quotation marks or be a reference to a cell containing the URL of a spreadsheet.
- range_string – A string, of the format “[sheet_name!]range” (e.g., “Sheet1!A2:B6” or “A2:B6”) specifying the range to import. The sheet_name component of range_string is optional; by default, IMPORTRANGE will import from the given range of the first sheet. The value for range_string must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.
Spreadsheets must be explicitly granted permission to pull data from other spreadsheets using IMPORTRANGE.
The first time the destination sheet pulls data from a new source sheet, the user will be prompted to grant permission. Once access is granted, any editor on the destination spreadsheet can use IMPORTRANGE to pull from any part of the source spreadsheet. The access remains in effect until the user who is granted access is removed from the source.
If the data you are trying to import is too large, you may get an error.
Acquiring the Source File’s URL
When working with other Google Sheets files, it is necessary to obtain the URL (Uniform Resource Locator) of the source files to inform IMPORTRANGE as to the location of the data.
With the source file open, highlight and copy the ULR visible in the Address Bar at the top of the browser window.
NOTE: The “edit…” information after the final “/” is not needed by the IMPORTRANGE function.
Writing the IMPORTRANGE Formula
To write the IMPORTRANGE formula, select a cell and type…
The first argument is the URL copied from the previous step. Remember, the URL must be enclosed within a set of double-quotes.
=IMPORTRANGE(“copied URL goes here”,
The second argument is the source sheet’s data location. This information must also be enclosed in double-quotes.
If you do not mention a sheet name, the default is to select the data from the first encountered sheet in the file.
If we wished to import all data from columns A through C, we would write the following…
=IMPORTRANGE(“copied URL goes here”, “A:C”)
By leaving off the row number references we ensure we return all rows with data. If you needed to start at a row deeper in the sheet and stop at a specific row, you could write the formula as follows.
=IMPORTRANGE(“copied URL goes here”, “A10:C100”)