Is it possible to use native spreadsheet functions such as sum() or today() in google apps script, when writing code for google Spreadsheet? If so, how do I do it?
Asked
Active
Viewed 1.9k times
3 Answers
23
https://developers.google.com/apps-script/reference/spreadsheet/range#setFormula(String)
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var cell = sheet.getRange("B5");
cell.setFormula("=SUM(B3:B4)");
AshClarke
- 2,990
- 5
- 21
- 27
-
Thank you.It's helpful. – Hsinhsin Hung Jun 27 '19 at 14:09
-
3Agree with Serge's answer that it will be slow and inefficient, but some functionality is VERY expensive to reimplement. Like `query`, so sometimes these gymnastics are indeed necessary. – ArtOfTheSmart Feb 11 '20 at 21:02
13
Google Apps Script is JavaScript, spreadsheets functions are not available and using a workaround like setFormula followed by getValue is not only cumbersome but really slow and inefficient. You will be better inspired to use JavaScript and Google services to manipulate data taken from a spreadsheet and write the data back in one single setValues().
Let Me Tink About It
- 15,156
- 21
- 98
- 207
Serge insas
- 45,904
- 7
- 105
- 131
2
Also might be good to refer to this article about using Javascript objects to read and write to cells. I can't think of any good reason for using functions in VBA or Apps Script unless they are doing so indirectly through reading a cell value. https://developers.google.com/apps-script/guides/sheets#reading