I am trying to find and replace data in column F2:F with the values stated in the array. But the fact is that I am missing something here, since nothing happen when the code is executed.

function cleanbbimport() {
  const rangeToModify = SpreadsheetApp.getActive().getRange('BDD!F2:F');
  const replaceWith = [
    ['611720', '446'],
    ['601420', '540c'],
    ['600690', '148c'],
    ['600190', '162c'],
    ['601260', '232c'],
    ['601480', '292c'],
    ['600620', '331c'],
    ['600960', '372c'],
    ['600440', '408c'],
    ['601160', '419c'],
    ['611630', '428c'],
    ['601110', '441c'],
    ['601130', '502c'],
    ['601510', '521c'],
    ['601420', '540c'],
    ['600810', '599c'],
    ['600320', '624c'],
    ['601720', '633c'],
    ['602270', '678c'],
    ['600870', '711c'],
  ];
  let values = rangeToModify.getDisplayValues();
  replaceWith.forEach(tuple =>
    values = values.map(row => row.map(value =>
      value.replace(tuple[0], tuple[1])
    ))
  );
  rangeToModify.setValues(values);
}

CodePudding user response：

I just put column rep[0] in column F

function cleanbbimport() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const rg = sh.getRange("F2:F"   sh.getLastRow());
  const rep = [
    ['611720', '446'],
    ['601420', '540c'],
    ['600690', '148c'],
    ['600190', '162c'],
    ['601260', '232c'],
    ['601480', '292c'],
    ['600620', '331c'],
    ['600960', '372c'],
    ['600440', '408c'],
    ['601160', '419c'],
    ['611630', '428c'],
    ['601110', '441c'],
    ['601130', '502c'],
    ['601510', '521c'],
    ['601420', '540c'],
    ['600810', '599c'],
    ['600320', '624c'],
    ['601720', '633c'],
    ['602270', '678c'],
    ['600870', '711c'],
  ];
  let vs = rg.getValues();
  let fA = rep.map(r => r[0]);
  let rA = rep.map(r => r[1]);
  vs.map((r,j) => {
    fA.forEach((e,i) => {
      let idx = r[0].toString().indexOf(e);
      if(~idx) {
        vs[i][0] = vs[i][0].replace(e,rA[i])
      }
    })
  })
  rg.setValues(vs);
}

Sheet0 Before:

Sheet0 After: