Google-sheets – Script to automatically change text value

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

In GDocs under Tools > Preferences, I can define automatic corrections, so that if I type l*, it spells low, h* -> high, etc.

In Google Spreadsheet there is nothing like that. Can I do that with script? How?

Best Answer

As Eddy told you, it can be done by using the onEdit trigger.

Code

//global
var ss = SpreadsheetApp.getActiveSpreadsheet();
var name = "Preferences";

function onEdit(e) {
  // clear cache if on preferences sheet
  if(ss.getActiveSheet().getName() == name) {
    CacheService.getPrivateCache().remove(name)
  } else {
    // get all the data
    var prefs = JSON.parse(preferences()); 
    var cValue = e.value, cRange = e.range;  
    var pref = cValue.match(/\b[a-z]+\*\B/);

    // find replacement text
    for(var i=1, iLen=prefs.length; i<iLen; i++) {
      if(pref == prefs[i][1] && prefs[i][0] == "Yes") {
        var text = prefs[i][2];
      }
    } 

    // make the change
    if(text.length == cValue.length) {
      cRange.setValue(prefs[i][2]);
    } else {
      var modcValue = cValue.replace(pref, text);
      cRange.setValue(modcValue);
    }
  }
}

function preferences() {
  // check if cache is available
  var cache = CacheService.getPrivateCache();
  var cached = cache.get(name);
  if (cached != null) {  
    return cached;
  }

  // collect data  
  var data = ss.getSheetByName(name).getDataRange().getValues();
  var jsonData = JSON.stringify(data);
  cache.put(name, jsonData, 21000);
  return jsonData;
}

Explained

The script will first check if the preference sheet has been modified. If yes, then it will clear/remove the cache. After that, it will either retrieve all the data from the references sheet or the cache. Then it will determine which shortcut is to be used, by means of the match and regex. If the criteria are met, the With column value will be added.

Copy the spreadsheet I've prepared and look under Tools, Script editor. There you will find the script. Press the bug button to validate/authenticate the script.

Example

I've created an example file for you: Script to automatically change text value

Note

  • For the moment, it only works in the old version of Google Spreadsheet.
  • Better would be to use a stand-alone script, making use of the ScriptDb. This can be incorporated much more easily into other spreadsheets, via a library.
  • The replacement will only work in combination with an *. Multiple leading characters are allowed (see example).
  • Only one replacement text will be replaced.

Edit (28-12-2013)

The regex, to match the replacement text, was all wrong. Added possibility to change the replacement text from within sentence.

Related Topic