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

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:

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:

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