CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Tom Sawkins
Tom Sawkins

Reputation: 361

Delete Row if Match found from another spreadsheet - Google Sheets

I would like to delete any rows of data from spreadsheet 2 if a Match is found in spreadsheet 1. In the image below (spreadsheet 1) we have SKU A10114 & New Location J05A1.

enter image description here

In the below Image(Spreadsheet 2) here you can see SKU A10114 at Location J05A1 has 2 line entries.

So the code would delete both lines of A10114 at Location J05A1 ONLY

If A10114 had a different location it would not be deleted

enter image description here

Upvotes: 0

Views: 1785

Answers (1)

Tanaike
Tanaike

Reputation: 201388

I believe your goal as follows.

  • You want to delete rows in Sales sheet, when the values of columns "A" and "F" in Relocation sheet are included in the values of columns "A" and "B" in Sales sheet.

For this, I would like to propose the following flow.

  1. Retrieve the values from Relocation sheet and create an object for searching values.
  2. Retrieve the values from Sales sheet and create an array for deleting the rows.
  3. Delete rows.

When above flow is reflected to the script, it becomes as follows.

Sample script:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  
  // 1. Retrieve the values from `Relocation` sheet and create an object for searching values.
  const sheet1 = ss.getSheetByName('Relocation');
  const valuesSheet1 = sheet1.getRange("A1:J" + sheet1.getLastRow()).getValues()
  .reduce((o, [a,,,,,f]) => Object.assign(o, {[a + f]: true}), {});

  // 2. Retrieve the values from `Sales` sheet and create an array for deleting the rows.
  const sheet2 = ss.getSheetByName('Sales');
  const valuesSheet2 = sheet2.getRange("A2:B" + sheet2.getLastRow()).getValues()
  .reduce((ar, [a,b], i) => {
    if (valuesSheet1[a + b]) ar.push(i + 2);
    return ar;
  }, []).reverse();
  
  // 3. Delete rows.
  valuesSheet2.forEach(r => sheet2.deleteRow(r));
}

Note:

  • When I saw your script in your shared Spreadsheet, from the sheet names in the script, I thought that your script might not be related to this question. So I proposed above sample script.

References:

Upvotes: 2

Related Questions