Find and Replace a data column with array of values

Question

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);
}

The code you quote seems fine. Consider sharing a publicly editable sample spreadsheet with realistic-looking data. — doubleunary
– doubleunary, Commented Aug 19, 2022 at 3:47

Cooper · Accepted Answer · 2022-08-21 14:59:41Z

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:

COL1	COL2	COL3	COL4	COL5	COL6	COL7	COL8	COL9	COL10
1	2	3	4	5	611720	7	8	9	10
2	3	4	5	6	601420	8	9	10	11
3	4	5	6	7	600690	9	10	11	12
4	5	6	7	8	600190	10	11	12	13
5	6	7	8	9	601260	11	12	13	14
6	7	8	9	10	601480	12	13	14	15
7	8	9	10	11	600620	13	14	15	16
8	9	10	11	12	600960	14	15	16	17
9	10	11	12	13	600440	15	16	17	18
10	11	12	13	14	601160	16	17	18	19
11	12	13	14	15	611630	17	18	19	20
12	13	14	15	16	601110	18	19	20	21
13	14	15	16	17	601130	19	20	21	22
14	15	16	17	18	601510	20	21	22	23
15	16	17	18	19	601420	21	22	23	24
16	17	18	19	20	600810	22	23	24	25
17	18	19	20	21	600320	23	24	25	26
18	19	20	21	22	601720	24	25	26	27
19	20	21	22	23	602270	25	26	27	28
20	21	22	23	24	600870	26	27	28	29

Sheet0 After:

COL1	COL2	COL3	COL4	COL5	COL6	COL7	COL8	COL9	COL10
1	2	3	4	5	446	7	8	9	10
2	3	4	5	6	540c	8	9	10	11
3	4	5	6	7	148c	9	10	11	12
4	5	6	7	8	162c	10	11	12	13
5	6	7	8	9	232c	11	12	13	14
6	7	8	9	10	292c	12	13	14	15
7	8	9	10	11	331c	13	14	15	16
8	9	10	11	12	372c	14	15	16	17
9	10	11	12	13	408c	15	16	17	18
10	11	12	13	14	419c	16	17	18	19
11	12	13	14	15	428c	17	18	19	20
12	13	14	15	16	441c	18	19	20	21
13	14	15	16	17	502c	19	20	21	22
14	15	16	17	18	521c	20	21	22	23
15	16	17	18	19	540c	21	22	23	24
16	17	18	19	20	599c	22	23	24	25
17	18	19	20	21	624c	23	24	25	26
18	19	20	21	22	633c	24	25	26	27
19	20	21	22	23	678c	25	26	27	28
20	21	22	23	24	711c	26	27	28	29

Answer to additional question:

function answertoquestion() {
  function cleanbbimport() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const rg = sh.getRange("F2:G" + 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]);
        vs[i][1] = vs[i][1].replace(e,rA[i]);
      }
    })
  })
  rg.setValues(vs);
}
}

I didn't quite know what to put into column F but I figured anything that worked woudld be more helpful than nothing. So I'm glad to hear that it helped.
Last question: If I want to apply the same find and replace to another column, can I replace this line: {const rg = sh.getRange("F2:F" + sh.getLastRow());} by this one: {var lastRow = sh.getLastRow(); var rg = ['F2:F' + lastRow, 'G2:G' + lastRow];} ? I'm getting this error [TypeError: rg.getValues is not a function]

Collectives™ on Stack Overflow

Find and Replace a data column with array of values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related