0

Newbie here asking dumb questions. Trying to teach myself GAS. I picked up this formula from the forum somewhere. It is deliberately simple just to get it working properly. The function works BUT ONLY for the first instance of the strToFind. My attempts to fix this have just made a mess of it. I admit to not fully knowing what I'm doing. Hopefully somebody can help. 

function changeFormula()
{
  var strToFind = "May 16";
  var strToReplace = "Jun 16";
  var range = SpreadsheetApp.getActiveSheet().getActiveRange();
  var formulae = range.getFormulas();
  var ui = SpreadsheetApp.getUi();
  ui.alert('Got here');
  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);
}
user6298472
  • 1
  • 2
  • 1
    `replace("this", "that")` only replaces the first appearance of "this", by design. `replace(/this/g, "that")` replaces globally. There are other approaches, and this has been discussed many times. –  May 06 '16 at 06:08
  • By way of comment, good practice in a spreadsheet should really _hard code_ values like `May 16` into formulas in the first place. Much better to have had a single cell reference where this value would have been set then no script required. https://en.m.wikipedia.org/wiki/Magic_number_(programming) – JSDBroughton May 06 '16 at 06:46

1 Answers1

0

Use Regex to replace all:

formulae[i][j] = formulae[i][j].replace(new RegExp(strToFind,'g'),strToReplace);
Max Makhrov
  • 17,309
  • 5
  • 55
  • 81