I am running a script that changes values in a formula with a message box.
var searchtext = Browser.inputBox("Enter search text");
var replacetext = Browser.inputBox("Enter replace text");
var form = ss1.getRange("D3");
var formula = form.getFormula();
var updated =formula;
updated.indexOf(searchtext);
updated = updated.replace(searchtext, replacetext);
form.setFormula(updated);
var form2 = ss1.getRange("D10");
var formula2 = form2.getFormula();
var updated2 =formula2;
updated2.indexOf(searchtext);
updated2 = updated2.replace(searchtext, replacetext);
form2.setFormula(updated2);
As you notice I have to repeat the code for the different ranges I have. In the code above I have D3 and D10 ranges. I have around another 20 ranges that I need to replace formula from. I have created this array to hopefully do them all together while the script runs but I am not seeing any changes. Any ideas why would this be happening?
function dash(){
var ss1 = SpreadsheetApp.getActiveSpreadsheet();
var searchtext = Browser.inputBox("Enter search text");
var replacetext = Browser.inputBox("Enter replace text");
var rangeArray = ss1.setActiveSheet(ss1.getSheetByName("Ranges").getRange("A1:A5").getValues());
var daily = ss1.setActiveSheet(ss1.getSheetByName("Daily"));
for(var i in rangeArray){
var form = daily.getRange(rangeArray[i][0]);
var formula = getRange(form).getFormula();
var updated =formula;
updated.indexOf(searchtext);
updated = updated.replace(searchtext, replacetext);
form.setFormula(updated);}
}
There are a few informtion about your sheet layout that I ignore so I had to make some assumptions... I suppose the ranges you want to process are columns in the sheet so I would do something like this (see comments in code): (I didn't have the opportunity to test this code, it might need some debugging)
function dash(){
var ss1 = SpreadsheetApp.getActiveSpreadsheet();
var searchtext = Browser.inputBox("Enter search text");
var replacetext = Browser.inputBox("Enter replace text");
var rangeArray = ss1.getSheetByName("Ranges").getRange("A1:A4").getValues(); // I suppose these cells contains A1 notation of the useful ranges
var daily = ss1.setActiveSheet(ss1.getSheetByName("Daily"));
Logger.log(rangeArray)
for(var i in rangeArray){
var formula = daily.getRange(rangeArray[i][0].toString()).getFormula();//
Logger.log(formula)
var updated =formula.toString().replace(searchtext, replacetext);
Logger.log(updated)
}
daily.getRange(rangeArray[i][0].toString()).setFormula(updated);//
}
EDIT : removed first code and replaced following your comment and example sheet