Delete rows based on datas in another tab (Google Apps Script)-CodePudding

I am working on a Sheets file which contains three tabs. The purpose of this file is to create contracts, add data related to these contracts and archive them when the time comes.

The first tab (called "Sheet") allows me to record the contracts and some data thanks to a form made in Apps Script (it acts as a database).

The second tab (called "Sheet2") allows me to retrieve in the first column the different contracts (in a drop-down menu), and to attach other values that were not present in the form. It also contains a checkbox to archive these contracts when the time comes.

The last tab (called "Archiving") allows me to store the archived contracts.

This is what I would like : When one or more contracts are to be archived (checkboxes checked), the script saves the data in the "Archiving" tab, cleans up the row(s) in the second tab and deletes the corresponding contract from the database.

I have a problem with the last step and I don't see how to do it. The script does not delete the right rows and I get the following error message after a while: "Those rows are out of bounds.". As you can see from the screenshot of Sheet2, I would like to be able to delete the contracts in any order I want and this is what is bothering me. If it had been in the same order as the Sheet, I wouldn't have had a problem.

Here is my code:

function archiving(){
   const ss = SpreadsheetApp.getActiveSpreadsheet();
   const sheet = ss.getSheetByName("Sheet");
   const sheet2 = ss.getSheetByName("Sheet2");
   const archiving = ss.getSheetByName("Archiving");
   var d = 0, rowToDelete = 0;

   // Get nonempty's values of Sheet2
   var tValuesSheet2 = sheet2.getRange("C2:H").getValues().filter(d =>d[0] !== '');

   // Get nonempty's values of Sheet
   var tValuesSheet = sheet.getRange("A2:E").getValues().filter(d =>d[0] !== '');

   // For each value of Sheet2
   for (let i = 0; i < tValuesSheet2.length; i   ){

     // If archiving's checkbox is checked
     if(tValuesSheet2[i][5]){

       // Archiving of values in "Archiving" Sheet
       archiving.appendRow(tValuesSheet2[i]);

       // Clear values of Sheet2
       sheet2.getRange("C" (i 2) ":G" (i 2)).clear({contentsOnly: true, skipFilteredRows: true});
       sheet2.getRange("H" (i 2)).setValue('FALSE');

       // ----- PROBLEM HERE : Delete values of Sheet ---- // 
       // Get row of Sheet to delete
       for (let j = 0; j < tValuesSheet.length; j   ){
         // If Data is same in Sheet and Sheet 2
         if (tValuesSheet[j][0] == tValuesSheet2[i][0]){
           rowToDelete = j   2;
         }
       }
       sheet.deleteRow(rowToDelete - d);
       d   ;
     }
   }
 }

Could you help me to solve my problem please? Here is the link to the Sheets if needed. Thanks in advance for your help and sorry for my English, I'm not comfortable with it.

CodePudding user response：

Modification points:

In your current script, sheet.deleteRow(rowToDelete - d) is run from the top row. I thought that this might be the reason for your issue.
In your script, appendRow, clear, setValue, and deleteRow are used in the loop. In this case, the process cost will become high. Ref

In this modification, your script is modified by reducing the process cost. The modified script is as follows.

Modified script:

function archiving() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet");
  const sheet2 = ss.getSheetByName("Sheet2");
  const archiving = ss.getSheetByName("Archiving");

  // Retrieve values from "Sheet" and "Sheet2".
  const lastRow = sheet.getLastRow();
  const valuesSheet = sheet.getRange("A2:A"   lastRow).getValues().map(([a]) => a);
  const valuesSheet2 = sheet2.getRange("C2:H"   sheet2.getLastRow()).getValues();

  // Create objects for putting to "Archiving", clearing rows of "Sheet2", unchecking cells of "Sheet2", and deleting rows of "Sheet".
  const { values, clearRows, uncheck, deleteRows } = valuesSheet2.reduce((o, r, i) => {
    const check = r.pop();
    if (check === true) {
      o.values.push(r);
      o.clearRows.push(`C${i   2}:G${i   2}`);
      o.uncheck.push(`H${i   2}`);
      const idx = valuesSheet.indexOf(r[0]);
      if (idx > -1) {
        o.deleteRows.push(idx   1);
      }
    }
    return o;
  }, { values: [], clearRows: [], uncheck: [], deleteRows: [] });

  // Put values to "Archiving".
  archiving.getRange(archiving.getLastRow()   1, 1, values.length, values[0].length).setValues(values);

  // Clear rows of "Sheet2" and uncheck the checkboxes.
  sheet2.getRangeList(clearRows).clear({ contentsOnly: true, skipFilteredRows: true });
  sheet2.getRangeList(uncheck).uncheck();

  // Delete rows of "Sheet".
  const range = sheet.getRange("A2:E"   lastRow);
  const updatedValuesSheet = range.getValues().filter((_, i) => !deleteRows.includes(i   1));
  range.clearContent().setBackground(null); // or clear()
  sheet.getRange(2, 1, updatedValuesSheet.length, updatedValuesSheet[0].length).setValues(updatedValuesSheet);
}

Note:

This sample script is for your provided Spreadsheet. When you change your Spreadsheet, this script might not be able to be used. Please be careful about this.

Modification points:

Modified script:

Note:

References: