0

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);
       }
    }
  }
}
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
bb_pt
  • 46
  • 1
  • 6
  • Tried this script also, without success: http://productforums.google.com/forum/#!topic/docs/yPnrYiXDVOU – bb_pt Jan 06 '14 at 14:26

2 Answers2

0

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);
}
AdamL
  • 23,691
  • 6
  • 68
  • 59
  • Thanks for the solution. In the meantime I moved the spread sheet to the new version of google spreadsheets, which allows find/replace in cell formulae. – bb_pt Jan 08 '14 at 09:18
  • I tried (in the new spreadsheet version) and it seemed to work, but only replaces the first instance of strToFind in the strToReplace, which has multiple references to strToFind. Suggest using `.replace(/strToFind/g, strToReplace)` (from [link](http://stackoverflow.com/questions/2116558/fastest-method-to-replace-all-instances-of-a-character-in-a-string)) – bb_pt Jan 08 '14 at 09:27
0

This is what you can do to keep the values of all your formulas and remove the formulas. That way you can use the sheet away from the tabs it references.

  1. Duplicate Tab (so you don't blow out original with formulas in case you still need it in that format)
  2. Select all Cells on duplicate Tab and copy
  3. with all cells still selected Paste special values only from edit pulldown

The values stay and the formulas go away. Then you can copy to other workbooks etc. I know it is pretty simple

Not sure if that is what you need but it is what I needed and I figured I would share for you or anyone else that might wander by like I did.

Good luck

Bruce

user3728496
  • 1