Is there a way to set a cell to a certain value if it is set to a certain value in Google Apps Scrip-CodePudding

I want to get all the data in one of my sheets and check for a specific value. Then I want to set the cell with that specific value to another value. I know I can use getDataRange() to get all the values, but how would I loop through and look for a specific value?

CodePudding user response：

Search and Replace

function searchAndReplace(n = 77) {
  const ss = SpreadsheetApp.getActive();
  const ui = SpreadsheetApp.getUi();
  const sh = ss.getSheetByName("Sheet0");
  const sr = 2;//data startrow
  const vs = sh.getRange(sr, 1, sh.getLastRow() - sr   1, sh.getLastColumn()).getValues();
  vs.forEach((r, i) => {
    r.forEach((c, j) => {
      if (c == n) {
        let r = ui.prompt("Enter new Value", ui.ButtonSet.OK_CANCEL)
        if (r.getSelectedButton() == ui.Button.OK) {
          sh.getRange(i   sr, j   1).setValue(r.getResponseText());
        }
      }
    })
  })
}

CodePudding user response：

The easiest method would be to use TextFinder.

Code:

function findReplace() {
  const findValue = "foo";
  const replaceValue = "bar";

  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getDataRange();
  const textFinder = range.createTextFinder(findValue).matchEntireCell(true);
  textFinder.replaceAllWith(replaceValue);
}

All you'd need to do is change the findValue and replaceValue to the values you're looking for.

If you're looking for a code-less option, a find and replace (shift Cmd H) would also do the trick!