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

Question

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!

What does this script do? Is the editor running the script from the Sheet or the Web App? They need to run it from the Web App URL for it to execute as your account. — Daniel, Mar 15 '22 at 01:32
@Daniel, that is an important detail. The script is run upon the click of a button. I've tried setting it as a library as well, but I got the same permission issue. In that case, any suggestion on how to proceed? — onit, Mar 15 '22 at 11:59

Rubén · Accepted Answer · 2022-03-15T15:46:34.673

2

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.

answered Mar 15 '22 at 15:24

Rubén

34,714
9
70
166

Thanks for bottling this one nicely. – onit Mar 15 '22 at 17:01
Hi! I've been trying to implement the web app approach, but I can't wrap my head around getting it called in the functions I got spreadsheet through the file/spreadsheet. Do I have to call it ```callMyWebApp()``` at the beginning of each function, so that scopes work? Do I replace each button's function for this one and append the fucntions supposed to run on the click of the buttons? Sorry to be asking too much, but I have no other choice than investigate everywhere I can! Thank you! – onit Mar 15 '22 at 22:02
"Do I have to call it callMyWebApp() ...? "No you can call it whatever you want with few exceptions (don't user reserved names like onEdit, doGet, etc.) "Do I replace each button's function for this one ..." No. If each button does something different you the should have assigned different functions. – Rubén Mar 16 '22 at 00:17
Sorry I wasn't clear, but I meant: At what point/where do I have to call this function? In each of the functions I got, or just once? Gracias – onit Mar 16 '22 at 00:28
If you haven't done yet, please read the linked Q/A. If need further help, please create a [mcve] and post a new question. – Rubén Mar 16 '22 at 04:22
Just added info above which I believe serve as an example! Thank you! – onit Mar 23 '22 at 18:33

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

1 Answers1

Linked