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](http://stackoverflow.com/posts/43463046/edit) the question to include a [Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) 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?](http://stackoverflow.com/help/on-topic), and [How to Ask](http://stackoverflow.com/help/how-to-ask) — Jacque, Feb 22 '19 at 06:11
@Sami.S The issue of "install the addon from another account" sounds like a "@OnlyCurrentDoc" issue. Refer (https://stackoverflow.com/questions/50859119/), (https://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, Feb 27 '19 at 10:06
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, Feb 27 '19 at 15:47
_"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, Feb 28 '19 at 04:11
@Tedinoz "@OnlyCurrentDoc" change nothing the error is always raised (perhaps it was deleted, or maybe you don't have read access?). And I have tested on both configuration GSuite and the free Google Apps. For information my Addon is publish with public visibility, and the API APP Script is deployed with visibility set to "anyone" ! — Sami.S, Feb 28 '19 at 16:23
_my Addon is publish with public visibility, and the API APP Script is deployed with visibility set to "anyone"_ Well, that probably explains everything. — Tedinoz, Mar 01 '19 at 05:16
@Tedinoz What do you mean by that ? I should deploy API APP Script with visibility set to "Only myself" ? — Sami.S, Mar 01 '19 at 07:31
@Tedinoz But how I can set my Addon visible and available for public ? The choose available on the developer dashboard are Public, Unlisted (Domain) and private ! — Sami.S, Mar 01 '19 at 09:10
@Sami.S I spoke in haste. I was reflecting that this topic started because of access to a document that you resolved by using "spreadsheet by ID". That would, in turn, require giving appropriate access to that document. I'm not certain that this ought to be a function of the Add-on, per se, But if this spreadsheet contains information that I would not like to share with the world, then having a public Add-on would just seem to increase the likelihood that the world will access your spreadsheet. — Tedinoz, Mar 01 '19 at 21:09

score 1 · Answer 1 · answered Mar 05 '19 at 15:58

1

I have resolved my problem using spreadsheet by ID:

SpreadsheetApp.openById("spreadsheet ID")

instead of

SpreadsheetApp.getActiveSpreadsheet()

answered Mar 05 '19 at 15:58

Sami.S

267
1
13

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

1 Answers1