google-apps-scriptgoogle-sheetswebapp2role-base-authorization

How to use Web App approach when having functions run by editors on protected ranges, using Google Apps Scritps?

So, the spreadsheet contains multiple functions which run as the editors click on buttons. Some of the functions bring data from other files, which editors don't have access to. So, data are brought into protected ranges. Editors add their inputs into unprotected ranges and as they click on Save, the results are saved back into other files.

Example of how another file is accessed: file: globals.gs

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

Here's a function using the data from the file above:

//It generates the next item code, based on the last record in the file above
function gerarRef() {
  try {
    const response = Browser.msgBox('Would you like to confirm it?', Browser.Buttons.YES_NO);
    if (response == 'no') {
      return;
    } else {
      let ref = 0;
      const refExistentes = sheetBDCadProd.getRange(2, 1, sheetBDCadProd.getLastRow(), 1).getValues();
      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('The following error has occurred: ' + err);
  }
}

I understand that the Web App approach requires a doGet() function to be added to it and it's to be deployed with Executed by: Me and Accessed by: Anyone. However, I don't know how to tie it together with the existing functions I got bounded to the spreadsheet.

Appreciate your attention/help!

Solution

  • In your situation, how about the following modification?

    1. Modified script:

    Please copy and paste the following script to the script editor and save the script.

    In this script, sheetCadProd is not declared, because I cannot see it in your showing script. Please be careful this.

    function doGet() {
  const CAD_PRODUTO = 'XXXXXXXXXXXXxxxxxxxxxxxxxxxx';
  const config = {
    get ssBDCadProd() {
      delete this.ssBDCadProd;
      return (this.ssBDCadProd = SpreadsheetApp.openById(CAD_PRODUTO));
    }
  }
  let ref = 0;
  const sheetBDCadProd = config.ssBDCadProd.getSheetByName('CadProduto');
  const refExistentes = sheetBDCadProd.getRange(2, 1, sheetBDCadProd.getLastRow(), 1).getValues();
  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);
  return ContentService.createTextOutput(JSON.stringify({ range: refRng.offset(0, 2).getA1Notation() }));
}

//It generates the next item code, based on the last record in the file above
function gerarRef() {
  try {
    const response = Browser.msgBox('Would you like to confirm it?', Browser.Buttons.YES_NO);
    if (response == 'no') {
      return;
    } else {
      const res = UrlFetchApp.fetch("https://script.google.com/macros/s/###/exec");
      console.log(res.getContentText());
      const { range } = JSON.parse(res.getContentText());
      sheetCadProd.getRange(range).activate();
    }
  } catch (err) {
    Browser.msgBox('The following error has occurred: ' + err);
  }
}

    2. Deploy Web Apps.

    The detailed information can be seen at the official document.

    1. On the script editor, at the top right of the script editor, please click "click Deploy" -> "New deployment".
    2. Please click "Select type" -> "Web App".
    3. Please input the information about the Web App in the fields under "Deployment configuration".
    4. Please select "Me" for "Execute as".
      • This is the importance of this workaround.
    5. Please select "Anyone" for "Who has access".
      • In your situation, I thought that this setting might be suitable.
    6. Please click "Deploy" button.
    7. Copy the URL of the Web App. It's like https://script.google.com/macros/s/###/exec.

    And also, please set your Web Apps URL to const res = UrlFetchApp.fetch("https://script.google.com/macros/s/###/exec"); of the above script. And, please reflect the latest script to the Web Apps again. By this, your script works. Please be careful this.

    3. Testing.

    Before you use this script, please check sheetCadProd. In my modified script, sheetCadProd is not declared, because I cannot see it in your showing script. Please be careful this.

    In the above script, please run gerarRef(). By this, the script of Web Apps is run by the owner of Web Apps.

    Note:

    References: