CODEWITHSUNDEEP
google-apps-script
Abe
Abe

Reputation: 11

Google Script that deletes Google Sheets Row if first 10 Columns are Duplicated

I'm trying to create a script that will search columns A:J within a row and if any other rows have those same columns, it will delete the new entire row.

I have a spreadsheet that has data importing rows into the spreadsheet into columns A to J. We then manually input text into columns K to P.

If new data imports and matches those first 9 columns, we need to simply delete that new row and keep the existing one. I found this script online that works perfectly, except it's not looking for a range of columns. Rather, it's looking at the entire row

// [START apps_script_sheets_remove_duplicates]
/**
 * Removes duplicate rows from the current sheet.
 */
function removeDuplicates() {
 
  // [START apps_script_sheets_sheet]
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  // [END apps_script_sheets_sheet]
 
  // [START apps_script_sheets_new_data]
  var newData = [];
  // [END apps_script_sheets_new_data]

  for (i in data) {
    var row = data[i];
    var duplicate = false;
    for (j in newData) {
      if (row.join() == newData[j].join()) {
        duplicate = true;
      }
    }
  
    // [START apps_script_sheets_duplicate]
    if (!duplicate) {
      newData.push(row);
    }
    // [END apps_script_sheets_duplicate]
  }
  
  // [START apps_script_sheets_clear]
  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
  // [END apps_script_sheets_clear]

}
// [END apps_script_sheets_remove_duplicates]

Upvotes: 0

Views: 72

Answers (1)

Cooper
Cooper

Reputation: 64100

Try this: Reference:

var row = data[i].slice(0,10);

So perhaps this will work for you:

for (i in data) {
    var row = data[i];
    var r10=row.slice(0,10);
    var duplicate = false;
    for (j in newData) {
      if (r10.join() == newData[j].join()) {
        duplicate = true;
      }
    }

    // [START apps_script_sheets_duplicate]
    if (!duplicate) {
      newData.push(r10);
    }
    // [END apps_script_sheets_duplicate]
  }

Upvotes: 0

Related Questions