英文:
Update data validation value when source data is changed
问题
我正在尝试在侧边栏中使用setValues将数据写入“Console”工作表时，当来自侧边栏的用户输入更改源数据时，更新数据验证列表（'Roster！A2'）中的选定值。我的当前解决方案（基于旧帖子）能够正常工作，但仅当在“Console”工作表上手动编辑单元格时才有效。我明白onEdit()无法处理由setValue更改的单元格。
我找到了一个类似的帖子来解决这个问题（链接：https://stackoverflow.com/questions/32016776/google-apps-script-make-onedit-recognize-setvalue-changes），但这个帖子已经超过7年了。现在是否有更好的方法来处理这个问题？
当前的代码如下：
英文:
I am trying to update the selected value in a data validation list ('Roster!A2') when the source data is changed on the sheet 'Console' by user input from the sidebar. I am writing the data to 'Console' from the sidebar by using setValues.
My current solution (based on an old post) works as expected, but only when the cells are manually edited on the 'Console' sheet. I understand onEdit() does not work with cells changed by setValue.
I found a similar post addressing the problem here, but the post is over 7 years old. Is there a better way to do this now?
Current code:
function onEdit(e) {
const { source, range, oldValue, value } = e;
if (!oldValue || !value || range.getSheet().getName() !== "Console") {
return;
}
const target = source.getRange("Roster!A2")
changeOptions(target, oldValue, value)
}
function changeOptions(target, search, replaceWith) {
target
.createTextFinder(search)
.matchCase(true)
.matchEntireCell(true)
.matchFormulaText(false)
.replaceAllWith(replaceWith)
}
</details>
# 答案1
**得分**: 2
在更新源范围时，包括像这样的内容在函数中，当有人在侧边栏表单中更改数值时，应在设置源范围之前调用此函数。
```javascript
function updatetargetValue(newArray) {
var targetCellValue = SpreadsheetApp
.getActive()
.getSheetByName('Roster')
.getRange('A2')
.getValue();
var sourceTable = SpreadsheetApp
.getActive()
.getSheetByName('Console')
.getRange('C42:C67')
.getValues();
var targetIndex = sourceTable.findIndex(a => a[0] == targetCellValue);
if (targetIndex > -1) {
var newTargetValue = newArray[targetIndex][0];
SpreadsheetApp
.getActive()
.getSheetByName('Roster')
.getRange('A2')
.setValue(newTargetValue);
} else {
console.log('未在源范围中找到"' + targetCellValue + '"');
}
}
英文:
Include something like this in the function that is updating the source range in "Console" when someone changes a value from the side bar form. This function should be called before the source range is set.
function updatetargetValue(newArray) {
var targetCellValue = SpreadsheetApp
.getActive()
.getSheetByName('Roster')
.getRange('A2')
.getValue();
var sourceTable = SpreadsheetApp
.getActive()
.getSheetByName('Console')
.getRange('C42:C67')
.getValues();
var targetIndex = sourceTable.findIndex(a => a[0] == targetCellValue);
//console.log(targetIndex);
if (targetIndex > -1) {
var newTargetValue = newArray[targetIndex][0];
SpreadsheetApp
.getActive()
.getSheetByName('Roster')
.getRange('A2')
.setValue(newTargetValue);
} else {
console.log('did not find "' + targetCellValue + '" in source range')
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论