Replace formula of a cell with script

Question

I am trying to replace some part of a formula in cell D3 of a spreadsheet, but I can't seem to do it. The formula in D3 is very long, but I only need to replace what would be searchtext variable and replace it with replacetext variable. Any ideas? Here's my code.

function dashboards(){ 

var ss1 = SpreadsheetApp.getActiveSpreadsheet();
var origSheet1 = ss1.getSheetByName('Daily');
var searchtext = Browser.inputBox("Enter search text");
var replacetext = Browser.inputBox("Enter replace text");
var form = origSheet1.getRange("D3").getFormulaR1C1();
form.indexof(searchtext);
var updated = form.replace(searchtext, replacetext);
form.setFormula(updated);}

Answer 1

You're not far off. The problem is that form, the below, is a String, not a reference to your Range.

var form = origSheet1.getRange("D3").getFormulaR1C1();

You can see this by inserting

Logger.log(form + "; type: " + typeof form); //String

after that line and checking the log in the Script Editor.

You just need to change

form.setFormula(updated);

to

origSheet1.getRange("D3").setFormulaR1C1(updated);

to update the actual range.

Answer 2

Copy the code below and run it via Script Manager or a menu item.

It operates on whatever the selected range is, whether it's a single cell or extends over multiple rows & columns.

It pops up a toast message to tell you when the procedure has finished but it leaves the UiInstance open in case you want to do more replacing.

You can keep it open and perform multiple search/replace in formulas on multiple selections or the same search on different sheets.

function handySRF() { // script composed by [email protected]
  var ss = SpreadsheetApp.getActive();
  var app = UiApp.createApplication().setHeight(200).setWidth(270).setTitle('Search and Replace In Formulas');

  var panel = app.createAbsolutePanel().setId('panel').setHeight(198).setWidth(268)
      .setStyleAttribute('background', 'lightCyan');

  var lblSearch = app.createLabel('Search for:').setId('lblSearch');
  var txtSearch = app.createTextBox().setId('txtSearch').setName('txtSearch');

  var lblReplace = app.createLabel('Replace with:').setId('lblReplace');
  var txtReplace = app.createTextBox().setId('txtReplace').setName('txtReplace');

  var handler = app.createServerHandler('btnStartSearch'); 
  var btnStartSearch = app.createButton('Start Search').addClickHandler(handler)
      .setStyleAttribute('background', 'lightGreen');
  handler.addCallbackElement(panel);

  var handler2 = app.createServerHandler('btnCloseWindow'); 
  var btnCloseWindow = app.createButton('Close Window').addClickHandler(handler2)
      .setStyleAttribute('background', 'lightYellow');
  handler2.addCallbackElement(panel);

  panel.add(lblSearch, 10, 6)
  panel.add(txtSearch, 10, 33)
  panel.add(lblReplace, 10, 75)
  panel.add(txtReplace, 10, 100)
  panel.add(btnStartSearch, 10, 151)
  panel.add(btnCloseWindow, 130, 151)
  app.add(panel);
  ss.show(app);
};

function btnStartSearch(e) {
  var ss = SpreadsheetApp.getActive();
  var app = UiApp.getActiveApplication();

  var search = e.parameter.txtSearch;
  var replace = e.parameter.txtReplace;

  var rows = ss.getActiveSelection();
  var numRows = rows.getNumRows();
  var formulas = rows.getFormulas();
  var newFormulas = [];

  for (var i = 0; i <= numRows - 1; i++) {
    var oldData = formulas[i];
    var newData = [];
    for (var j=0; j<oldData.length; ++j) {
        var item = oldData[j].replace(new RegExp(search, "g"), replace);
        newData.push(item);
    }
    newFormulas.push(newData);
  }
  rows.setFormulas(newFormulas);
  var str = 'Finished replacing ' + search + ' with ' + replace;
  ss.toast(str, '', 2);
};


function btnCloseWindow(e) {
  var ss = SpreadsheetApp.getActive();
  var app = UiApp.getActiveApplication();
  app.close();
  return app;
};