Search code examples
google-apps-scriptgoogle-sheetsweb-applicationsgoogle-workspace

Why webapp to execute as me, accessed by anyone shows effectiveUser and activeUser being the editor, not the owner?

So, I have some functions bounded to a spreadsheet, which makes modifications to ranges protected from editors. The functions are to be run as the editors click on buttons on the spreadsheet. These functions also bring data from other files, which editors don't have access to. So, as per comments below, it would have to be deployed as a web app, run by me and accessed by anyone.

My understanding is there must be a doGet() function in it and the web app url is to be called. However, I don't know where to add this doGet() function as suggested below.

Examples of existing functions:

file: globals.gs, which is where data outside of the spreadsheet are brought into the file

const CAD_PRODUTO = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
const config = {
  get ssBDCadProd() {
    delete this.ssBDCadProd;
    return (this.ssBDCadProd = SpreadsheetApp.openById(CAD_PRODUTO));
  }
const sheetBDCadProd = config.ssBDCadProd.getSheetByName('CadProduto');

Function sitting in the spreadsheet, which creates a new number based on data in an external file:

function gerarRef() {
  try {
    const response = Browser.msgBox('Do you want to create a new record?', Browser.Buttons.YES_NO);
    if (response == 'no') {
      return;
    } else {
      let ref = 0;
      const refExistentes = sheetBDCadProd.getRange(2, 1, sheetBDCadProd.getLastRow(), 1).getValues(); //Calls other file
      let ultRef = Math.max(...refExistentes);
      Logger.log('Ult.: ' + ultRef)
      if (ultRef == 0 || ultRef == '') {
        ref = 10000;
      } else {
        ref += ultRef + 1;
      }
      const refRng = sheetCadProd.getRange('B5').setValue(ref);
      refRng.offset(0, 2).activate();
    }
  } catch (err) {
    Browser.msgBox('Ocorreu um erro: ' + err);
  }
}

Besides the functions above, I got many others. How can I then apply the web app approach?

Thanks a million!

Solution

  • The web app deployment settings like "Run as me" and "By Anyone" only have effect over doGet and doPost functions, and only when they are called by HTTP requests (when they are called by using the web app URL).

    If you have set a button on a spreadsheet by inserting a drawing and assigning a function to it, the web app deployment settings will not have any effect over a function that directly calls the Google Apps Scripts services like SpreadsheetApp, but might work if the function use the UrlFetchApp service to make the HTTP request to your web app.

    Here is an oversimplified Google Apps Script server side code (.gs) to make a call to a web app:

    function callMyWebApp(){
  const url = 'put here your web app URL';
  const response = UrlFetchApp.fetch(url); // This will do a GET request
  // do something with the response
}

    An easier approach might be to use an installable trigger as it's not required to deal with another "complexity layer" as it doesn't require to add another service to your project (UrlFetchApp), it will not be necessary to deploy a web-app and make a new version every time that you update your code, but if you want to keep at minimum the scopes to be authorized by the editors, you will have to use the web app approach.

    Related