Find and replace only on last row of Google Spreadsheet

Question

I am trying to get this function to run on a spreadsheet and find and replace all apostrophes with nothing. Right now it works but it takes too long, right now in my spreadsheet I have 12 rows and it took 128 seconds to run on just those. What I want to try and make this do is only activate on the last row in the spreadsheet.

I tried getting it to work by adding var row = r.getLastRow(); and changing a few spots to use row. I was not able to get it to run when I did that. I am going to have it run every time a form is submitted so it should always be the last row.

I got the code from: https://productforums.google.com/d/msg/docs/7IlOotksJ4I/liXa0SrC-R4J

function fandr() {
  var r=SpreadsheetApp.getActiveSheet().getDataRange();
  var rws=r.getNumRows();
  var cls=r.getNumColumns();
  var i,j,a,find,repl;
  find="'";
  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;}
    }
  }
}

score 1 · Answer 1 · answered Apr 04 '16 at 03:05

1

I expirienced the same performance issue and found that Google encourages getValues over getValue when handling many cells. You get a 2-dimensional array with getValues. See https://developers.google.com/apps-script/reference/spreadsheet/range#getvalues

answered Apr 04 '16 at 03:05

Sangbok Lee

2,132
3
15
33

score 1 · Accepted Answer · edited May 23 '17 at 12:31

In my opinion, best way to replace text with script is to use map. This function was suggested by @serge-insas here. You could modify it to get best performance results and replace only last row values:

function testReplaceInRange(){
    var sheet = SpreadsheetApp.getActiveSheet()
    var lastRow = sheet.getLastRow();
    var DataRange = sheet.getDataRange();

    var range = DataRange.offset(lastRow - 1, 0, 1); // last Data row

    replaceInRange(range,"'","");
}


function replaceInRange(range, to_replace, replace_with) {
  //get the current data range values as an array
  var values = range.getValues();

  // make RegExp
  var Rep = new RegExp(to_replace, 'g');
  //loop over the rows in the array
  for(var row in values){

    //use Array.map to execute a replace call on each of the cells in the row.
    var replaced_values = values[row].map(function(original_value){
      return original_value.toString().replace(Rep,replace_with);
    });

    //replace the original row values with the replaced values
    values[row] = replaced_values;
  }

  //write the updated values to the range
  range.setValues(values);
}

I've also used the trich with regular expressions, suggested by @cory-gross here. It's made in purpose to raplace all occurrences of to_replace text, not only the first one.

Thank you!! That worked perfectly so far. This runs way faster than the other function. It finishes in 0.5 seconds! I tried using Google Forms data validation first but that seems kind of broken. — maxlowry123, Apr 04 '16 at 11:35

Find and replace only on last row of Google Spreadsheet

2 Answers2