2

I have a Google Sheet filled with League of Legends champions names in Korean. I have another sheet with 2 columns, where I have the name in Korean in the first column and the name in English in the other one.

I want to replace all the Korean names in the first sheet by its equivalent in English. It sounds like a LOOKUP for each cell but I don't know how I can automate that.

Example Sheet: https://docs.google.com/spreadsheets/d/1LAflwQtkqCymqh0khimIQkbgSGaPANE-zg_H2hoU7t8/edit?usp=sharing

I have the cells in Korean in the first sheet called Example and in the second sheet Reference, I have 2 columns (Korean & English).

Jacques-Guzel Heron
  • 2,480
  • 1
  • 7
  • 16
Hajin
  • 21
  • 3
  • What do you mean by a sheet and if it an excel sheet - what it has to do with javascript? In javascript terms - if you have the first sheet as a string or array of strings and the second one is an object where the key is Korean name and value is English name, you can simply call the `replace` function which will find matches in a string of the key and replace with the proper value. – Pavlin Petkov Feb 11 '20 at 08:48
  • I'm working on google sheets and I put javascript as it's the language used by the Google Sheets' script editor tool. – Hajin Feb 11 '20 at 08:52
  • Edited the hashtags for you. There is another tag for google apps script specifically. Sadly I am not familiar with it to give you more specific advice, but it might be worth checking what native `replace` function does and if it might be of any use to you – Pavlin Petkov Feb 11 '20 at 08:55
  • share a copy of your sheet – player0 Feb 11 '20 at 09:23
  • look into VLOOKUP - https://stackoverflow.com/a/60164380/5632629 – player0 Feb 11 '20 at 09:24
  • Added an example in the original post! Thank you for your help – Hajin Feb 11 '20 at 09:40
  • Hi there @Hajin! I can't access the example spreadsheet. Please, make it public (*share with anyone*) so we all can study it to help you better. – Jacques-Guzel Heron Feb 11 '20 at 09:55
  • oh right! Done, thanks @Jacques-GuzelHeron – Hajin Feb 12 '20 at 08:08

1 Answers1

0

After reading your question I wrote a code that fulfills your expectations. This is the code in question:

function koreanTranslator() {
  var spreadsheetID = "{SPREADSHEET ID}";
  var koreanSheet = SpreadsheetApp.openById(spreadsheetID).getSheetByName(
    "Example");
  var koreanCells = koreanSheet.getRange(1, 1, koreanSheet.getLastRow(),
    koreanSheet.getLastColumn()).getValues();
  var equivalencesSheet = SpreadsheetApp.openById(spreadsheetID).getSheetByName(
    "Reference");
  var equivalencesCells = equivalencesSheet.getRange(2, 1, equivalencesSheet
    .getLastRow(),
    2).getValues();

  for (var i = 0; i < koreanCells.length; i++) {
    for (var j = 0; j < koreanCells[0].length; j++) {
      for (var k = 0; k < equivalencesCells.length; k++) {
        if (koreanCells[i][j] == equivalencesCells[k][0]) {
          koreanSheet.getRange(i + 1, j + 1).setValue(equivalencesCells[k][1]);
        }
      }
    }
  }
}

The code will first open the spreadsheet with .openById(), continuing by opening both sheets with .getSheetByName(). After that, it will read both tables with .getRange() (calculating the size of the table with .getLastColumn() and .getLastRow(), so you can use bigger or shorter tables without changing the code) and .getValues().

Finally, the code will iterate by each cell in the korean sheet and compare it with the dictionary sheet; and if it finds a coincidence it will update the korean sheet with .setValue(). Please, don't hesitate to comment on my answer for further help or more clarification.

Jacques-Guzel Heron
  • 2,480
  • 1
  • 7
  • 16
  • I get an error line 3 and koreanSheet is undefined when I debug. (And all the other var like koreanCells, equivalencesCells etc. Any idea why? – Hajin Feb 17 '20 at 08:47