I have a google sheet with a table including City Name and Code as headings. You can see it in the image that I've attached.

I have to use a lot of repetitive city names for this table for some reason. So I need to make a function to select a selection from City name (from any where in 'A' cell) and replace the relevant city code in the Code cell 'B'(in front of the relevant city name) if you can please help me to figure this out. Sorry for my bad English. I`m still learning it as a second language.

city code ex: NY, TK , DL

CodePudding user response：

function ezfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('sheetname');
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, 2).getDisplayValues();
  let vc = vs.map(r => [`${r[1]},${r[0]}`]);
  sh.getRange(2,3,vc.length,vc[0].length).setValues(vc);
  Logger.log(JSON.stringify(vc));
}

CodePudding user response：

You can use a formula. Try to paste this formula into B3 cell:

=ARRAYFORMULA(IFS(A3:A="NEW YORK","NY",A3:A="TOKYO","TK",A3:A="DEHLI","DL",A3:A="",""))