0

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.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Juan J Arroyo
  • 1
  • you cant avoid that line number when throwing an exception. – Zig Mandel Aug 13 '15 at 05:23
  • 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 Aug 15 '15 at 02:34
  • Possible duplicate of [Throwing custom exceptions and error messages in Google Sheets custom function?](https://stackoverflow.com/questions/26152630/throwing-custom-exceptions-and-error-messages-in-google-sheets-custom-function) – Rubén Jan 22 '18 at 15:26

1 Answers1

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.

Wim den Herder
  • 1,197
  • 9
  • 13
  • 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? – Juan J Arroyo Aug 14 '15 at 02:22
  • You can write an if statement if(date instance of Date) and then do what ever you like :) – Wim den Herder Aug 14 '15 at 07:51
  • 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. – Juan J Arroyo Aug 25 '15 at 04:01