0

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!

onit
  • 2,275
  • 11
  • 25
  • When you use a Web App in Apps Script, you have to access the link in order to execute the doGet()/doPost() function, which returns an HTML service HtmlOutput object or a Content service TextOutput object. This is similar to creating a website or an API. Could you explain why you needed the Web App and what exactly part of the Web App you are having trouble with? – Nikko J. Mar 23 '22 at 23:47
  • Hi , @NikkoJ.! Thanks for your input. From what I got from this [answer](https://stackoverflow.com/a/71484779/11832197) thread, I'd have to wrap this url in the function(s), but doing that is what I'm not able to quite figure out yet. – onit Mar 24 '22 at 00:22
  • 1
    Could you confirm this, you want your doGet() function to execute the code above so that the permission is on the owner of the script and not to the one using it? – Nikko J. Mar 24 '22 at 00:40
  • 1
    In your script, what is `sheetCadProd` of `const refRng = sheetCadProd.getRange('B5').setValue(ref);`? If `sheetCadProd.getRange('B5')` cannot be accessed from the user, this part is required to be included in the Web Apps. But at that time, `refRng.offset(0, 2).activate()` doesn't work. Please be careful this. – Tanaike Mar 24 '22 at 00:44
  • Correct, @NikkoJ.! – onit Mar 24 '22 at 00:50
  • @Tanaike, I have many functions like, where the user is led to a range after the code runs. Does this mean that after setting it as a web app, methods like ```activate()``` won't work? – onit Mar 24 '22 at 00:51
  • 1
    Thank you for replying. Yes. At the Web Apps, `refRng.offset(0, 2).activate();` cannot be directly used. So for example, when the range of `refRng.offset(0, 2)` is returned to `gerarRef()` of the client side, `range.activate()` can be used. But if `sheetCadProd.getRange('B5')` can be used by the user, this issue is not required to be considered. So, I asked about it before I proposed a modified script. – Tanaike Mar 24 '22 at 00:55
  • Thank you, @Tanaike! Since the requirement that the functions run as the editors click on buttons is top priority, I'll adjust methods such as ```offset()```, ```activate()``` and so on. Thanks! – onit Mar 24 '22 at 00:58
  • Thank you for replying. From your reply, I proposed a modified script as an answer. Could you please confirm it? In this modification, the range you want to activate is returned from Web Apps as `refRng.offset(0, 2).getA1Notation()`. By this, at the client side, this cell can be activated. But if you are not required to use it, please remove the script of `sheetCadProd.getRange(range).activate()`. At that time, please reflect the latest script to the Web Apps. Please be careful this. – Tanaike Mar 24 '22 at 01:08

1 Answers1

1

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thanks a lot for the detailed, top notch answer. I'll work on it shortly. I got many functions and files to adjust, considering principle explained. Thanks a million! – onit Mar 24 '22 at 01:22
  • @santosOnit Thank you for replying. If that was not useful, I apologize. – Tanaike Mar 24 '22 at 01:31