Using built-in spreadsheet functions in a script

Question

I'm using Google App Script for the first time. I'm using it on a Google Doc spreadsheet.

I'm trying very simple functions, just to learn the basics. For example this works:

function test_hello() {
    return 'hello';
}

But I'm puzzled by this simple one :

function test_today() {
    return today();
}

It makes an #ERROR! wherever I use it. And when I put my cursor on it, it says :

error : ReferenceError: "today" is not defined.

While the today() function works when used directly in the spreadsheet.

Does this mean that in scripts, I cannot use spreadsheet built-in functions? Is there any elegant way around this?

Some spreadsheet functions are quite useful to me (I like weekday() for example).

A non-elegant way could be to create columns to calculate intermediate values that I need, and that can be calculated with spreadsheet functions. But I'd rather avoid something this dirty and cumbersome.

Serge insas · Accepted Answer · 2012-07-26 00:54:40Z

13

Google Apps Script is a subset of JavaScript, spreadsheet functions are currently not supported. For example, if you want to create a function that returns today's date you should write :

function test_today(){
return new Date()
}// note that this will  eventually return a value in milliseconds , you'll have to set the cell format to 'date' or 'time' or both ;-)

syntax is the same as with sheet functions : =test_today() see tutorial

There are many internet ressources on javascript, one of the most useful I found is w3school

edited Jul 26, 2012 at 0:54

answered Jul 26, 2012 at 0:22

Serge insas

46.9k7 gold badges113 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Captain Delano Over a year ago

Do you know if Google has plans to implement spreadsheet functions in their scripting language? In my case, I'm looking at TO_DATE, which is a pain to rewrite in JavaScript.

Serge insas Over a year ago

Not that I know, and I think that it will never be the case.

Doron Behar Over a year ago

That w3school link is dead.

Matthew · Accepted Answer · 2020-05-07 04:50:34Z

5

Google Apps Script still does not (1/7/20) include an API to Google Sheets native functions.

But you can set the formula (native functions) of a cell named as a named range in a spreadsheet.

Then in the GAS:

var nativeOutput = spreadsheet.getRangeByName("outputCell").getValue();

Voila! Your GAS is calling the native function in the cell.

You can send data from the GAS to the native function in the cell, by naming another cell in the sheet (or in any sheet) referred to by the formula in the other cell:

spreadsheet.getRangeByName("inputCell").setValue(inputData);

Your GAS can dynamically create these cells, rather than hardcoding them, eg:

// Create native function, its input and output cells; set input value; use native function's output value:


// Use active spreadsheet.
var spreadsheet = SpreadsheetApp.getActive();


// Name input, output cells as ranges.
spreadsheet.setNamedRange("inputCell", spreadsheet.getRange("tuples!F1"));
spreadsheet.setNamedRange("outputCell", spreadsheet.getRange("tuples!F2"));

var outputCell = spreadsheet.getRangeByName("outputCell");
var inputCell = spreadsheet.getRangeByName("inputCell");


// Set native formula that consumes input cell's value, outputting in formula's cell.
outputCell.setFormula("=WEEKNUM(inputCell)");


// Call native function by setting input cell's value for formula to consume.
// Formula sets its cell's value to formula's output value.
inputCell.setValue(15);

// Consume native function output.
var nativeOutput = outputCell.getValue();
Logger.log("nativeOutput: "+ JSON.stringify(nativeOutput)); // Logs "nativeOutput: 3"

Beware: this technique exposes the code in cells that a spreadsheet user can access/change, and other spreadsheet operations could overwrite these cells.

edited May 7, 2020 at 4:50

answered Feb 8, 2019 at 4:54

Matthew

89112 silver badges20 bronze badges

3 Comments

clozach Over a year ago

I can't seem to find setRangeByName in Google's docs. In fact, your SO answer is one of only 2 Google search results for google sheets "setRangeByName". Am I missing something? 🥴

Matthew Over a year ago

clozach: No, you caught something: my typo. I edited my post "getRangeByName" -> "setRangeByName". The correct call to set a range's name is eg. spreadsheet.setNamedRange("inputCell", spreadsheet.getRange("tuples!F1")); as I correctly typed later in my post.

Dan Loewenherz Over a year ago

Exception: You do not have permission to call setFormula

Wicket · Accepted Answer · 2024-06-13 01:09:11Z

The Spreadsheet Service and the Advanced Sheets Service don't include methods to make the Google Sheets "native" functions available on a Google Apps Script project. This was mentioned in previous answers. As of Jun 12, 2024, there are several open-source code that have implemented spreadsheet formulas using JavaScript. One of them is Formula.js.

From the above link:

Community built JavaScript implementation of most Microsoft Excel formula functions.

To use Formula.js in Google Apps Script the general and broad steps are the following:

Use UrlFetchApp.fetch(...) to get the content from Formula.js (JavaScript Library).
Use eval() to load the library into the execution global object.
Call the spreadsheet function using formulajs.FUNCTION_NAME(parameters) syntax.

Example:

function loadFormulaJs() {
  const response = UrlFetchApp.fetch("https://cdn.jsdelivr.net/npm/@formulajs/formulajs/lib/browser/formula.min.js")
  if(response.getResponseCode() === 200){
    eval(response.getContentText())
  }
}

function test(){
  loadFormulaJs()
  const results = [
    formulajs.DATE(2008, 7, 8),
    formulajs.SUM([1, 2, 3])
  ];
  Logger.log(JSON.stringify(results, null, " "))
}

Disclaimer: I haven't tested this thoroughly yet.

Jean-François Fabre · Accepted Answer · 2020-05-21 19:41:34Z

-2

What the spreadsheet functions can do, Javascript can do. I just have to replace var day_num = weekday() by var day_num = new Date(date).getDay()

Here is the result :

/**
* Writes the day of the week (Monday, Tuesday, etc), based on a date
*/
function day_name(date) {
  // calculate day number (between 1 and 7)
  var day_num = new Date(date).getDay();

  // return the corresponding day name
  switch(day_num) {
    case 0: return 'Sunday';    break;
    case 1: return 'Monday';    break;
    case 2: return 'Tuesday';   break;
    case 3: return 'Wednesday'; break;
    case 4: return 'Thursday';  break;
    case 5: return 'Friday';    break;
    case 6: return 'Saturday';  break;
  }
  return 'DEFECT - not a valid day number';
};

edited May 21, 2020 at 19:41

Jean-François Fabre♦

141k24 gold badges179 silver badges246 bronze badges

answered Jul 26, 2012 at 10:05

mascip

1,3771 gold badge14 silver badges16 bronze badges

2 Comments

Serge insas Over a year ago

the statement outside the switch loop (return 'DEFECT - not a valid day number';) will never be executed since getDay() can only return integers between 0 and 6, you can remove it ;-). If you want to do some error trapping you could check if the input argument is valid for new Date()

Zig Mandel Over a year ago

this is not an answer, should be in a comment in the actual answer.

Collectives™ on Stack Overflow

Using built-in spreadsheet functions in a script

4 Answers 4

3 Comments

3 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related