Can a Script distinguish IMPORTRANGE N/As due to non-existent Tabs from N/As due to not having access permissions?

Question

I'm creating a sheet that will import and process data from other Sheets. On a daily basis, the user will be given a new document to process with it. I have cell B1 reserved for them to enter the URL of the new document, and a test cell where they will be able to click the "Allow access" button.

This cell contains this simple formula:

    =IMPORTRANGE(B1,"ImaginarySheet!A1")

It produces an N/A error before the user connects the documents, but of course it also produces an N/A error afterwards, because there is no such Tab as ImaginarySheet. If only I could predict with certainty what any of the Tabs in the document will be named, I would use that Tab name instead, but unfortunately I can not, due to a sheer lack of a naming convention on the other end, which I can not even communicate about.

I can clearly see the difference between the pop-up errors before when/if the documents are successfully connected and afterwards, but the user is not the most tech-savvy person, and I would greatly prefer if the document could communicate more clearly whether or not they have successfully granted permissions.

Wrapping the formula in an IFNA or IFERROR won't work because the formula result is N/A regardless of whether the documents are connected. I need some way to distinguish between "docs are not connected/you don't have permissions to connect them" and "docs are connected but there is no such Tab as ImaginaryTab in the import doc." There doesn't seem to be any way to differentiate between the cause of the N/A on the formula level, but I wonder, might there be some way a Script could distinguish between the two, and return a specific result once the docs are connected?

Answer 1

I followed the link Automatically "Allow Access" for IMPORTRANGEs in Google Sheets? StackExchange added to the discussion, and learned something new:

You actually DON'T HAVE TO specify the Tab name in IMPORTRANGE! This formula actually works:

=IMPORTRANGE([URL],"A1")

It imports cell A1 from the leftmost visible Tab in the document at [URL], whichever Tab that happens to be at the time (I just tested this, and found that I could change the formula results if I rearranged the Tabs in the import doc, but I had to cut and paste the IMPORTRANGE formula; refreshing the page actually didn't do it.)

Anyhow, for my purposes, all I have to do is write the formula this way instead. To avoid confusing the user with irrelevant data, I styled this cell with white type so you don't actually see that A1 value, just the pop-up. I have another formula minding this one with an IF(ISNA... to signal the user when they need to connect the docs, and when they have succeeded.

Answer 2

From the question

Can a Script distinguish IMPORTRANGE N/As due to non-existent Tabs from N/As due to not having access permissions?

, but I wonder, might there be some way a Script could distinguish between the two, and return a specific result once the docs are connected?

No, but you could use a script to check if the sheet exists.

var spreadsheet = SpreadsheetApp.openByUrl(url);
var sheet = spreadsheet.getSheetByName('ImaginarySheet');
if(sheet){
  console.log('The sheet ImaginarySheet exists');
} else {
  console.log('The sheet ImaginarySheet doesn't exists')
}

Related

• Automatically "Allow Access" for IMPORTRANGEs in Google Sheets?