CODEWITHSUNDEEP
google-apps-script
test tester
test tester

Reputation: 7

search if value exist in another sheet and display an alert google appscript

I have the following if condition that checks if the a value entered in a form exists in another sheet and if thats the case it will display an alert to the user, but i also wanted to check if the value was found in another sheet that the cell next to it is not empty

 var valueToSearch2 = formS.getRange("B5").getValue();
 var logSValues = logS.getDataRange().getValues();
 if(logSValues.filter(row => row.includes(valueToSearch2)).length)
{
 SpreadsheetApp.getUi().alert("Stop Processing");
 return
}

Upvotes: 0

Views: 211

Answers (2)

Cooper
Cooper

Reputation: 64042

Try this:

function checkTheEntireSheet() {
  const ss = SpreadsheetApp.getActive();
  const formS = ss.getSheetByName('Sheet0');
  const vts = formS.getRange("B5").getValue();
  const logS = ss.getSheetByName('Sheet1');
  let tf = logS.createTextFinder(vts).findAll();
  if (tf.length > 0) {
    SpreadsheetApp.getUi().alert("Stop Processing");
  }
}

Upvotes: 1

GoranK
GoranK

Reputation: 1668

const valueToSearch2 = formS.getRange("B5").getValue();
const logSValues = logS.getDataRange().getValues();

const found = logSValues.some(row => {
  const foundIndex = row.findIndex(cell=>cell==valueToSearch2)
  if(foundIndex<0){
    return false // search term not found
  }
  const emptyRight = !row[foundIndex+1]
  const emptyLeft = foundIndex>0 && !row[foundIndex-1]
  // additional condition, must be also empty either to the right or to the left
  return emptyLeft || emptyRight
})

if(found){
 SpreadsheetApp.getUi().alert("Stop Processing");
 return
}

Upvotes: 1

Related Questions