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


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?


1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.