Replace multiple strings in Google Spreadsheet with script

Question

I want to make an script for Google Spreadsheet, in order to replace automatically a series of strings (actually, for translating strings from one language to the other). The thing is that I have to do this everyday, and a Script would allow me to save a lot of time.

I found this script made by Bannager Bong. and it works for one single string, but I would want to make it work for an array of elements:

function fandr() {
   var r=SpreadsheetApp.getActiveSheet().getDataRange();
   var rws=r.getNumRows();
   var cls=r.getNumColumns();
   var i,j,a,find,repl;

   find="abc";
   repl="xyz";

   for (i=1;i<=rws;i++) {
      for (j=1;j<=cls;j++) {
         a=r.getCell(i, j).getValue();
         if (r.getCell(i,j).getFormula()) {continue;}
         try {
            a=a.replace(find,repl);
            r.getCell(i, j).setValue(a);
         }
         catch (err) {continue;}
      }
   }
}

so for instance I would like to loop the variables from find=["abc","aaa","ooo"]; and changing those strings to repl=["xyz","UUU","aAa"];

However given my poor skills in programming, I don't know how to procede.

Could you please help me?

Thanks in advance,

FluffyLlemon · Accepted Answer · 2018-05-14 01:42:58Z

I've used a map from the find -> replace objects so it's more clear which values go with which other values. I then loop over each key/value pair and use the same logic as in the original snippet.

function FindAndReplace() {
  var data_range = SpreadsheetApp.getActiveSheet().getDataRange();
  var num_rows = data_range.getNumRows();
  var num_columns = data_range.getNumColumns();

  var find_replace = {
      "abc": "xyz",
      "aaa": "UUU",
      "ooo": "aAa",
  }

  Object.keys(find_replace).map(function(find) {
    var replace = find_replace[find];
    for (var row=1; row <= num_rows; row++) {
     for (var col=1; col <= num_columns; col++) {
        var value = data_range.getCell(row, col).getValue();
        if (data_range.getCell(row, col).getFormula()) {continue;}
        try {
           value = value.replace(find, replace);
           data_range.getCell(row, col).setValue(value);
        }
        catch (err) {continue;}
     }
  }
 });
}

Collectives™ on Stack Overflow

Replace multiple strings in Google Spreadsheet with script

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related