Google-sheets – Calling IMPORTRANGE from within another function

formulasgoogle sheetsimportrange

I have a bunch of separate spreadsheets and I'm creating an overview spreadsheet which pulls data from each of the others. I also need to apply the COUNTUNIQUE formula to that data. If I simply type the following into a cell in the overview spreadsheet

=COUNTUNIQUE(importRange("key","G2:G"))

the formula does not work. It displays a value of 1 and there is no request to access the spreadsheet that the data should be pulled from.

If however I first type importRange("key","G2:G") I get the request to access the spreadsheet, and after I approve it the data populates, and then I can add the COUNTUNIQUE function around the IMPORTRANGE and it will work properly. However I have to do this in a separate sheet as otherwise I get an error that the range can not be imported as it will overwrite other parts of the spreadsheet.

Is there any way around this?

Best Answer

Indeed, the first invocation of importrange cannot be nested within another function: the request for authorization does not "bubble up" (at first I thought this was an error on Google's part, but then considered that a single command can combine several importrange within it, so providing an authorization dialog in such a case would be extra complicated).

But you don't need a separate sheet to handle authorization. Just make the first call to the spreadsheet a single cell request: importRange("key","A1"). Then this can be replaced with the countunique that you want. Or, you can have a column with keys, and another column with a single cell request for authorization purpose.

Related Topic