Google spreadsheet: substitute formula

Question

I've got a sheet with multiple formulas (many many many) referencing another sheet. Since "replace all" doesn't work in formulas, I've tried to make a simple script to do it, but it outputs "unknown range name" at the end.

I've tried adding simple quotes, using setValue with "=" and setFormula without success.

Here's the code:

function changeFormula() {
  var strToFind = "DB!";
  var strToReplace = "'DB2'!";
  var range = SpreadsheetApp.getActiveSheet().getActiveRange();
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {
       cell = range.getCell(i,j);
       var currentFormula = cell.getFormula();

       if (currentFormula.indexOf(strToFind) > 0 )
       {
         var newFormula = currentFormula.replace(strToFind,strToReplace); //currentFormula + " string";
         cell.setValue("\'" + newFormula + "\'");
         cell.setValue("="+ cell.getValue().substring(1,cell.getValue().length-1))
         //cell.setValue(newFormula);
       }
    }
  }
}

Tried this script also, without success: productforums.google.com/forum/#!topic/docs/yPnrYiXDVOU — bb_pt

AdamL AdamL · Accepted Answer · 2014-01-06T22:38:02

You will certainly need setFormula(). This seemed to work for me (I restructured a bit to get and set the formulae as a batch, which should give better performance):

function changeFormula()
{
  var strToFind = "DB!";
  var strToReplace = "'DB2'!";
  var range = SpreadsheetApp.getActiveSheet().getActiveRange();
  var formulae = range.getFormulas();
  for (var i = 0; i < formulae.length; i++) 
  {
    for (var j = 0; j < formulae[0].length; j++)
    {
       if (formulae[i][j].indexOf(strToFind) > -1)
       {
         formulae[i][j] = formulae[i][j].replace(strToFind, strToReplace);
       }
    }
  }
  range.setFormulas(formulae);
}

Google spreadsheet: substitute formula

2 Answers