Replace text in Google Sheet depending on the value of another column-CodePudding

Been struggling to find a way to get this done faster, at the moment I am using mailmerg on linux to create multiple versions of the text and manually posting them in my sheet.. Is how could I update lets the message column's placeholder value with column ID's value.

ID	Message
3	sample {ID} not avaliable
4	sample {ID} not avaliable

CodePudding user response：

Replaice {ID} with value from column1

function replaceId() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");//change sheet name
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, 2).getDisplayValues();
  let a = vs.map(r => {
    r[1] = r[1].replace('{ID}', r[0]);//returns a new string
    return [r[1]];
  });
  sh.getRange(2, 2, a.length, 1).setValues(a);
}

String.replace()

Before:

ID	Message
3	sample {ID} not avaliable
4	sample {ID} not avaliable

After:

ID	Message
3	sample 3 not avaliable
4	sample 4 not avaliable