How do I add a formula like:
=SUM(A1:A17)
to a range of fields using the Google Apps Script API for Google Sheets?
How do I add a formula like:
=SUM(A1:A17)
to a range of fields using the Google Apps Script API for Google Sheets?
Here's a more general answer.
Suppose you want to populate column B with a formula that is the value of column A, plus 1. For example,the formula in cell B1 would be "=A1 + 1".
Let's stipulate that first row of the range is 1, and the last is 20.
// create an array the same size as the number of rows.
var data = [];
// populate the array with the formulas.
for (var i=0; i < 20; i++)
{
// note that as usual, each element of the array must itself be an array
// that has as many elements as columns. (1, in this case.)
data[i] = ['=A' + (i+1).toString() + ' + 1 ' ];
}
// set the column values.
sheet.getRange(1,2,20,1).setFormulas(data);
Making this work for different numbers of rows, and different starting row, is a matter of using variables instead of the hard-coded 1 and 20 in this example.
Set, Copy & Paste in a range
If you want for example A18 to hold the formula '=SUM(A1:A17)' and the same for columns B to Z you can set the formula in A18 and then copy A18 to B18:Z18. The same thing holds for more complicated ranges. The easiest to get going is to use the macro recorder and then inspect your script.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
sheet.getRange('A18').setFormula('=SUM(A1:A17)');
sheet.getRange('A18').copyTo(sheet.getRange('B18:Z18'));
//There are other parameters to 'copyTo' that you can use if needed
I used the copyTo formula suggested by Frans Van Assche, but used conversions from numeric rows and columns to A1 notation because I didn't have a predefined range. For example, the following code sums each row, not including the header, from column D to however many columns of data were created earlier in the totals column C.
const startRow = 2; // first row to sum
const startCol = 4; // first column to sum
let endCol = sheet.getLastColumn(); // last column with data
let endRow = sheet.getLastRow(); // last row with data
let sumRange = sheet.getRange(startRow, startCol, 1, endCol - startCol + 1); // range for first cell of formula
let copyRange = sheet.getRange(startRow, startCol - 1, endRow - 1, 1); // range to copy formulas into
let sumFormula = "=SUM(" + sumRange.getA1Notation() + ")"; // define formula
let totalsCol = startCol - 1; // this could be whichever column you want the totals to be in, for example, a constant or endCol + 1 if you want the totals at the end
let startCell = sheet.getRange(startRow, totalsCol); // first cell to copy formula into
startCell.setFormula(sumFormula); // add formula to first cell
startCell.copyTo(copyRange); // copy formula from first cell to other range
setValues() is also working in place of setFormulas()
var disht = spss.getSheetByName("DI");
var divals = disht.getRange("A1:G50").getValues();
divals[40][5]= "=round(SUM(F10:F39),0)"; //F41 =round(SUM(F10:F39),0)
divals[42][5]= "=(F42+F41)*(F8/100)"; //F43 =(F42+F41)*(F8/100)
divals[43][5]= '=if(G8>0,(F41+F42)*(G8/100),"")' ; //F44 =if(G8>0,(F41+F42)*(G8/100),"")
divals[44][5]= '=if(isblank($G$7),"",sum($F$41:$F$44)*$G$7*0.01)';//F45 =if(isblank($G$7),"",sum($F$41:$F$44)*$G$7*0.01)
divals[45][5]= '=round(F41,0)+round(F42,0)+round(F43,0)+round(F44,0)+round(F45,0)' ; //F46 =round(F41,0)+round(F42,0)+round(F43,0)+round(F44,0)+round(F45,0)
divals[47][5]= "=F46-F47"; //F48 =F46-F47
divals[44][4]= '=if(isblank($G$7),"","TCS")'; //E45 =if(isblank($G$7),"","TCS")
disht.getRange("A1:G50").setValues(divals);