I have created a custom formula in Google Sheets that can take in an array and output another array. Some of the values in the input array may result in an error; I would like to show that error for those values, but show the result of the correct computation for the other values.
This doesn't work because the entire column just evaluates to one error:
function test(input) {
if (Array.isArray(input)) {
return input.map(
(row) => row.map(
(val) => {if (val != 0) {throw new Error("not 0");} else {return 0;}}
));
}
}
This also doesn't work because there are no errors reported in any cell:
function test(input) {
if (Array.isArray(input)) {
return input.map(
(row) => row.map(
(val) => {if (val != 0) {return new Error("not 0");} else {return 0;}}
));
}
}
How can I get the result I want?
Google Sheets custom functions cannot return error values with a message like "not 0". They can throw errors, and when they do, error.message
does get displayed, but throwing halts the whole function, so that won't let you iterate over an array.
There is an issue filed at the issue tracker regarding this: Custom functions need a way to show an error to user without throwing. To vote for fixing the issue, click +1. To get notified of status changes, click the star icon ☆. Please do not comment, i.e., do not post a "me too" or "+1" reply, but just click those icons, because comments spam everyone's inboxes. Google prioritizes issues with the most +1 clicks and ignores comments.
One workaround to output an error value is to return NaN
, like this:
/**
* @customfunction
*/
function test(input) {
if (Array.isArray(input)) return input.map(test);
if (input !== 0) {
return NaN;
} else {
return 0;
}
}
This won't let you output a custom error like "not 0" but will always display the #NUM!
error with this hover-over text:
Error
Result was not a number.