onEditHandler works but onSelectionChange fails with Exception: You do not have permission to call UrlFetchApp.fetch. Required permissions:

Question

I want that when the button is clicked (which is just an image within the cell), the function to translate the text is fired:

When using onSelectionChange, the following error is output:

Exception: You do not have permission to call UrlFetchApp.fetch. Required permissions: https://www.googleapis.com/auth/script.external_request

I've also tried with onSelectionChangeHandler, but nothing happens in that case (it doesn't seem to executed at all)

Please note I've on appscript.json that: https://www.googleapis.com/auth/script.external_request:

{
  "timeZone": "Europe/Warsaw",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": ["https://www.googleapis.com/auth/script.scriptapp", "https://www.googleapis.com/auth/spreadsheets.currentonly", "https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/script.container.ui", "https://www.googleapis.com/auth/script.external_request"],
  "runtimeVersion": "V8"
}

Please note I've tried both by naming the function with onSelectionChangeHandler and onSelectionChange, adding the trigger manually:

Owned by	Last run	Deployment	Event	Function	Error rate
Me	May 24, 2023, 2:57:16 PM	Head	From spreadsheet - On change	onSelectionChange	100%
Me	May 24, 2023, 2:57:16 PM	Head	From spreadsheet - On edit	onEditHandler	8%

And also in the code:

function installTriggers() {
  var sheet = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("onEditHandler")
    .forSpreadsheet(sheet)
    .onEdit()
    .create();
  ScriptApp.newTrigger("onSelectionChange")
    .forSpreadsheet(sheet)
    .onChange()
    .create();
}

function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu('install menu')
    .addItem('Install script (1/2): Give permissions', 'installTriggers')
    .addToUi();
}

This is the function that is working:

function onEditHandler(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEdit(e) function in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet.'
    );
  }

  var r = e.source.getActiveRange();

  // To translate with DeepL the column C (column 3):
  if (r.getColumn() == 3 && r.getRow() > 4 && r.getRow() < 75) {
    var toLang = SpreadsheetApp.getActiveSheet().getName();
    if (toLang == "ES" || toLang == "IT" || toLang == "FR" || toLang == "DE") {
      r.offset(0, -2).setValue(DEEPL2(e.value, toLang, "en"));
    }
  }

}

But the following fails with the mentioned Exception:

function onSelectionChange(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;

 range = e.source.getActiveRange();


  // Check if the selected range intersects with column B
  if (range.getColumn() == 2) {
    var row = range.getRow();
    var text = sheet.getRange(row, 3).getValue();

    var toLang = sheet.getName();
    if (toLang == "ES" || toLang == "IT" || toLang == "FR" || toLang == "DE") {
      var translatedText = DEEPL2(text, toLang, "en");
      var targetRange = range.offset(0, -1);
      targetRange.setValue(translatedText);
    }
  }
}

Intuitively, I think the reason of why it's not working may be related to the following affirmation I found in the official documentation:

To activate this trigger, you must refresh the spreadsheet once the trigger is added and every time the spreadsheet is opened.

I've refreshed the spreadsheet, but I got that exception over and over:

No error is shown in the logs:

Answer 1

I understand that the function DEEPL2 contains a UrlFetchApp.fetch() call. I was able to reproduce the issue and it is occurring because Google Apps Script is recognizing the function onSelectionChange() as a simple trigger.

Based on Google Reference related to simple triggers Restrictions:

Cannot access services that require authorization.

The approach to create Installable Triggers instead is appropriate but you'll need to make sure that the functions are not named the same as simple triggers, otherwise Google Apps Script will attempt to run those as well. You could try resetting the application by:

1. Deleting all installed triggers at Triggers -> Delete trigger
2. Change the name of the function back to onSelectionChangeHandler()

function onSelectionChangeHandler(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;

 range = e.source.getActiveRange();
...

3. Update the installTriggers() funtion to include the new function name:

...
  ScriptApp.newTrigger("onSelectionChangeHandler")
    .forSpreadsheet(sheet)
    .onChange()
    .create();
...

4. Install the triggers via menu one more time.

---

References:

• Simple triggers - onSelectionChange(e)
• UrlFetchApp - Authorization is required.

Answer 2

Problems:

• You can't execute UrlFetch with simple trigger
• By inserting an image inside a cell, and attaching an Apps Script function on it, you can't access the env (row/column/...).
• clicking on image does not trigger onEdit/onChange event

Regarding what you are trying to do, here two workarounds I'm suggesting :

1. custom Apps Script function: This method will execute the translate in (almost) realtime everytime you modify the source cell.

Open Apps Script Editor from Google Spreadsheet and add the following function

function customTranslate(value, langue) {

  const options = {
    "method" : "post",
    "payload" : {
      "content_to_translate": value,
      "langue": langue
    }
  }

  const r = UrlFetchApp.fetch(<<YOUR_ENDPOINT>>, options);
  return r.getContentText();
}

//for testing:
//function customTranslate(value, langue) {
//  return value + " (translated)";
//}

Add the function as a formula with the parameters in the Google Spreadsheet

--

2. Trigger with checkboxes: replacing the image with a checkboxes in order to trigger onChange event

Add a custom onEdit function :

function onEditCustom(e) {

  //console.log(e);

  const column = e.range.getColumn();
  const row = e.range.getRow();

  if (e.value === "TRUE") {
    //get the value (with col and row)
    //call api
    //write the return
  }

}

Then, add the trigger manually in the project section:

• Create new trigger
• Select the function
• Select onChange
• Confirm

Each time the checkboxe is checked, it will execute the function