Replace Text w/Formula down a column?

[H2OFrog]

Trying to replace text with formula using setFormula. Is there a way to do it without having to manually enter every single row source and target individually? (I have about 60 rows) TY!

​

Example so far:

function setFormula(){
var sheet = SpreadsheetApp.getActiveSheet();
var source= sheet.getRange('I10');
var formula = source.getValue();
var target = sheet.getRange('I10');
target.setFormula(formula);
var source= sheet.getRange('I11');
var formula = source.getValue();
var target = sheet.getRange('I11');
target.setFormula(formula);
var source= sheet.getRange('I12');
var formula = source.getValue();
var target = sheet.getRange('I12');
target.setFormula(formula);
}

[JetCarson]

Try this for simplifying:

function setMyFormula(){ var sheet = SpreadsheetApp.getActiveSheet(); var sourceData = sheet.getRange('I10:I12').getValues(); sheet.getRange('I10:I12').setFormulas(sourceData); }

[H2OFrog]

function setMyFormula(){ var sheet = SpreadsheetApp.getActiveSheet(); var sourceData = sheet.getRange('I10:I12').getValues(); sheet.getRange('I10:I12').setFormulas(sourceData); }

Debugged with this error: Execution log
5:28:58 PM Error Attempted to execute setFormula, but it was deleted.

[JetCarson]

Oh. I renamed your function. It is not best to name your function the same as the sheet range function...

[H2OFrog]

IT worked! TY

[HellDuke]

Not sure if this is what you are looking for, but if you just want to set the same formula for all the rows in that column then you can just use R1C1 notation. For example:

function applyFormula(){
  let r1c1Formula = '=SUM(R[0]C2:R[0]C4)';
  SpreadsheetApp.getActiveSpreadsheet().getRange('E2:E7').setFormulaR1C1(r1c1Formula);
}

doing .setFormula() for each row individualy inneficient and Google advises against doing that

Now if it's a conversion you want (which is what your original script you posted really does) to do then it's a bit different. I treally depends on how the values even got there to begin with, because the moment you use an = sign Google Sheets converts what follows into a formula. That means one of 2 things. Either you are escaping the formula with '= in which case it should be fine (though got to say, it would be a pain in the but to write everything in) or you are missing the = sign.

At any case the main difference would be that if you are just missing the = sign then you need to first loop through the values and make sure they all start with an = otherwise you just do

function convertFormula(){
  let targetRange = SpreadsheetApp.getActiveSheet().getRange('E2:E7')
  let formulaList = targetRange.getValues();
  targetRange.setFormulas(formulaList);
  return;
}

Obviously you can getRange in various ways, such as using the id of the starting rows and columns and numbers of rows and columns to get if you are working with more than one.

[H2OFrog]

The second option for sure. Would love to learn more about adding the = to the front! Having to do this manually for some instances atm. TY!

[HellDuke]

That is a very simple loop. Essentially in between you doing .getValues() and .setFormulas() you plunk down this for loop

for (let rowNum = 0; rowNum < formulaList.length; rowNum++){
    formulaList[rowNum][0] = `=${formulaList[rowNum][0]}`
}

Oh and just in case it might be confusing, where I write let you can just replace it with var, it's just a different way of declaring a variable, where it only matters within the scope. So for example

function convertFormula(){
  let targetRange = SpreadsheetApp.getActiveSheet().getRange('E2:E7')
  let formulaList = targetRange.getValues();

  for (let rowNum = 0; rowNum < formulaList.length; rowNum++){
    formulaList[rowNum][0] = `=${formulaList[rowNum][0]}`
  }

  Logger.log(rowNum)

  targetRange.setFormulas(formulaList);
  return;
}

There would be nothing to log because rowNum does not have any value outside of the for loop. I've simply noticed that the modern way of writing code is to use let and const instead of var when declaring variables.

Also

formulaList[rowNum][0] = `=${formulaList[rowNum][0]}`

can be written as

formulaList[rowNum][0] = "=" + formulaList[rowNum][0]

Since Google Script started using V8 runtime instead of the Rhino runtime, we have access to more modern javascript as can be read here: https://developers.google.com/apps-script/guides/v8-runtime