交叉更新特定项目谷歌工作表中同一工作表中两列的数据

Question

因此，我想在列C和列K之间为位于列>D中的每个特定项目(在本例中为链接(进行"串扰"(包含从=D5开始的web中的各种链接(。

整个工作表分为两部分(见图(：

1. 主数据库1。工作表图像
2. 过滤数据(适合D5:D中的链接(2.我用过的"过滤"公式-看看

如果我选择"；完成">对于C5中的项目(3.免疫学→1.转基因动物→2.生物技术和应用动物学(，我想选择"完成">在K16中反之亦然。我希望所有项目都能做到这一点。见图：3。C5→K16&4.K16→C5

此外，我正在使用谷歌应用程序脚本多行相关下拉列表-

//Sheet = 'Link Database' - Creating suitable dependant Dropdown 
function onEdit(){
//this line refers to the current active sheet
var start = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
//this 'current' variable captures the currently selected cell in the active spreadsheet
var current = start.getActiveCell();
//var to refer to the worksheet
var topicDatabase = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Topic Database");
var linkDatabase = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Link Database");
//has the user selected a subject?
if (current.getColumn()==5)
{
//to copy the selcted subject
var coiceSubject = current.getValue()
topicDatabase.getRange("P2").setValue(coiceSubject)
//clear any validation
linkDatabase.getRange("F5:F").clearDataValidations();
//create the rule
var point = current.offset(0,1)
var items = topicDatabase.getRange("N2:N")
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(items).build();

//clear content
point.clearContent();
point.setDataValidation(rule)
}
//has the user selected a topic?
if(current.getColumn()==6)
{
//to copy the selected topic
var choiceTopic = current.getValue()
topicDatabase.getRange("Q2").setValue(choiceTopic)

//clear any validation
linkDatabase.getRange("G5:G").clearDataValidations();

//create the rule
var point2 = current.offset(0,1)
var items2 = topicDatabase.getRange("O2:O")
var rule2 = SpreadsheetApp.newDataValidation().requireValueInRange(items2).build();
point2.clearContent();
point2.setDataValidation(rule2)
}
//if subject is blank - clear content & validation in topic and sub topic
if(linkDatabase.getRange("E5:E").isBlank()==true){
//clear validaton on the same range
linkDatabase.getRange("F5:F").clearDataValidations();
linkDatabase.getRange("G5:G").clearDataValidations();
linkDatabase.getRange("F5:F").clearContent()
linkDatabase.getRange("G5:G").clearContent()
}
else if(linkDatabase.getRange("F5:F").isBlank()==true){
linkDatabase.getRange("G5:G").clearDataValidations();
linkDatabase.getRange("G5:G").clearContent()
}
}

谷歌表单的链接[更新已解决的答案]：https://docs.google.com/spreadsheets/d/1-Oz4F02UnHrf9x_tBZLNU2BK5aMUHdfFgKnhjANMeTI/copy

Answer 1

您可以参考该示例代码：

//Sheet = Link Database - Creating suitable dependant Dropdown 
function onEdit(e){
// YOUR ORIGINAL CODE HERE..... //

if(start.getName() == "Link Database" ){

//Get list of filtered links in B5:B
var filteredLinks = linkDatabase.getRange("B5:B").getDisplayValues().flat().filter(String);
//Get all links in D5:D
var allLinks = linkDatabase.getRange("D5:D").getDisplayValues().flat().filter(String);
Logger.log(filteredLinks)
Logger.log(allLinks)
//Check if filter criteria was updated, update column C based on column K value
if(current.getColumn()==2 && current.getRow() >= 2 && current.getRow() <= 4){
//Clear content of C5:C
linkDatabase.getRange("C5:C").clearContent();
//Update status of each filtered links
filteredLinks.forEach((link,index) => {
var idx = allLinks.indexOf(link);
if(idx > -1){
//Get status value in column K
var status = linkDatabase.getRange(5+idx,11).getDisplayValue();
//Set status value in column C
linkDatabase.getRange(5+index,3).setValue(status);
}
});
}
//Get the rows being updated using the event object 'e'
Logger.log(JSON.stringify(e));
var rowStart = e.range.rowStart;
var rowCnt = e.range.rowEnd - rowStart;

//Loop each row
for(var i = 0; i <= rowCnt; i++){
var currentRow = rowStart + i;
Logger.log(currentRow);
//Check if status in column C5:C was updated. Update status in column K
if(current.getColumn()==3 && currentRow >= 5){
var status = linkDatabase.getRange(currentRow,current.getColumn()).getDisplayValue();
Logger.log(status);
//Get matching filtered link
var index = currentRow - 5;
//Get the filtered link index in allLink array
var idx = allLinks.indexOf(filteredLinks[index]);
Logger.log(idx);
if(idx > -1){
//link found. Set status in column K
linkDatabase.getRange(5+idx,11).setValue(status);
}else{
//revert modification
current.clearContent();
}
}
//Check if status in column K5:K was updated. Update status in column C
if(current.getColumn()==11 && currentRow >= 5){
var status = linkDatabase.getRange(currentRow,current.getColumn()).getDisplayValue();
Logger.log(status);
//Get matching link
var idx = currentRow - 5;
//Get the filtered link index in allLink array
var index = filteredLinks.indexOf(allLinks[idx]);
Logger.log(index);
if(index > -1){
//link found. Set status in column K
linkDatabase.getRange(5+index,3).setValue(status);
}
}
}

}
}

添加的修改：

1. 当B2, B3, B4中的筛选条件更改时，处理列C5:C中状态的更新
2. 当列C5:C中的状态发生更改时，处理列K5:K中的状态更新
3. 当列K5:K中的状态发生更改时，处理列C5:C中的状态更新

注意：

为了获得要修改的行数，我使用了onEdit事件对象e。然后逐个循环每一行以更新列C或K的状态。

请注意，如果使用编辑器手动运行脚本，则会遇到未定义e的错误对象。这是因为只有在修改/编辑工作表中的单元格时，事件对象才存在。要调试代码，我需要编辑工作表中的单元格/单元格范围，然后检查执行选项卡下的日志。