Replace text in a Google Sheet and maintaining formulas

Question

I have a Google Sheet that I would like to use as a template. A user submits a Form and a OnFormSubmit trigger makes a copy of the template and replaces certain text with the answers. I would like to use this in several applications and the locations of these text fields may change, so I would rather not specify a specific range or Named range for every replacement.

Using some tidy code from this question I can do exactly what I want. Except, formulas that I have in the template are removed using this method. In other words, a cell in the template has =E10*F10 but after the replacement the value is either blank or 0.

Is it possible to fix this within the code that I have? I can't find a solution except to try a different approach perhaps...

function replacetext(target,key,value){
var spread_sheet = SpreadsheetApp.open(target)
var sheet = spread_sheet.getSheetByName('Sewage Flows');
replaceInSheet(sheet,key,value);
}


function replaceInSheet(sheet, to_replace, replace_with) {
  //get the current data range values as an array
  var values = sheet.getDataRange().getValues();
  Logger.log("values: " + values)

  //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(to_replace,replace_with);
    });

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

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

Look at setFormula. Also if the cell has a formula then it starts with "=" so you could check that and not replace those cells. — Zig Mandel, Mar 25 '15 at 05:03

score 0 · Answer 1 · answered Mar 25 '15 at 13:24

This isn't the best solution, it seems really inefficient but it works. I changed the approach from grabbing all values using getValues() to just getDataRange()

Then I just check for a formula and skip those cells. Maybe there is a better way.

function replacetext(target,key,value){
    var spread_sheet = SpreadsheetApp.open(target)
    var sheet = spread_sheet.getSheetByName('Sewage Flows');
    replaceInSheet(sheet,key,value);
}


function replaceInSheet(sheet, to_replace, replace_with) {
  //get the current data range values as an array
  var range = sheet.getDataRange();
  var LastRow = range.getLastRow();
  var LastCol = range.getLastColumn();


  for(var i=0; i<= LastRow; i++){

    for(var k=0; k<= LastCol; k++){
      var formula = sheet.getRange(i+1,k+1).getFormula();

      if(formula == ""){
        var old_value = sheet.getRange(i+1,k+1).getValue().toString()
        var new_value = old_value.replace(to_replace,replace_with);
        Logger.log("old, new: " + old_value + ", " + new_value);
        sheet.getRange(i+1,k+1).setValue(new_value);
      }
    }  
  }  
}

Replace text in a Google Sheet and maintaining formulas

1 Answers1