Error with importrange and filter views

[Emil Bryngelsson]

I have a google sheets spreadsheet that makes use of importrange to display
data from another sheet in the same document.

The formula for using importrange is this

=IMPORTRANGE(M1;"Datasheet!G2:G300")

The cell M1 contains the URL to the document itself, and the reason I put
it in one cell is that I'm reusing that same URL in many columns, and it is
more readable to not have the whole URL in each formula.


However, when activating a filter view, I get an REF# ERROR for example
*Error:* The formula in cell M1 is referencing a relative range outside the
filter.


And nothing is displayed.

It works fine when not having an active filter view.

It ALSO WORKS FINE when the URL is used directly in the importrange
formula, instead of using M1 and having the URL in cell M1.

What is the difference and why does this happen? These cases should be
identical. It's a ridiculous error.

[Harold Kyle]

+1

Having the same error by sorting a filter view when one column is simply a
cell reference to another sheet in the same document.

Formula:
='designs'!A31

Error:
The formula in cell 'designs'!A31 is referencing a relative range outside
the filter.

This is true when the referenced cell is simply a value (but just in
another sheet). Sorting works fine in the regular Filter but breaks with
#REF in a filter view.

[Fernando Martinelli]

Hi Harold,

Post by Harold Kyle
+1
Having the same error by sorting a filter view when one column is simply a
cell reference to another sheet in the same document.
='designs'!A31
The formula in cell 'designs'!A31 is referencing a relative range outside
the filter.

I keep getting this same error. Did you sort it out? Or any
alternatives?

Thanks so much!
Fernando

Post by Harold Kyle
This is true when the referenced cell is simply a value (but just in
another sheet). Sorting works fine in the regular Filter but breaks with
#REF in a filter view.

[Bob Puffer]

This is not my code but was posted on the web. Wish I could remember by
whom so I could give them credit. The first time it runs you'll have to
authorize:

function myImportRange(key ,sheetrange) {
var shra = sheetrange.split("!") ;
if (shra.length==1) shra[1]=shra[0], shra[0]="";

var sheetstring = shra[0].replace( /^'(.*)'$/g , "$1") // was: replace(
/'/g , "") ; updated 2011-07-17 (ahab)
var rangestring = shra[1]

var source = SpreadsheetApp.openById( key )
if ( sheetstring.length==0 ) sheet = source.getSheets()[0] ;
else sheet = source.getSheetByName( sheetstring ) ;

return sheet.getRange( rangestring ).getValues();
}

Post by Emil Bryngelsson
I have a google sheets spreadsheet that makes use of importrange to
display data from another sheet in the same document.
The formula for using importrange is this
=IMPORTRANGE(M1;"Datasheet!G2:G300")
The cell M1 contains the URL to the document itself, and the reason I put
it in one cell is that I'm reusing that same URL in many columns, and it is
more readable to not have the whole URL in each formula.
However, when activating a filter view, I get an REF# ERROR for example
*Error:* The formula in cell M1 is referencing a relative range outside
the filter.
And nothing is displayed.
It works fine when not having an active filter view.
It ALSO WORKS FINE when the URL is used directly in the importrange
formula, instead of using M1 and having the URL in cell M1.
What is the difference and why does this happen? These cases should be
identical. It's a ridiculous error.

[Fernando Martinelli]

Hi Bob,

many thanks! I forgot to say that I also have this error when using a
filter view AND using the sheet name in the Importrange formula. Is this
solution still going to work for me?

Thanks!
Fernando

Fernando

Post by Bob Puffer
This is not my code but was posted on the web. Wish I could remember by
whom so I could give them credit. The first time it runs you'll have to
function myImportRange(key ,sheetrange) {
var shra = sheetrange.split("!") ;
if (shra.length==1) shra[1]=shra[0], shra[0]="";
var sheetstring = shra[0].replace( /^'(.*)'$/g , "$1") // was: replace(
/'/g , "") ; updated 2011-07-17 (ahab)
var rangestring = shra[1]
var source = SpreadsheetApp.openById( key )
if ( sheetstring.length==0 ) sheet = source.getSheets()[0] ;
else sheet = source.getSheetByName( sheetstring ) ;
return sheet.getRange( rangestring ).getValues();
}

Post by Emil Bryngelsson
I have a google sheets spreadsheet that makes use of importrange to
display data from another sheet in the same document.
The formula for using importrange is this
=IMPORTRANGE(M1;"Datasheet!G2:G300")
The cell M1 contains the URL to the document itself, and the reason I put
it in one cell is that I'm reusing that same URL in many columns, and it is
more readable to not have the whole URL in each formula.
However, when activating a filter view, I get an REF# ERROR for example
*Error:* The formula in cell M1 is referencing a relative range outside
the filter.
And nothing is displayed.
It works fine when not having an active filter view.
It ALSO WORKS FINE when the URL is used directly in the importrange
formula, instead of using M1 and having the URL in cell M1.
What is the difference and why does this happen? These cases should be
identical. It's a ridiculous error.
--

You received this message because you are subscribed to a topic in the
Google Groups "Google Spreadsheets API" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/google-spreadsheets-api/z7K7iL5kFTA/unsubscribe
.
To unsubscribe from this group and all its topics, send an email to
For more options, visit https://groups.google.com/d/optout.

[Andrejs Anna Muizulis]

Hei! I also has this error but this fixed this: I typed the WEB ADDRESS of
the source sheet, like this:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174","'Sheet1'!A1:B25")


втПрМОк, 28 Оюля 2015 г., 21:49:53 UTC+3 пПльзПватель Fernando Martinelli

Post by Fernando Martinelli
Hi Bob,
many thanks! I forgot to say that I also have this error when using a
filter view AND using the sheet name in the Importrange formula. Is this
solution still going to work for me?
Thanks!
Fernando
Fernando

Post by Bob Puffer
This is not my code but was posted on the web. Wish I could remember by
whom so I could give them credit. The first time it runs you'll have to
function myImportRange(key ,sheetrange) {
var shra = sheetrange.split("!") ;
if (shra.length==1) shra[1]=shra[0], shra[0]="";
var sheetstring = shra[0].replace( /^'(.*)'$/g , "$1") // was: replace(
/'/g , "") ; updated 2011-07-17 (ahab)
var rangestring = shra[1]
var source = SpreadsheetApp.openById( key )
if ( sheetstring.length==0 ) sheet = source.getSheets()[0] ;
else sheet = source.getSheetByName( sheetstring ) ;
return sheet.getRange( rangestring ).getValues();
}

Post by Emil Bryngelsson
I have a google sheets spreadsheet that makes use of importrange to
display data from another sheet in the same document.
The formula for using importrange is this
=IMPORTRANGE(M1;"Datasheet!G2:G300")
The cell M1 contains the URL to the document itself, and the reason I
put it in one cell is that I'm reusing that same URL in many columns, and
it is more readable to not have the whole URL in each formula.
However, when activating a filter view, I get an REF# ERROR for example
*Error:* The formula in cell M1 is referencing a relative range outside
the filter.
And nothing is displayed.
It works fine when not having an active filter view.
It ALSO WORKS FINE when the URL is used directly in the importrange
formula, instead of using M1 and having the URL in cell M1.
What is the difference and why does this happen? These cases should be
identical. It's a ridiculous error.
--

You received this message because you are subscribed to a topic in the
Google Groups "Google Spreadsheets API" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/google-spreadsheets-api/z7K7iL5kFTA/unsubscribe
.
To unsubscribe from this group and all its topics, send an email to
For more options, visit https://groups.google.com/d/optout.

[Fernando Martinelli]

MANY MANY thanks for sharing this Andrejs!!!

Fernando

Post by Andrejs Anna Muizulis
Hei! I also has this error but this fixed this: I typed the WEB ADDRESS of
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174","'Sheet1'!A1:B25")
втПрМОк, 28 Оюля 2015 г., 21:49:53 UTC+3 пПльзПватель Fernando Martinelli

Post by Fernando Martinelli
Hi Bob,
many thanks! I forgot to say that I also have this error when using a
filter view AND using the sheet name in the Importrange formula. Is this
solution still going to work for me?
Thanks!
Fernando
Fernando

Post by Bob Puffer
This is not my code but was posted on the web. Wish I could remember by
whom so I could give them credit. The first time it runs you'll have to
function myImportRange(key ,sheetrange) {
var shra = sheetrange.split("!") ;
if (shra.length==1) shra[1]=shra[0], shra[0]="";
var sheetstring = shra[0].replace( /^'(.*)'$/g , "$1") // was: replace(
/'/g , "") ; updated 2011-07-17 (ahab)
var rangestring = shra[1]
var source = SpreadsheetApp.openById( key )
if ( sheetstring.length==0 ) sheet = source.getSheets()[0] ;
else sheet = source.getSheetByName( sheetstring ) ;
return sheet.getRange( rangestring ).getValues();
}

Post by Emil Bryngelsson
I have a google sheets spreadsheet that makes use of importrange to
display data from another sheet in the same document.
The formula for using importrange is this
=IMPORTRANGE(M1;"Datasheet!G2:G300")
The cell M1 contains the URL to the document itself, and the reason I
put it in one cell is that I'm reusing that same URL in many columns, and
it is more readable to not have the whole URL in each formula.
However, when activating a filter view, I get an REF# ERROR for example
*Error:* The formula in cell M1 is referencing a relative range
outside the filter.
And nothing is displayed.
It works fine when not having an active filter view.
It ALSO WORKS FINE when the URL is used directly in the importrange
formula, instead of using M1 and having the URL in cell M1.
What is the difference and why does this happen? These cases should be
identical. It's a ridiculous error.
--

You received this message because you are subscribed to a topic in the
Google Groups "Google Spreadsheets API" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/google-spreadsheets-api/z7K7iL5kFTA/unsubscribe
.
To unsubscribe from this group and all its topics, send an email to
For more options, visit https://groups.google.com/d/optout.

--

You received this message because you are subscribed to a topic in the
Google Groups "Google Spreadsheets API" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/google-spreadsheets-api/z7K7iL5kFTA/unsubscribe
.
To unsubscribe from this group and all its topics, send an email to
For more options, visit https://groups.google.com/d/optout.