Using built-in spreadsheet functions in a script

问题:

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.

回答1:

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

回答2:

Thank you Serge. What i understand from your post is that i have to re-implement the spreadsheet functions myself, and cannot re-use them directly.

This was just a silly "simplest possible" example, to show the problem. What i want to do, right now, is a function that writes the week day in letters.

EDIT : problem solved !

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';
};