could not open Documents in a Spreadsheet add-on. openByUrl( ) no permission

Question

I have a speadsheet extension, which saves many docs metadata.

It uses an add-on menu to activate a merge files function, which uses DocumentApp.openBy..()

My question is a little like this.

I modified the manifest (appsscript.json) to have the following permissions:

It still does not work.

Here's my code.

makeMerge is triggered by an add-on menu button.

function onOpen(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createAddonMenu().
    addItem("merge", 'uiTrigger').
    addToUi();
}


function uiTrigger() {
  makeMerge(SpreadsheetApp.getActiveSheet(), DriveApp.getRootFolder());
}


function makeMerge(sheet, folder) {
  if (folder === null || folder === undefined) {
     folder = DriveApp.getRootFolder();
  }

  var dataRows = sheet.getDataRange().getValues().length - 1;
  var names = sheet.getRange(2, 1, dataRows, 1).getValues();
  var docsRef = sheet.getRange(2, 8, dataRows, 1).getValues();
  var toDownload = sheet.getRange(2, 11, dataRows, 1).getValues();

  for (var i in toDownload) {
    var name = names[i][0];
    var docRef = docsRef[i][0];
    Logger.log(docRef);
    try {
      var doc = DocumentApp.openByUrl(docRef);
    } catch(error) {
      Logger.log(error);
    }
  }  
}

I have no idea (I don't use spreadsheet custom function) why do I have the same issue? How do I give my script permission to open Documents?

What error do you get exactly, when you run this program? Have you tried running this from script editor first to give it all the required permissions? Secondly, are you sure you have permission to access all the files you are trying to access via DocumentApp.openByUrl()? — Jack Brown
1. caught by catch() block : Exception: Document is missing (perhaps it was deleted, or you don't have read access?) and have an undefined doc variable. 2. Yes. I run other functions. And I do have permission to access my spreadsheet. 3. All granted scopes are listed in manifest. (appsscript.json) and all files are own by myself. — 程柏硯
Ok few things to try, in debug mode check the value of the docRef value, make sure you are accessing the right values from the sheet. Secondly, I am not asking about permission to access the spreadsheet, I am asking if you have the right to access the document files you are trying to open programmatically. Finally, what do you mean by run other functions? Have you tried running uiTrigger() function from the script editor, does it work? — Jack Brown
1. docRef is current file URL string. ie. https://docs.google.com/a/XXXX/open?id=longFileIDString 2. I did grant the document scope, and these documents are owned by myself. 3. Run any function in script triggers oauth prompt 4. It does not work. This script is a standalone, so getActivesheet() can only run in extenstion sandbox. Therefore, it runs good until accessing google docs. — 程柏硯

程柏硯程柏硯 · Accepted Answer · 2019-01-25T02:08:54

DocumentApp.openByUrl() does not take a link from doc.getUrl() It seems not documented at offical website. Here's an example.

// with a container-bound script, in this example
function notWork() {
  var doc = DocumentApp.getActiveDocument();
  var docUrl = doc.getUrl();
  Logger.log(docUrl);  
  var newDoc = DocumentApp.openByUrl(docUrl);
}

Therefore, DocumentApp.openById() still works.

function Worked() {
  var doc = DocumentApp.getActiveDocument();
  var docId = doc.getId();
  Logger.log(docId);  
  var newDoc = DocumentApp.openById(docId);
}

So, we can extract the id from the doc.getUrl(), which in the form of https://docs.google.com/open?id=YOUR_DOCUMENT_ID or https://docs.google.com/a/YOUR_DOMAIN/open?id=YOUR_DOCUMENT_ID

function modifyFromNotwork(){
  var doc = DocumentApp.getActiveDocument();
  var docUrl = doc.getUrl();
  var docId = docUrl.slice(docUrl.indexOf("=")+1);
  Logger.log(docId);
  var newDoc = DocumentApp.openById(docId);
}

could not open Documents in a Spreadsheet add-on. openByUrl( ) no permission

1 Answers