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

huangapple go评论89阅读模式
英文:

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

问题

我明白了,以下是您要翻译的部分:

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

  2. "When using onSelectionChange, the following error is output:"

  3. "Exception: You do not have permission to call UrlFetchApp.fetch."

  4. "Required permissions: https://www.googleapis.com/auth/script.external_request"

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

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

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

  8. "This is the function that is working:"

  9. "But the following fails with the mentioned Exception:"

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

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

请告诉我您需要翻译的内容,我会为您提供相应的翻译。

英文:

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

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

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:

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

No error is shown in the logs:

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

答案1

得分: 1

我理解DEEPL2函数包含UrlFetchApp.fetch()调用。我能够重现这个问题,因为Google Apps Script将函数onSelectionChange()识别为简单触发器。

根据Google参考中有关简单触发器限制的信息:

> 无法访问需要授权的服务。

适当的方法是创建可安装触发器,但您需要确保函数的名称与简单触发器的名称不同,否则Google Apps Script也会尝试运行这些函数。您可以尝试通过以下方式重置应用程序:

  1. 触发器 -> 删除触发器中删除所有已安装的触发器
  2. 将函数名称更改回onSelectionChangeHandler()
  var sheet = e.source.getActiveSheet();
  var range = e.range;

 range = e.source.getActiveRange();
...
  1. 更新installTriggers()函数以包含新函数名称:
  ScriptApp.newTrigger("onSelectionChangeHandler")
    .forSpreadsheet(sheet)
    .onChange()
    .create();
...
  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();
...
  1. Update the installTriggers() funtion to include the new function name:
...
  ScriptApp.newTrigger("onSelectionChangeHandler")
    .forSpreadsheet(sheet)
    .onChange()
    .create();
...
  1. Install the triggers via menu one more time.

References:

答案2

得分: 1

问题:

  • 无法使用简单触发器执行UrlFetch
  • 通过在单元格中插入图像并在其上附加Apps Script函数,无法访问环境(行/列/...)。
  • 单击图像不会触发onEdit/onChange事件

关于您尝试做的事情,我提供两种解决方法:

  1. 自定义Apps Script函数
    此方法将在每次修改源单元格时(几乎)实时执行翻译。

从Google电子表格中打开Apps Script编辑器,并添加以下函数:

function customTranslate(value, language) {

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

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

将函数添加为Google电子表格中的带有参数的公式。

  1. 复选框触发器:将图像替换为复选框以触发onChange事件

添加自定义onEdit函数:

function onEditCustom(e) {

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

  if (e.value === "TRUE") {
    //获取值(带有列和行)
    //调用API
    //写入返回值
  }
}

然后,在项目部分手动添加触发器:

  • 创建新触发器
  • 选择函数
  • 选择onChange
  • 确认

每次选中复选框时,它将执行该函数。

英文:

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 = {
    &quot;method&quot; : &quot;post&quot;,
    &quot;payload&quot; : {
      &quot;content_to_translate&quot;: value,
      &quot;langue&quot;: langue
    }
  }

  const r = UrlFetchApp.fetch(&lt;&lt;YOUR_ENDPOINT&gt;&gt;, options);
  return r.getContentText();
}

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

Add the function as a formula with the parameters in the Google Spreadsheet
onEditHandler works but onSelectionChange fails with Exception: You do not have permission to call UrlFetchApp.fetch. Required permissions:

--

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

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

Add a custom onEdit function :

function onEditCustom(e) {

  //console.log(e);

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

  if (e.value === &quot;TRUE&quot;) {
    //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

huangapple
  • 本文由 发表于 2023年5月24日 23:02:08
  • 转载请务必保留本文链接:https://go.coder-hub.com/76324927.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定