I am currently writing a Google Apps Script inside a Google Sheet to read data from a list of spreadsheets (spreadsheet url is provided by the user). However, I cant seems to find a way to check if the url is valid or if user have access to the spreadsheet or not before calling SpreadsheetApp.openByUrl().

I have written the following code to "validate" the url:

for(int i = 0; i < urls.length; i  ) {
  let spreadsheet = null
  try {
    spreadsheet = SpreadsheetApp.openByUrl(urls[i]);
  } catch (e) {
    continue;
  }

  // Continue to do other stuff to read data from spreadsheet...
}

This however has an issue, it was able to catch the first few 'You do not have permission to access the requested document.' exception. But after a certain number of exception had occur, I would get a permenant error that cant be caught, stopping the script all together.

Is there a better way to do this?

Minimal reproducible example:

1. Create 3 google sheet using different google account
2. Using a different google account, create a google sheet and add the following code into Code.gs

function myFunction() {
  // Put any 3 real spreadsheet url that you do not have access to
  let urls = [
    "https://docs.google.com/spreadsheets/d/1gOyEAz0amm4RghpE4B7f26okU3PG3vWZkrfiC-SBlbw/edit#gid=0",
    "https://docs.google.com/spreadsheets/d/1Oia7ADu5BmYroUq1SLyDMHTJowrwSXOhCEyNO3nXmMA/edit#gid=0",
    "https://docs.google.com/spreadsheets/d/1HE_IXURpBr_FJN--mwLo6k9gih07ZEtDGBqYSk6KgiA/edit#gid=0",
  ]
  urls.forEach(url => {
    try {
      SpreadsheetApp.openByUrl(url)
    } catch (e) {
      console.log("Unable to open this spreadsheet")
    }
  })
}

function onOpen() {
  SpreadsheetApp.getUi().createMenu("Test").addItem("myFunction", "myFunction").addToUi()
}

3. Run the function once in the apps script panel and authorize the application
4. Refresh this google sheet
5. Wait for the Custom Menus to show up and press "Menu" > "myFunction"

As you can see, the openByUrl() call is sitting inside the try catch block, however when you run the function through custom menu, you will still get "Error: You do not have permission to access the requested document.".

Executions Log:

CodePudding user response：

From your question, I thought that your situation might be due to the specification or a bug of SpreadsheetApp.openByUrl. If my understanding is correct, in order to avoid this issue, how about putting the method for checking whether the file can be used before SpreadsheetApp? In your script, how about the following modification?

From :

SpreadsheetApp.openByUrl(url)

To:

var fileId = url.split("/")[5];
var file = DriveApp.getFileById(fileId);
spreadsheet = SpreadsheetApp.open(file);

• In this modification, the file is retrieved with DriveApp.getFileById(fileId). When fileId cannot be used, an error occurs. But in this case, try-catch can be correctly worked. By this, the issue of SpreadsheetApp doesn't occur.