Calling IMPORTRANGE from within another function

Question

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?

Answer 1

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.