How to handle error on a custom function in spreadsheets

Question

I am writing a custom function in google spreadsheets. One of my inputs have to be a date since I am using date.getDate()

If the input variable is for example a string I get an #ERROR! on the cell and a generic error on the cell comments.

Using a try and catch structure:

try {
  var DAYNUMBER = date.getDate();
} catch (e) {
  throw ("Function parameter 1 expects a date value the value entered is not in a date format.");
}

I was able to change the error message on the comments but at the end it displays:

(line 44).

Also I would like to change the Cell value to: #VALUE! instead of the #ERROR! message.

I have been looking for documentation on how to do this, but I have been unable to find anything.

If you could point to documentation or help with how to avoid the (line 44) message and change the cell value to #VALUE! it would be appreciated.

Thanks in advance.

My question is more related on how to handle the error on the spreadsheet. I know I can avoid the error, but if I want to get the error and just change the value and give help to the user, I am not sure how to do that and I have not found any documentation. — Juan J Arroyo
– Juan J Arroyo, Commented Aug 15, 2015 at 2:34
Possible duplicate of Throwing custom exceptions and error messages in Google Sheets custom function? — Wicket
– Wicket, Commented Jan 22, 2018 at 15:26

Wim den Herder · Accepted Answer · 2015-08-13 10:11:44Z

0

You can check if the variable is a date object with the instanceof command:

var DAYNUMBER = (date instanceof Date) ? date.getDate() : "#VALUE!";

The a ? b : c structure is shortcut for if a then b, else c.

answered Aug 13, 2015 at 10:11

Wim den Herder

1,35512 silver badges17 bronze badges

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

3 Comments

Juan J Arroyo Over a year ago

Thanks a lot it is really useful. It sets the value on the variable, but it does not handle the error message since it is not produced. Do you have any information on how to handle the error message (how to change the value that is displayed on the Cell itself and how to control better the message that you get on the cell as a comment?

Wim den Herder Over a year ago

You can write an if statement if(date instance of Date) and then do what ever you like :)

Juan J Arroyo Over a year ago

Thanks, I am just not sure how to do what I need. I can get the if statement, but in case I get the error, how do I change the error message that the user will se on the spreadsheet.

Collectives™ on Stack Overflow

How to handle error on a custom function in spreadsheets

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related