google apps script - Archive a row after a dropdown status is selected on multiple sheets - Stack Overflow

admin管理员组

文章数量:1336663

I have a file with three sheets. On all three sheets, when a certain dropdown selection is changed to "Cleared" I want the script to copy that row of data over into a hidden archive sheet and then clear that row on the main sheet.

I had this working when it was just one sheet that it was watching and archiving. But I can't get the code to work right for three different sheets that I want archived to three separate hidden archived sheets.

When I clear an item on one sheet, it is grabbing that row and rows from other sheets and archiving those rows even though I haven't selected those as "Cleared". I know something is messed up in my code but I don't know how to fix it.

Here is the current code:

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

I have a file with three sheets. On all three sheets, when a certain dropdown selection is changed to "Cleared" I want the script to copy that row of data over into a hidden archive sheet and then clear that row on the main sheet.

I had this working when it was just one sheet that it was watching and archiving. But I can't get the code to work right for three different sheets that I want archived to three separate hidden archived sheets.

When I clear an item on one sheet, it is grabbing that row and rows from other sheets and archiving those rows even though I haven't selected those as "Cleared". I know something is messed up in my code but I don't know how to fix it.

Here is the current code:

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
• google-apps-script
• triggers
• google-workspace

Share Improve this question Follow edited Nov 21, 2024 at 3:43 Wicket 38.5k99 gold badges7878 silver badges193193 bronze badges asked Nov 20, 2024 at 22:42 Kamron KimbroughKamron Kimbrough 2133 bronze badges 3

• From what I understand, there are 6 sheets in the Google Sheet, and what you'd like to do is whenever Column J is set to Cleared, the entire row with its data would move from their license sheets to their respective archive sheets. Would that be correct? – Saddles Commented Nov 21, 2024 at 0:08
• 1 You are using onChange as a trigger. Event Objects-onChange. Even if the 'change" is an EDIT, the event objects are NOT the same as for the equivalent onEdit trigger. Specifically, e.range is not an event object. 1) Insert this line as the first line of code: Logger.log(JSON.stringify(e)) and then view the Event Objects in the Executions tab when the script is triggered. 2) Why have you chosen to use onChange? – Tedinoz Commented Nov 21, 2024 at 1:15
• Please read How to create a Minimal, Reproducible Example; then edit the question to include sample data as well as an example of a successful outcome. – Tedinoz Commented Nov 21, 2024 at 1:21

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

The reason why When I clear an item on one sheet, it is grabbing that row and rows from other sheets and archiving those rows even though I haven't selected those as "Cleared". is due to the if statements checking if what was edited was Column J and set to Cleared. This would mean that regardless of what sheet you're editing, the other sheets would do the same and what you described.

Here's a modified version of your script that should achieve what you'd like:

// Function name from "onChange(e)" to "onEdit(e)"
function onEdit(e) {
  // Declared variable so that "e.source" doesn't need to be repeated for the sheet names
  var sr = e.source;
  var mainSheet = sr.getSheetByName("Individual Licenses");
  var archiveSheet = sr.getSheetByName("Individual Archive");
  var secondSheet = sr.getSheetByName("Branch Licenses");
  var archivebranchSheet = sr.getSheetByName("Branch Archive");
  var thirdSheet = sr.getSheetByName("Company Licenses");
  var archivecompanySheet = sr.getSheetByName("Company Archive");
  var columnToWatch = 10;
  var editedRange = e.range;
  // Gets the sheet of the edited range
  var ss = editedRange.getSheet();
  // Gets the sheet name of the edited range
  var sh = ss.getName();
  if (sh == mainSheet.getName() || sh == secondSheet.getName() || sh == thirdSheet.getName()) {
    if (editedRange.getColumn() == columnToWatch && e.value == "Cleared") {
      var rowData = ss.getRange(editedRange.getRow(), 1, 1, ss.getLastColumn()).getValues()[0];
      if (sh == mainSheet.getName()) {
        var archiveFirstEmptyRow = archiveSheet.getLastRow() + 1;
        archiveSheet.getRange(archiveFirstEmptyRow, 1, 1, rowData.length).setValues([rowData]);
      } else if (sh == secondSheet.getName()) {
        var archiveFirstEmptyRow = archivebranchSheet.getLastRow() + 1;
        archivebranchSheet.getRange(archiveFirstEmptyRow, 1, 1, rowData.length).setValues([rowData]);
      } else if (sh == thirdSheet.getName()) {
        var archiveFirstEmptyRow = archivecompanySheet.getLastRow() + 1;
        archivecompanySheet.getRange(archiveFirstEmptyRow, 1, 1, rowData.length).setValues([rowData]);
      }
      ss.deleteRow(editedRange.getRow());
    }
  }
}

Note: I've removed the other var columnToWatch = 10; since they're all the same as well as combined var editedIRange = e.range; var editedBRange = e.range;, and var editedCRange = e.range; as var editedRange = e.range;.

OUTPUT

REFERENCE

• onEdit(e)
• Event Objects

本文标签： google apps scriptArchive a row after a dropdown status is selected on multiple sheetsStack Overflow