javascript - How to use correctly setFormula in App Script - Stack Overflow

admin管理员组

文章数量:1420213

I have a problem to use correctly setFormula in app script, i tried to use setFormula in a indeterminated range cells but I do not know how to specify that the range of rows be increased and it is not just a specific range. The script that I try to make is a condition in which if in a range of cells there is information, then put the formula in a cell.

function formulas() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 1");
  var rows = activeSheet.getMaxRows();

  for(var  i=7; i <= rows; i++){
    var workingCell = activeSheet.getRange(i, 3).getValue();

     if(workingCell != ""){
       activeSheet.getRange(i, 4).setFormula("=$B$5"); //this is fine
       activeSheet.getRange(i, 5).setFormula("=((100/H7)*I7)/100"); //but this not
  }
 }
}

how can I do it so if it's row 8 is (" = ((100 / H8) * I8) / 100 ") and so on.

EDIT
The problem is that I try to apply it to many cells and that the rows that I add will increase according to the row in which I am placing the formula ... If a formula is added in row D9 and D10 , then The range is H9, I9 and H10, I10

I have a problem to use correctly setFormula in app script, i tried to use setFormula in a indeterminated range cells but I do not know how to specify that the range of rows be increased and it is not just a specific range. The script that I try to make is a condition in which if in a range of cells there is information, then put the formula in a cell.

function formulas() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 1");
  var rows = activeSheet.getMaxRows();

  for(var  i=7; i <= rows; i++){
    var workingCell = activeSheet.getRange(i, 3).getValue();

     if(workingCell != ""){
       activeSheet.getRange(i, 4).setFormula("=$B$5"); //this is fine
       activeSheet.getRange(i, 5).setFormula("=((100/H7)*I7)/100"); //but this not
  }
 }
}

how can I do it so if it's row 8 is (" = ((100 / H8) * I8) / 100 ") and so on.

EDIT
The problem is that I try to apply it to many cells and that the rows that I add will increase according to the row in which I am placing the formula ... If a formula is added in row D9 and D10 , then The range is H9, I9 and H10, I10

• javascript
• google-apps-script
• google-sheets

Share Improve this question Follow edited Sep 13, 2018 at 16:08 Leon K. asked Sep 13, 2018 at 15:28 Leon K.Leon K. 11533 silver badges1212 bronze badges 5

• Whats the error message you're getting if there is one? – DevNico Commented Sep 13, 2018 at 15:32
• If it works, but the problem is that I try to apply it to many cells and that the rows that I add will increase according to the row in which I am placing the formula ... If a formula is added in row D9 and D10 , then The range is H9, I9 and H10, I10. – Leon K. Commented Sep 13, 2018 at 15:39
• 1 If understand you correctly this should work: activeSheet.getRange(i, 5).setFormula("=((100/H" + i + ")*I" + i + ")/100"); – DevNico Commented Sep 13, 2018 at 16:14
• @Nicolas Works!! Thank you very much friend, what I was trying to do was add another for and trying to remove the setFormula, I did not see that the solution was in the same way I had created, Thank you very much. – Leon K. Commented Sep 13, 2018 at 16:29
• @Nicolas Put your answer so I can upVote it as an answer that helped me. – Leon K. Commented Sep 13, 2018 at 16:37

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 3

The simplest "fix" , as pointed out in a ment, is to concatenate your i loop variable into the formula, like this:

function formulas() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 1");
  var rows = activeSheet.getLastRow(); //maxRows consider blank rows, you don't need those

  for(var i=7; i <= rows; i++){
    var workingCell = activeSheet.getRange(i, 3).getValue();

     if(workingCell != ""){
       activeSheet.getRange(i, 4).setFormula("=$B$5");
       activeSheet.getRange(i, 5).setFormula("=((100/H" +i+ ")*I" +i+ ")/100");
     }
  }
}

Anyway, this function executes too many gets and sets against the spreadsheet, and this will perform poorly as your sheet grows. You should try to minimize all your sets and gets by issuing them in bulk, that is, against a bigger range rather than cell-by-cell.

Your use-case has a problem with this approach because you have some blank spots in your range (when workingCell is blank). If setting a "blank" formula for those values is not an issue for you, then you can speed your script greatly by using this:

function formulas() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 1"); //not necessarily active
  var workingCells = sheet.getSheetValues(7, 3, -1, 1); //-1 == lastRow
  var r1c1formulas = [];
  for (var i=0; i < workingCells.length; i++){
     if (workingCells[i][0] != "") {
       r1c1formulas.push(['=R5C2', '=((100/R[0]C[3])*R[0]C[4])/100']);
     } else
       r1c1formulas.push(['=""','=""']);
  }
  sheet.getRange(7, 4, workingCells.length, 2).setFormulasR1C1(r1c1formulas);
}

The 2nd "trick" is to use the formulas in R1C1 notation instead of regular A1 style. Check the setFormulaR1C1 documentation here.

The R1C1 notation may seem daunting at first but is rather simple, I'd say it is simpler then the 'A1' one. I'll try to summarize it here. R is the row number, and C column, and in front of the letter you have the row and column numbers (instead of letter). So =$B$5 is written as =R5C2.

The last thing different is the relative reference. In the A1 notation you just don't place the '$' signs. Which is not really intuitive and not all that flexible when you're trying to set a bunch of formulas at once (exactly your use-case). Because on the A1 the relative formula is a different formula, the references are different =B1 is not the same as =C1 (which could be "the same" if set on two cells in the same row and consecutive columns).

Anyway, on the R1C1 notation the relative reference is counted as number of rows and columns from the cell that is the reference.

So, when you set the formula =H7*I7 to cell E7, you count that H is 3 columns ahead of E and I 4. And it is all on the same row, so zero row difference. Lastly, to write a relative reference you wrap the number in []. Therefore =H7 * I7 on E7 bees =R[0]C[3] * R[0]C[4].

本文标签： javascriptHow to use correctly setFormula in App ScriptStack Overflow