Sheets app script addon publish but all actions are done on container-bound

Question

I have developed an addon composed of :

an Apps Script bound to a spreadsheet
an external web UI which calls some Apps Script function thanks the Apps Script API.

Here an example of my code :

1) in my external web UI I do something like :

window.gapi.client.script.scripts.run({
    scriptId: 'myscripID',
    resource: {
      function: addWorksheet,
      parameters: [
        'worksheetName'
      ],
      devMode: false
    } 
    ....

2) And the in my app Script I have the function :

function addWorksheet(worksheetName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var newSheet = ss.insertSheet(0);
  ss.setActiveSheet(ss.getSheetByName(newSheet.getName()));
  newSheet.setName(worksheetName);
  return(newSheet.getName());
}

Problem description : The problem is when I test my add-on from 'Run->Test as add-on ..." menu, on the container-bound Scripts, it's working well. => The new sheet is well created.

But if I test it on a another Google Sheets, the new sheet is created in the container-boundScripts but not in the active Google Sheets.

I have also remarked if I install the addon from another account than this used to create the container-bound Scripts, the following error is raised :

@type: "type.googleapis.com/google.apps.script.v1.ExecutionError" errorMessage: "You do not have permission to access the requested document." errorType: "ScriptError"

Help will be greatly appreciated !

Please edit the question to include a Minimal, Complete, and Verifiable example that duplicates the problem. Questions seeking debugging help ("why isn't this code working the way I want?") must include: (1) the desired behavior, (2) a specific problem or error and (3) the shortest code necessary to reproduce it in the question itself. Please also see: What topics can I ask about here?, and How to Ask — Jacque
@Sami.S The issue of "install the addon from another account" sounds like a "@OnlyCurrentDoc" issue. Refer (stackoverflow.com/questions/50859119), (stackoverflow.com/questions/30587331), and many others. Possibly if you resolve this, then the script will execute on non-container bound spreadsheet.s Otherwise you would have to specify your spreadsheet by ID, or you build a standalone script. — Tedinoz
Thanks for your help @Tedinoz. I have resolved my problem using spreadsheet by ID. But now when I test my deployed Addon, edition or read actions are possible only if I change the status spreadsheet to Public ("Public on the web Anyone on the Internet can find and access. No sign-in required."). If I keep the spreadsheet on private state the following error is raised : [Error] ScriptError: Document xxxxxxxxx is missing (perhaps it was deleted?, or you don't have read access?) — Sami.S
"only if I change the status spreadsheet to Public ("Public on the web Anyone on the Internet can find and access. No sign-in required.") Is this a corporate/business app or a private app? If business then, well, statement of the bleeding obvious: it's public to the entire world! Are you running GSuite or the free Google Apps. If it's the latter, then it might explain why you have to share so freely; though I do find it odd that you aren't able to share selectively. As a matter of interest, have you explored the "@OnlyCurrentDoc" issue whether it is relevant to your situation? — Tedinoz

Sami.S Sami.S · Accepted Answer · 2019-03-05T15:58:06

I have resolved my problem using spreadsheet by ID:

SpreadsheetApp.openById("spreadsheet ID")

instead of

SpreadsheetApp.getActiveSpreadsheet()

Sheets app script addon publish but all actions are done on container-bound

1 Answers