如果单元格自动更新,则“编辑"不起作用 - 需要解决方法 Google 表格 [英] On Edit doesnt work if cell auto updates - Need a workaround Google Sheets
问题描述
当您手动更改单元格时,此代码按预期工作.
function onEdit(event) {//假定名为 Prepsheet 的工作表中的源数据//移动到命名的 TopUp 的目标表 必需var ss = SpreadsheetApp.getActiveSpreadsheet();var s = event.source.getActiveSheet();var r = event.source.getActiveRange();if(s.getName() == "PrepSheet" && r.getColumn() == 15 && r.getValue() == 0) {var row = r.getRow();var numColumns = s.getLastColumn();var targetSheet = ss.getSheetByName("TopUp Required");var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);s.getRange(row, 1, 1, numColumns).copyTo(target);}}
我的问题是我正在使用 =sum(A2-B2)
来更改单元格.if cell then =0 它应该运行脚本,但不是因为单元没有手动更改.
我怎样才能修改代码来做到这一点?或者你会怎么做
感谢任何帮助
解决方法是使用 可安装的 onChange
触发器与带有 =IMPORTRANGE()
函数.
说明:
虽然 onEdit
触发器根本不检测由单元格公式引起的任何单元格内容更新,但 onChange
可以检测某些更改,例如通过 <代码>=IMPORTRANGE() 公式.
工作流程:
- 创建第二个空电子表格
- 将公式
=IMPORTRANGE(IMPORTRANGE(spreadsheet_url, range_string)
分配给第二个电子表格工作表中的单元格,其中电子表格 url 是包含所有数据和公式的第一个电子表格的 URL(例如=sum(A2-B2)
) 和 range_string 感兴趣的范围(例如PrepSheet!O1:O"表PrepSheet
中第 15 列) - 将脚本附加到第二个电子表格,而不是附加到原始电子表格
- 使用 Scriptproperties 存储单元格旧单元格值并与新单元格值进行比较值 - 为了检测哪个单元格已被编辑
<块引用>
示例:
var ss = SpreadsheetApp.getActiveSpreadsheet();var sheet = ss.getSheetByName("name");//包含 =IMPORTRANGE() 公式的工作表的名称var origin=SpreadsheetApp.openById('原始电子表格的ID');var s=origin.getSheetByName("PrepSheet");var lastRow=s.getLastRow();var range=sheet.getRange(1,1,lastRow,1);//从原始电子表格中导入第 15 列的列function initialSetUp(){//只运行一次这个函数,除非你感兴趣的范围发生变化//如果需要改变var values=range.getValues();for(var i=0;i
This code works as intended when you manually change a cell.
function onEdit(event) {
// assumes source data in sheet named Prepsheet
// target sheet of move to named TopUp Required
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();
if(s.getName() == "PrepSheet" && r.getColumn() == 15 && r.getValue() == 0) {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("TopUp Required");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).copyTo(target);
}
}
My problem is I am using =sum(A2-B2)
to change the cell. if cell then =0 it should run the script, but doesn't because the cell wasnt manually changed.
How could I modify code to do this? Or how would you do it
Appreciate any help
The workaround would be using the installable onChange
trigger in combination with a second spreadsheet with an =IMPORTRANGE()
function.
Explanation:
While an onEdit
trigger does not detect any cell content update caused by a cell formula at all, onChange
can detect certain changes, such as the update occuring through the =IMPORTRANGE()
formula.
Workflow:
- Create a second, empty spreadsheet
- Assign to a cell in a sheet of the second Spreadsheet the formula
=IMPORTRANGE(IMPORTRANGE(spreadsheet_url, range_string)
, whereby spreadsheet_url is the URL of the first spreadsheet that contains all your data and formulas (e.g.=sum(A2-B2)
) and range_string the range of interest (e.g. "PrepSheet!O1:O" for column 15 in sheetPrepSheet
) - Attach a script to the second spreadsheet, rather then to the original spreadsheet
- Use Scriptproperties to store cell old cell values and compare to the new values - in order to detect which cell has been edited
Sample:
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("name"); // the name of the sheet containing the =IMPORTRANGE() formula
var origin=SpreadsheetApp.openById('ID of original spreadsheet');
var s=origin.getSheetByName("PrepSheet");
var lastRow=s.getLastRow();
var range=sheet.getRange(1,1,lastRow,1); //the column into which you imported column 15 from the original spreadsheet
function initialSetUp(){//run this function only once, unless your range of interest changes
//change if required
var values=range.getValues();
for(var i=0;i<values.length;i++){
PropertiesService.getScriptProperties().setProperty('values '+i, values[i][0]);
}
}
function triggeredOnChange() {
var values=range.getValues();
var numColumns = s.getLastColumn();
var targetSheet = origin.getSheetByName("TopUp Required");
for(var i=0;i<values.length;i++){
var scriptValue=PropertiesService.getScriptProperties().getProperty('values '+i);
var newValue=values[i][0];
if(newValue!=scriptValue && newValue==0){
Logger.log(scriptValue);
Logger.log(newValue);
var row = i+1;
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).copyTo(target);
}
}
}
这篇关于如果单元格自动更新,则“编辑"不起作用 - 需要解决方法 Google 表格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!