Find and replace substrings using an array in google scripts

Question

Is there a way to alter this code to have it examine substrings rather than the whole cell and also globally rather than just once per cell? I am using google scripts to alter a spreadsheet and have to find and replace dozens of terms.

I have very limited javascript skills and have searched all over for something that works. I found some other code that is inefficient and times out when I run it, this works, but does not include substrings or work globally. It has to work globally because I also need to replace "," with ";" for when there are more than 2 countries.

function findReplace() {
//create array
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = ss.getActiveSheet();
var lngLastRow = sheet1.getLastRow();
var lngLastCol = sheet1.getLastColumn();
var shValues = sheet1.getSheetValues(1,1,lngLastRow,lngLastCol);

//for each cell in shValues, perform replacement, and string trim
for (c=0;c<lngLastCol;c++){
for(r=1;r<lngLastRow;r++){
  if(shValues[r][c] != ""){
    var strString = String(shValues[r][c]); 
    var strHeading = String(shValues[0][c]);  

    if(strHeading.indexOf("Country of Origin") >-1 ){
      if(strString == "United Kingdom") (strString = "U.K.");

//set
    shValues[r][c] = strString;

  }
}

}

//set values
var range1 = sheet1.getRange(1,1,lngLastRow,lngLastCol);
range1.setValues(shValues);
}
}

EDIT:
I am trying to replace country names to standardize them with another set of data. So, United Kingdom becomes U.K., USA becomes U.S., all the commas "," become semi-colons ";". There are several more, not all are countries, but this is the main issue. "Bulgaria, France, USA" becomes "Bulgaria; France; U.S."

Here is the other code that DOES account for substrings, but times out about halfway through and does not replace multiple instances of ",". For brevity, this is only 1/2 of the countries on the list in this function.

function fandrCountrynames() {
// standardizes country names
var r=SpreadsheetApp.getActiveSheet().getRange("J2:K");
var rws=r.getNumRows();
var cls=r.getNumColumns();
var i,j,a,find,repl;
find="United States";
repl="U.S.";
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;}
}
}

 // standardizes country names
find="Korea; Republic of";
repl="South Korea";
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;}
}
}

   // standardizes country names

find="Not Specified";
repl=" ";
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;}
}
}


  // standardizes country names

find="Iran; Islamic Republic of";
repl="Iran";
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 what are you trying to replace and what are you trying to replace it with? — nibnut
– nibnut, Commented Jan 24, 2017 at 20:31
I am trying to replace country names to standardize them with another set of data. So, United Kingdom becomes U.K., USA becomes U.S., all the commas "," become semi-colons ";". There are several more, not all are countries, but this is the main issue. — Kat Touschner
– Kat Touschner, Commented Jan 24, 2017 at 20:38
There can be multiple countries in each cell, for example, it might say "Bulgaria, France, USA" where I would need to replace all commas and the USA so that is displays as "Bulgaria; France; U.S." — Kat Touschner
– Kat Touschner, Commented Jan 24, 2017 at 20:40

Karl_S · Accepted Answer · 2017-01-24 22:17:11Z

1

EDIT: This is not fully correct as it was written and posted prior to seeing some comments explaining the situation more. The first part can still work, but would need to loop through an array of values to substitute from and to.

The line below will replace any instance of United Kingdom with U.K. in String(shValues[r][c]):

var strString = String(shValues[r][c]).replace("United Kingdom", "U.K.");

To search all cells in a file you would have to load the sheet into memory and loop through it.

EDIT: The following is not pertinent to the OP based on comments made in the OP, but is left for others.

If you are looking to have this text replaced in a different cell, you can use the standard Google Sheet formula =SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number]) So to have cell D2 show the contents of cell C2, replacing the same strings as above, you would have:

=SUBSTITUTE(C2, "United Kingdom", "U.K.")

If you want to apply this down all of column D place this in cell D2:

=ARRAYFORMULA(IF(ISBLANK(C2:C),,SUBSTITUTE(C2:C, "United Kingdom", "U.K.")))

EDIT 2: After some comments below, we come to: While editing your later supplied code, I found you are calling .getValue() many times. That send a request back to Google's servers to read the file for each call. This makes many calls back to the server and causes the timeout. We limit this by reading all the necessary data from the sheet into memory using r.getValues() to pull all the values from the range r. In your case we are only grabbing 2 columns, J and K. We then loop through this data and loop through an Object of replacements to change all the countries:

function fandrCountrynames() {
  // standardizes country names

  //Define an Object of the replacement strings
  var country = [];
  country[0] = {searchFor: "Not Specified", replacement: " "};
  country[1] = {searchFor: "United Kingdom", replacement: "U.K."};
  country[2] = {searchFor: "United States", replacement: "U.S."};
  country[3] = {searchFor: "Korea; Republic of", replacement: "South Korea"};
  country[4] = {searchFor: "Iran; Islamic Republic of", replacement: "Iran"};

  //Read the items in the range below
  var sheet = SpreadsheetApp.getActiveSheet()
  var r = sheet.getRange("A2:B");
  var sheetData = r.getValues();

  //Loop through each row in the sheet
  for (var i = 0; i < sheetData.length; ++i) {
    //Loop through each repalcement string pair and save the replaced string back to the data object
    for (var j = 0; j < country.length; ++j) {
      var strString = sheetData[i][0].replace(country[j].searchFor, country[j].replacement);
      sheetData[i][0] = strString;
      strString = sheetData[i][1].replace(country[j].searchFor, country[j].replacement);
      sheetData[i][1] = strString;
    }
  }

  //Save the data back to the file
  r.setValues(sheetData);
}

edited Jan 24, 2017 at 22:17

answered Jan 24, 2017 at 20:42

Karl_S

3,5922 gold badges24 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Kat Touschner Over a year ago

Thanks. Will this call the google servers too much? I tried another bit of code that uses a range and this is what timed out, because I need to go through ever cell in two columns, with a dozen or more terms.

Kat Touschner Over a year ago

I should mention this is part of a larger script that makes other changes, so this needs to be in google scripts .gs file, not directly in the spreadsheet.

Karl_S Over a year ago

You have called to the servers in the first 4 lines, where you define the variables. After that you are looping through the value of every cell stored in an object (shValues). If you only need to go through 2 columns, change your definition and test of the column counter to only do those two. So if columns C and D, set c=2 and have the for be c<4. I am thinking your other code is the hangup.

Kat Touschner Over a year ago

I've just edited my original post with the other code that does use substrings but times out.

Karl_S Over a year ago

Save the countries to an Object: country.searchFor="United Kingdom", country.replacement="U.K." and create a for loop inside your for (j=1;j<=cls;j++) { loop. Then you would have 1 instance of that loop in your code as opposed to multiple.

|

Collectives™ on Stack Overflow

Find and replace substrings using an array in google scripts

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related