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:
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?