MANY MANY thanks for sharing this Andrejs!!! Fernando
2015-08-08 10:05 GMT-03:00 Andrejs Anna Muizulis <[email protected]>: > 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 > написал: >> >> 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 >> >> 2015-07-28 13:13 GMT-03:00 Bob Puffer <[email protected]>: >> >>> 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 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 >>> [email protected]. >>> 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 > [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- 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.
