在同一工作表中的两列上交叉更新特定项google工作表的数据

Question

因此，我想在列C和K之间对列D中的每个特定项目(本例中的链接)进行“交叉交谈”(包含来自web的各种链接，从=D5开始)。

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

1. 主数据库 1.工作表图像
2. 过滤数据( D5:D中的链接) 2.我用过的“过滤器”公式--看看

如果我选择"Done"作为C5中的项目(3.免疫学→1.转基因动物→2.生物技术和应用动物学)，我想选择"Done" in K16。，反之亦然，。我希望这一切都能发生。见图：3. C5→K16 & 4. K16→C5

另外，我正在使用Google脚本来处理多行相关下拉列表-

//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()
  }
}

谷歌工作表中的链接--已解决的答案：TBZLNU2BK5aMUHdfFgKnhjanmeti/拷贝

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. 在更改C5:C中的筛选条件时，处理列B2, B3, B4中状态的更新。
2. 更改列K5:K中的状态时，处理C5:C列中状态的更新。
3. 更改列C5:C中的状态时，处理K5:K列中状态的更新。

注意：

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

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