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?