IssueTracker

Hello and thanks for your feature request.

If I understand correctly you are asking for something like a custom error category that will throw an error in the UI but not in the logs. Is that right?

I am not sure this is feasible because spreadsheet functions are intentionally limited. For instance, they don't have access to the UI of sheets like scripts do.

Workaround ideas

• If returning a text string is not viable because it is not clear that its an error, could you not implement a conditional formatting rule to turn the cell red?
• Implement the calculation as a script which will summarize the errors in a sidebar UI. You can trigger this with an onEdit trigger or a time based trigger.
• If you need extensive UI customization, web apps could be an option for you.

Information required to continue

Bearing this in mind, to fully understand and complete your feature request, can you address a few points:

• Please provide some context for this request. Why do you need this functionality? What business impact does it have for you?
• Please provide an example of the spreadsheet you are using which demonstrates the workflow you are trying to implement.
• You can filter the logs, please justify why this is not suitable. In what way are you working with the logs?
• You can implement this functionality as a script that has access to the UI, again please justify why this option is not suitable.
• Why is returning a text string is not a viable workaround?

Thank you

If I understand correctly you are asking for something like a custom error category that will throw an error in the UI but not in the logs. Is that right?

OK, let's take a step back. This feature request is about the custom function interface between Apps Script and Google Sheets.

There is no need for "a custom error category." The issue is that custom functions currently have no way to display an error in the spreadsheet without throwing an error that will add noise to the logs.

Custom functions can currently return instances of these objects: Number, String, Date. I think it would be useful to be able to return Error objects as well, in such a fashion that the error would be shown in the user interface. Currently, an Error objects returned by a custom function is shown as a blank value. It should show as an error value, with the message property of that Error object visible when the user hovers over the cell that shows the error.

The value shown in the cell needs to be an error value rather than a text string message, because an actual error value is the only way to ensure that the error gets propagated to cells whose formulas reference the custom function cell.

I am not sure this is feasible because spreadsheet functions are intentionally limited. For instance, they don't have access to the UI of sheets like scripts do.

That is understood. There is no need for the custom function to access the user interface. The feature request is about being able a custom function to return an Error object, and have the message property of that Error object shown in the user interface. This would let custom function add-on developers avoid the need to throw errors just to make them visible in the user interface.

If returning a text string is not viable because it is not clear that its an error, could you not implement a conditional formatting rule to turn the cell red?

I could do that if I was writing these custom functions just for myself. But that is not what I am doing — I am writing add-ons for the Google Workspace Marketplace. The users of those add-ons will need to be notified when their formula is using incorrect parameters. Currently, the only way I can notify them is by throwing an error. But I do not want to deliberately throw an error — that will add superfluous noise to the logs. I would like to be able show an error in the cell that uses the custom function, without throwing an error.

Please provide some context for this request. Why do you need this functionality? What business impact does it have for you?

I am writing add-ons that extend the functionality of Google Sheets through custom functions. I want to be able to browse the logs to find genuine error events. That is difficult because of the noise created by deliberate throws that are need to report incorrect parameters to the end user.

Please provide an example of the spreadsheet you are using which demonstrates the workflow you are trying to implement.

Please see the sample code posted above.

You can filter the logs, please justify why this is not suitable. In what way are you working with the logs?

The filtering features in GCP Error Reporting cannot distinguish between actual crashes and deliberate throws. It would be best to be able to avoid the need to deliberately throw errors just to get them shown to the end user.

You can implement this functionality as a script that has access to the UI, again please justify why this option is not suitable.

I could do that if I was writing these custom functions just for myself. But that is not what I am doing — I am writing add-ons for the Google Workspace Marketplace.

Why is returning a text string is not a viable workaround?

Because a text string is not an error value, and will not prompt dependent formula cells to propagate the error further as required.

Thank you for your detailed response.

In built function error

The in-built spreadsheet functions return user errors as is shown in the image attached titles "in built function error".

Throwing an error

If you throw an Error object from a custom function with a function like:

function customFunction2() {
  throw Error
}

It results in the image "throwing error which logs error".

Returning an error

If you return the object:

function customFunction3() {
  return Error
}

It results in the image "returning error object" in which the Error object has been converted into a string.

So your request is for the ability to return, not throw, a user error like the in-built functions. Perhaps via a custom error object or to have custom functions be able to handle error objects.

Can you confirm that I have this correct?

Images attached

So your request is for the ability to return, not throw, a user error like the in-built functions. Perhaps via a custom error object or to have custom functions be able to handle error objects.

That is somewhat correct. I do not think it makes a difference whether the error object is a plain vanilla instance, i.e., new Error(), or a custom Error type.

Please note that the code examples you provide above are not really correct because they are not throwing instances of Error objects. They are throwing the Error prototype, i.e., a function that gets coersed to a text string. They should use new Error() or instead of Error. See the snippets I gave originally, or refer to MDN.

Cheers --Hyde

Thank you for that confirmation and correction - oversight on my part.

I have filed this internally for the corresponding team to take a look at. Any updates will be posted here.