A modified Google App Script hack to normalize Vietnamese characters into ASCII

normalize-ascii-google-sheet-README.md

Example Google Apps Script functions to normalize non-ASCII characters and insert a timestamp, when a new row is created.

Basically I sloppily added various Vietnamese accented characters to this Gist example: https://gist.github.com/akora/51b2933a2554776d7144#gistcomment-2936646

Blogpost about Apps Script in general, onEdit and timestamps here: http://blog.danwin.com/how-to-automatically-timestamp-a-new-row-in-google-sheets-using-apps-script/

normalize-vietnamese-characters.js

// modified from: 
// https://gist.github.com/akora/51b2933a2554776d7144#gistcomment-2936646
var VIET_CHARMAP = [
    ["a", 'ăáắấàằầảẳẩãẵẫạậặ'],
    ["d", "đ"],
    ["e", 'éếèềẻểẽễẹệ'],
    ["i", "íìỉĩị"],        
    ["o", "ơóốòồỏổõỗọợộớờởỡ"],        
    ["u", "ưúứùừủửũữụự"],        
    ["y", "ýỳỷỹỵ"]      
]
var VIET_ACCENTED = VIET_CHARMAP.reduce(function(i, v){ return i + v[1]}, '') +
    VIET_CHARMAP.reduce(function(i, v){ return i + v[1].toUpperCase()}, '');

var VIET_NORMED = VIET_CHARMAP.reduce(function(i, v){return i + v[0].repeat(v[1].length)}, '') +  
    VIET_CHARMAP.reduce(function(i, v){return i + v[0].toUpperCase().repeat(v[1].length)}, '')

var ACCENTED = VIET_ACCENTED + 'ÀÁÂÃÄÅàáâãäåÒÓÔÕÕÖØòóôõöøÈÉÊËèéêëðÇçÐÌÍÎÏìíîïÙÚÛÜùúûüÑñŠšŸÿýŽžęłćń诹żŁ';
var REGULAR = VIET_NORMED + 'AAAAAAaaaaaaOOOOOOOooooooEEEEeeeeeCcDIIIIiiiiUUUUuuuuNnSsYyyZzelcncZazL';
var REGEXP = new RegExp('[' + ACCENTED + ']', 'g');


function replaceDiacritics(rng) {
  function charRep(match) {
    var p = ACCENTED.indexOf(match);
    return REGULAR[p];
  }
  
  if (typeof rng === 'object' && rng.length !== undefined) { // if rng is an array
    return rng.map(function(cell) { return replaceDiacritics(cell); });
  }
  else {
    return rng.replace(REGEXP, charRep);
  }
};

z-everything.js

// my entire Google App Script: given a sheet named "words" and a first column named "word",
//   automatically insert the current datetime and a ascii-normalized version of the value in the "word" column
// more info here: 
var SHEET_NAME = 'words';
var WORD_HEADER = 'word';
var DATETIME_HEADER = 'datetime';
var NORMWORD_HEADER = 'normalized_word';

function getColByHeader(headerval){
  // https://stackoverflow.com/questions/31214352/how-to-use-a-column-header-to-reference-a-cell-in-google-apps-script-spreadsheet
  var headers = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME).getDataRange().getValues().shift();
  var colindex = headers.indexOf(headerval);
  return colindex+1;
}

function insertTimestamp(){
  var ss = SpreadsheetApp.getActiveSheet();
  var cell = ss.getActiveCell();
  var datecell = ss.getRange(cell.getRowIndex(), getColByHeader(DATETIME_HEADER));
  // Conditions: 
  //  - edited (active) cell is in the first column
  //  - edited cell is not blank
  //  - corresponding datetime cell is blank 
  //  - active sheet is named [SHEET_NAME]
  if (cell.getColumn() == getColByHeader(WORD_HEADER) && !cell.isBlank() && datecell.isBlank() && ss.getName() == SHEET_NAME) {      
    datecell.setValue(new Date()).setNumberFormat("yyyy-MM-dd hh:mm");
  }
};

function insertNormWord(){
  var ss = SpreadsheetApp.getActiveSheet();
  var cell = ss.getActiveCell();
  var target_cell = ss.getRange(cell.getRowIndex(), getColByHeader(NORMWORD_HEADER));

  if (cell.getColumn() == getColByHeader(WORD_HEADER) && !cell.isBlank() && ss.getName() == SHEET_NAME) {      
    target_cell.setValue(replaceDiacritics(cell.getValue()));
  }
};

// modified from: 
// https://gist.github.com/akora/51b2933a2554776d7144#gistcomment-2936646
var VIET_CHARMAP = [
    ["a", 'ăáắấàằầảẳẩãẵẫạậặ'],
    ["d", "đ"],
    ["e", 'éếèềẻểẽễẹệ'],
    ["i", "íìỉĩị"],        
    ["o", "ơóốòồỏổõỗọợộớờởỡ"],        
    ["u", "ưúứùừủửũữụự"],        
    ["y", "ýỳỷỹỵ"]      
]
var VIET_ACCENTED = VIET_CHARMAP.reduce(function(i, v){ return i + v[1]}, '') +
    VIET_CHARMAP.reduce(function(i, v){ return i + v[1].toUpperCase()}, '');

var VIET_NORMED = VIET_CHARMAP.reduce(function(i, v){return i + v[0].repeat(v[1].length)}, '') +  
    VIET_CHARMAP.reduce(function(i, v){return i + v[0].toUpperCase().repeat(v[1].length)}, '')

var ACCENTED = VIET_ACCENTED + 'ÀÁÂÃÄÅàáâãäåÒÓÔÕÕÖØòóôõöøÈÉÊËèéêëðÇçÐÌÍÎÏìíîïÙÚÛÜùúûüÑñŠšŸÿýŽžęłćń诹żŁ';
var REGULAR = VIET_NORMED + 'AAAAAAaaaaaaOOOOOOOooooooEEEEeeeeeCcDIIIIiiiiUUUUuuuuNnSsYyyZzelcncZazL';
var REGEXP = new RegExp('[' + ACCENTED + ']', 'g');


function replaceDiacritics(rng) {
  function charRep(match) {
    var p = ACCENTED.indexOf(match);
    return REGULAR[p];
  }
  
  if (typeof rng === 'object' && rng.length !== undefined) { // if rng is an array
    return rng.map(function(cell) { return replaceDiacritics(cell); });
  }
  else {
    return rng.replace(REGEXP, charRep);
  }
};


function onEdit(e) {  
  insertTimestamp();
  insertNormWord();
 
};