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();
}
On Tuesday, May 20, 2014 at 8:17:49 AM UTC-5, Emil Bryngelsson wrote:
>
> 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 the Google Groups
"Google Spreadsheets API" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
