mardi 24 mars 2015

Replace text in a Google Sheet and maintaining formulas


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);
}




Aucun commentaire:

Enregistrer un commentaire