Google App Script fails when run as webapp

Question

I have shared a Google Sheet with another user and I want them to be able to run some script functions as the owner (me).

I have looked at these two topics:

Google Sheets - Run script with Sheet Protection

Calling Different Functions using doGet()

But I am not having success. This is my error: "Exception: Request failed for https://script.google.com returned code 404."

Here is my code:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('myMenu')
      .addItem('run function 1', 'function1')
      .addItem('run function 2', 'function2')
      .addItem('run webApp function 1', 'runfunction1')
      .addItem('run webApp function 2', 'runfunction2')
      .addToUi();
};

function doGet(e) {
  if (e.parameter.run == "function1") {
    function1();
    return ContentService.createTextOutput("run 1");
  }
  else if (e.parameter.run == "function2") {
    function2();
    return ContentService.createTextOutput("run 2");
  }
  else {
    return ContentService.createTextOutput("Oops...no match found");
  }
};

function function1() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("One");
  ss.getRange(1,1,1,1).setValue(1);
};

function function2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("One");
  ss.getRange(1,1,1,1).setValue(2);
};

function runfunction1() {
  const url = ScriptApp.getService().getUrl();
  fixedURL = url + "?run=function1";
  UrlFetchApp.fetch(fixedURL, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } });
};

function runfunction2() {
  const url = ScriptApp.getService().getUrl();
  fixedURL = url + "?run=function2";
  UrlFetchApp.fetch(fixedURL, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } });
};

And here is my spreadsheet: Link

What am I missing?

Answer 1

Modification points:

• If your actual script is the same as your showing script, I guess that the scope is not enough for requesting Web Apps. In this case, for example, a scope of https://www.googleapis.com/auth/drive.readonly is required to be included.

• In your script, when ScriptApp.getService().getUrl() is run, it returns the invalid URL. This has already been reported to the Google issue tracker. Ref

When these points are reflected in your script, please do the following flow.

1. Add a scope

Please add a scope of https://www.googleapis.com/auth/drive.readonly.

In this case, please add a comment line to your script as follows.

From:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('myMenu')
      .addItem('run function 1', 'function1')
      .addItem('run function 2', 'function2')
      .addItem('run webApp function 1', 'runfunction1')
      .addItem('run webApp function 2', 'runfunction2')
      .addToUi();
};

To:

// DriveApp.getFiles() // Please add this comment line. By this, a scope of "https://www.googleapis.com/auth/drive.readonly" is automatically added by the script editor, when the script is run.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('myMenu')
      .addItem('run function 1', 'function1')
      .addItem('run function 2', 'function2')
      .addItem('run webApp function 1', 'runfunction1')
      .addItem('run webApp function 2', 'runfunction2')
      .addToUi();
};

2. Deploy Web Apps as new version

Please deploy Web Apps as a new version. And please copy the Web Apps URL of https://script.google.com/macros/s/###/exec. This URL is used instead of ScriptApp.getService().getUrl().

3. Modify your script as follows

From:

function runfunction1() {
  const url = ScriptApp.getService().getUrl();
  fixedURL = url + "?run=function1";
  UrlFetchApp.fetch(fixedURL, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } });
};

function runfunction2() {
  const url = ScriptApp.getService().getUrl();
  fixedURL = url + "?run=function2";
  UrlFetchApp.fetch(fixedURL, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } });
};

To:

function runfunction1() {
  const url = "https://script.google.com/macros/s/###/exec"; // Modified
  fixedURL = url + "?run=function1";
  UrlFetchApp.fetch(fixedURL, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } });
};

function runfunction2() {
  const url = "https://script.google.com/macros/s/###/exec"; // Modified
  fixedURL = url + "?run=function2";
  UrlFetchApp.fetch(fixedURL, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } });
};

4. Redeploy Web Apps as the same version

After you modify ScriptApp.getService().getUrl() to "https://script.google.com/macros/s/###/exec", please redeploy Web Apps as the same version. In this case, you can see my report "Redeploying Web Apps without Changing URL of Web Apps for new IDE (Author: me)".

In your situation, when you modified the Google Apps Script of Web Apps, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.

5. Testing

After the above flow, please run runfunction1 and runfunction2. By this, the script works. Also, I confirmed that the modified script worked.

Note:

• If an error occurs or runfunction1 and runfunction2 was not run after the above flow was done, please confirm the above flow again.

• By the way, from your showing script, in this modification, it supposes your Web Apps setting as follows.

  • Execute as: Me and Who has access to the app: Anyone with Google account, and your Spreadsheet is shared with the user.

  • If your setting of them is, Execute as: Me and Who has access to the app: Anyone, I think that you are not required to use the access token.

References:

• Web Apps
• Taking advantage of Web Apps with Google Apps Script