What do I need to change in my script to find and replace all instances of a value in the range A1:G on the Original Sheet with the new value in B4 on the New Sheet?

Currently, the script looks at the value in B2 on the New Sheet, checks it against the range A1:G on the Original Sheet, but only replaces the first found value with the value in B4 on the New Sheet.

My Script

  function replaceIds() {
  const newss = SpreadsheetApp.openById("1L_aug9TpiD4JR86uETXeLCRyNYTruafF2ad0UaEB8Zo")
  const newSheet = newss.getSheetByName("New Sheet")
  const originalss = SpreadsheetApp.openById("1TlpETQm1Lo7AIi34iNMUmY9gJYVPxt-Y9NQ2eijyLUs");
  const originalSheet = originalss.getSheetByName("Original Sheet")

  const oldIds = newSheet.getRange("B2").getValues().flat()
  const newIds = newSheet.getRange("B4").getValues().flat()

  const rangeToCheck = originalSheet.getRange("A1:G")

  oldIds.forEach(function(id, index) {
    let cell = rangeToCheck.createTextFinder(id).findNext()
    
    if (cell) {
      cell.setValue(newIds[index])
    }
  })
}

I would like all instances of the value in cell B2 on the New Sheet found in the range A1:G on the Original Sheet to get replaced.

What lines do I need to modify and what do I replace them with?

I have seen similar questions but cannot figure out to to implement the answers.

CodePudding user response：

Use .findAll(), like this:

  oldIds.forEach((id, index) => {
    const cells = rangeToCheck.createTextFinder(id).findAll();
    cells.forEach(cell => cell.setValue(newIds[index]));
  });