How to Skip the Value

user_8470494 0
Viewed 46

I want to skip or ignore in worksheet 2 if the value is already set as "REFUNDED" in Column R and then continue to look for other match in Column G and then set the value in Column R.

enter image description here

I tried to play around by changing some aspects in the code but it didn't work. Please help to fix this.

function installedOnEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (!(sheet.getSheetName() == "Backend" && range.getRow() > 1 && range.getColumn() == 17 && range.getValue() == "SOLD")) return;
  const srcId = range.offset(0, -16).getValue();
  const spreadsheetIdOfWorksheet2 = "1VEQs6h_vVrI1Hi8lMa9dvwxG0N7A_X-CwT5R9c"; // Please set the Spreadsheet ID of "WorkSheet 2" here.
  const worksheet2 = SpreadsheetApp.openById(spreadsheetIdOfWorksheet2).getSheetByName("Advance");
  const dstValues = worksheet2.getRange("G2:G" + worksheet2.getLastRow()).createTextFinder(srcId).findAll();
  if (dstValues.length > 0) {
     worksheet2.getRangeList(dstValues.map(r => r.offset(0, 11).getA1Notation())).setValue("Sold");
  }
}
google-apps-script javascript
1 Answers

I believe your goal as follows.

  • When your script is run, you want to skip the rows which have the value of "Refunded" at the column "R".

In this case, I would like to propose to modify the script in your if statement.

Modified script:

Please modify your script as follows.

From: 
const dstValues = worksheet2.getRange("G2:G" + worksheet2.getLastRow()).createTextFinder(srcId).findAll();
if (dstValues.length > 0) {
   worksheet2.getRangeList(dstValues.map(r => r.offset(0, 11).getA1Notation())).setValue("Sold");
}
To: 
const lastRow = worksheet2.getLastRow();
const dstValues = worksheet2.getRange("G2:G" + lastRow).createTextFinder(srcId).findAll();
if (dstValues.length > 0) {
  const temp = worksheet2.getRange("R2:R" + lastRow).createTextFinder("Refunded").matchEntireCell(true).findAll().map(e => e.getRow());
  worksheet2.getRangeList(dstValues.reduce((ar, r) => {
    if (!temp.includes(r.getRow())) ar.push(r.offset(0, 11).getA1Notation());
    return ar;
  }, [])).setValue("Sold");
}
  • worksheet2.getLastRow() can be declared only one time.
  • In this modification, in the if statement, the row numbers which have the value of "Refunded" at the column "R" are retrieved, and the range list is created using the retrieved row numbers. By this, the rows which have the value of "Refunded" at the column "R" can be skipped.
user_71086530
Related