英文:
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
" -
"Please note I've tried both by naming the function with
onSelectionChangeHandler
andonSelectionChange
, adding the trigger manually:" -
"This is the function that is working:"
-
"But the following fails with the mentioned Exception:"
-
"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 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:
答案1
得分: 1
我理解DEEPL2
函数包含UrlFetchApp.fetch()
调用。我能够重现这个问题,因为Google Apps Script将函数onSelectionChange()
识别为简单触发器。
根据Google参考中有关简单触发器限制的信息:
> 无法访问需要授权的服务。
适当的方法是创建可安装触发器,但您需要确保函数的名称与简单触发器的名称不同,否则Google Apps Script也会尝试运行这些函数。您可以尝试通过以下方式重置应用程序:
- 在
触发器 -> 删除触发器
中删除所有已安装的触发器 - 将函数名称更改回
onSelectionChangeHandler()
var sheet = e.source.getActiveSheet();
var range = e.range;
range = e.source.getActiveRange();
...
- 更新
installTriggers()
函数以包含新函数名称:
ScriptApp.newTrigger("onSelectionChangeHandler")
.forSpreadsheet(sheet)
.onChange()
.create();
...
- 再次通过菜单安装触发器。
参考:
-
简单触发器 - onSelectionChange(e)
-
UrlFetchApp - 需要授权。
英文:
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:
- Deleting all installed triggers at
Triggers -> Delete trigger
- 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();
...
- Update the installTriggers() funtion to include the new function name:
...
ScriptApp.newTrigger("onSelectionChangeHandler")
.forSpreadsheet(sheet)
.onChange()
.create();
...
- Install the triggers via menu one more time.
References:
-
Simple triggers - onSelectionChange(e)
-
UrlFetchApp - Authorization is required.
答案2
得分: 1
问题:
- 无法使用简单触发器执行UrlFetch
- 通过在单元格中插入图像并在其上附加Apps Script函数,无法访问环境(行/列/...)。
- 单击图像不会触发onEdit/onChange事件
关于您尝试做的事情,我提供两种解决方法:
- 自定义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电子表格中的带有参数的公式。
- 复选框触发器:将图像替换为复选框以触发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 :
- 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
--
- 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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论