In google sheets, how to call custom javascript comparators within sumifs

Question

What I want to do:

In my personal accounting sheet, I'd like to have tags so I can categorize expenses into more than one bucket. For instance "food" and "kids". Some expenses are both. So for each row, besides the date, amount and other stuff, I fill a field with comma-separated tags. Then on another sheet I want to sum those up for given periods (say, a month, a quarter, or a year). So on each row i have a couple dates to define the time period, and a bunch of columns, each for one tag. I'd also like to be able to filter with more than one tag, e.g. "kids,food" so i know how much i've spent of dinner out with the kids.

With sumifs, i can select the rows within the time period, but I don't know how to do the tags.

I've already written a function is_subset(superset, subset).

What i've tried

I've tried writing my own sumifs, but that proved difficult.

I've tried using the standard format range, condition with condition being ">"&A$2 but then date formats don't match. From the range, i get a string in the format Sat Apr 01 2023 00:00:00 GMT-0300 (GMT-03:00) and from the concatenation i get ">40000" or something close, whatever that represents. I've tried to determine the format of each condition so i can parse the date, but when giving a range to a custom function, all we get are the values, not the range, so we can't access the format information from a proper Range object.

So i've tried passing the range strings around, e.g. =sumifs(mysheet!A1:A, "mysheet!B1:B", "A2", "ARGLEFT>ARGRIGHT"). So i could get a Range, check the format, then replace ARGLEFT and ARGRIGHT and call eval, but then i can't copy and paste the formula around anymore since the ranges won't get adjusted. And testing for all formats would be a pain anyways.

What i think might work, if only...

The closest i've got is using eval with custom functions always: =sumifs(mysheet!A1:A, mysheet!B1:B, A2, "gt")

I wrote function lt(left,right){return left<right;}, gt, gte, lte, eq, and now i can use my initial is_subset, too. But this only works if i give it CELLS, not RANGES. Cells compare properly: =test(A1, A2, "gt") is fine. Ranges do not: =test_loop(A1:A5, A2, "gt") returns wrong values.

Here's a link to a prototype sheet.

This link will eventually be broken, so here are screenshots:

Sheet Balance containing all money movements:

Sheet Expenses containing the formula i'm looking for:

Here's the formula i have so far for Expenses!D2:

=mysumifs(Balance!$E$3:$E, Balance!$A$3:$A, $A2, "gte", Balance!$A$3:$A, $B2, "lt", Balance!$B$3:$B, "Expense", "eq", Balance!$D$3:$D, D$1, "is_subset")

Here's the full code:

function is_subset(superset, subset)
{
  subset = subset.toLowerCase().split(",")
  superset = superset.toLowerCase().split(",")
  for (var i = 0 ; i < subset.length ; i++)
  {
    var found = false;
    for (var j = 0 ; j < superset.length ; j++)
    {
      if (subset[i] == superset[j])
      {
        found = true;
        break;
      }
    }
    if ( ! found)
      return false;
  }
  return true;
}
function gt (left,right) { return left> right; }
function gte(left,right) { return left>=right; }
function lt (left,right) { return left< right; }
function lte(left,right) { return left<=right; }
function eq (left,right) { return left==right; }
function mysumifs()
{
  if ((arguments.length % 3) != 1)
    throw "Bad arg count";
  var range_to_sum = arguments[0];
  var sum = 0;
  for (row_idx = 0 ; row_idx < range_to_sum.length ; row_idx++)
  {
    Logger.log("row_idx: " + row_idx);
    var add = true;
    for (arg_idx=1 ; add && arg_idx<arguments.length ; arg_idx+=3)
    {
      Logger.log("arg_idx: " + arg_idx);
      try
      {
        var left_range = arguments[arg_idx];
        if (range_to_sum.length != left_range.length)
          throw "Ranges have differente sizes";
        left = left_range[row_idx];
        Logger.log("left: " + left);
        var right = arguments[arg_idx+1];
        Logger.log("right: " + right);
        var op = eval(arguments[arg_idx+2]);
        Logger.log("op: " + op);
        Logger.log("op(left,right): " + op(left,right));
        
        if ( ! op(left, right))
          add = false;
      } catch(error){
          add = false;
          Logger.log("exception: " + error)
      }
    }
    if (add)
      sum += range_to_sum[row_idx][0];
  }
  return sum;
}

Am I missing something obvious about sumifs that would allow to use arbitrary, custom comparators?

Did I make a mistake about reading values from given ranges?

Answer 1

Answering my own question...

I was able to fix the date format compatibility problem using value() to force the date format into something easily comparable and arrayformula() to make it so for a range. I did not need to alter my javascript functions. Here's the correct call for Expenses!D2:

=mysumifs(Balance!$E$3:$E, arrayformula(value(Balance!$A$3:$A)), value($A2), "gte", arrayformula(value(Balance!$A$3:$A)), value($B2), "lt", Balance!$B$3:$B, "Expense", "eq", Balance!$D$3:$D, D$1, "is_subset")

With:

• Balance!$E$3:$E being the values to be summed
• Balance!$A$3:$A the date of each "event"
• $A2 the period's start date
• gte the custom javascript function (check the code in the question) to compare the start date and the event's date
• Balance!$A$3:$A, $B2 and lt to check that the event's date is before the period's end date
• Balance!$B$3:$B, Expense, eq says the event's type must be equal to Expense
• Balance!$D$3:$D, D$1, is_subset, says the column's header must be a tag subset of the To column of the event.

The data:

The Formula: