主页 / user-28404155

Kamron Kimbrough's questions

Martin Hope
Kamron Kimbrough
Asked: 2024-11-21 06:42:30 +0800 CST
  • 6

我有一个包含三张工作表的文件。在这三张工作表上，当某个下拉列表选项更改为“清除”时，我希望脚本将该行数据复制到隐藏的存档工作表中，然后在主工作表上清除该行。

当只监视和归档一张工作表时，我可以正常工作。但是我无法让代码正确地处理三张不同的工作表，我想要将这三张工作表归档到三个单独的隐藏归档工作表中。

当我清除一张工作表上的项目时，它会从其他工作表中抓取该行和行并存档这些行，即使我没有选择这些行作为“已清除”。我知道我的代码有些问题，但我不知道如何修复它。

当前代码如下：

function onChange(e) {
  // Define the relevant sheets and columns 
  var mainSheet = e.source.getSheetByName("Individual Licenses");    
  var archiveSheet = e.source.getSheetByName("Individual Archive"); 
  var columnToWatch = 10; 
  var secondSheet = e.source.getSheetByName("Branch Licenses"); 
  var archivebranchSheet = e.source.getSheetByName("Branch Archive"); 
  var columnToWatch = 10; 
  var thirdSheet = e.source.getSheetByName("Company Licenses"); 
  var archivecompanySheet = e.source.getSheetByName("Company Archive"); 
  var columnToWatch = 10; 
// Column J (0-based index)

  // Get the edited range 
  var editedIRange = e.range;
  var editedBRange = e.range;
  var editedCRange = e.range;

  // Check if the edited cell is in the "Milestone" column 
  if (editedIRange.getColumn() == columnToWatch) { 
      // Check if the status is "Cleared" 
      if (e.value == "Cleared") { 
      // Get the entire row of the edited cell 
      var rowData = mainSheet.getRange(editedIRange.getRow(), 1, 1, mainSheet.getLastColumn()).getValues()[0];

      // Find the first empty row in the Archive sheet 
      var archiveFirstEmptyRow = archiveSheet.getLastRow() + 1;

      // Copy the row data to the Archive sheet 
      archiveSheet.getRange(archiveFirstEmptyRow, 1, 1, rowData.length).setValues([rowData]);

      // Delete the row in the Main Data sheet 
      mainSheet.deleteRow(editedIRange.getRow()); 
      } 
    } 

  if (editedBRange.getColumn() == columnToWatch) { 
      // Check if the status is "Cleared" 
      if (e.value == "Cleared") { 
      // Get the entire row of the edited cell 
      var rowData = secondSheet.getRange(editedBRange.getRow(), 1, 1, secondSheet.getLastColumn()).getValues()[0];

      // Find the first empty row in the Archive sheet 
      var archiveFirstEmptyRow = archivebranchSheet.getLastRow() + 1;

      // Copy the row data to the Archive sheet 
      archivebranchSheet.getRange(archiveFirstEmptyRow, 1, 1, rowData.length).setValues([rowData]);

      // Delete the row in the Main Data sheet 
      secondSheet.deleteRow(editedBRange.getRow()); 
      } 
    } 

  if (editedCRange.getColumn() == columnToWatch) { 
      // Check if the status is "Cleared" 
      if (e.value == "Cleared") { 
      // Get the entire row of the edited cell 
      var rowData = thirdSheet.getRange(editedCRange.getRow(), 1, 1, thirdSheet.getLastColumn()).getValues()[0];

      // Find the first empty row in the Archive sheet 
      var archiveFirstEmptyRow = archivecompanySheet.getLastRow() + 1;

      // Copy the row data to the Archive sheet 
      archivecompanySheet.getRange(archiveFirstEmptyRow, 1, 1, rowData.length).setValues([rowData]);

      // Delete the row in the Main Data sheet 
      thirdSheet.deleteRow(editedCRange.getRow()); 
      } 
    } 
}
google-sheets