AppleScript Numbers: Process data from Quicken

The code...(part 2 of 2)

(* The next two functions could simply return the value rather than
   creating a variable and returning that, but when trying to debug
   code it helps to have the steps separated *)
on getRegExString(targetCell)
  tell application "Numbers"
    tell table 1 of active sheet of front document
      set regex to value of targetCell's row's cell (regexColumn)
      return regex
    end tell
  end tell
end getRegExString

on getMonthString(targetCell)
  tell application "Numbers"
    tell table 1 of active sheet of front document
      set monthNum to (value of targetCell's column's cell (monthRow) as integer)
      return monthNum
    end tell
  end tell
end getMonthString

(* The year value appears in one cell over one of the twleve month
   columns, so if there isn't any value in the row/column pair
   associated with the current target cell, we need to go back (to the
   left) one column, and continue to do so until we find a value *)
on getYearString(targetCell)
  tell application "Numbers"
    tell table 1 of active sheet of front document
      repeat with x from 0 to 11
        set yearCol to address of targetCell's column
        set yearCell to cell (yearCol - x) of row yearRow
        set yearNum to value of yearCell
        if yearNum is not missing value then
          return yearNum as integer
        end if
      end repeat
      display notification "ERROR: unable to find year in row " & yearRow
    end tell
  end tell
end getYearString

The code...(part 1 of N [currently N=2])

-- Set properties to reference specific rows or columns to use in formulas
property yearRow : 0
property monthRow : 2
property regexColumn : 4

-- Overall Service main function
on run {input, parameters}
  tell application "Numbers"
    (* Since running as service, 'front document' and 'active sheet'
       are the current sheet in the current document *)
    tell table 1 of active sheet of front document
      set selRng to selection range
      (* 'selRng' will be the curret, or currently selected range of,
          cell(s). It's stored in a variable for reference because
          otherwise as soon as you edit the contents of a cell, the
          next call in the loop would be referencing an index into a
          range that is no longer selected *)
      repeat with currentCell in cells of the selRng
        my processCell(currentCell)
      end repeat
    end tell
  end tell
  return input
end run

(* NOTE: When creating functions that are going to interact with the
         data in sheet, you need to re-specify the context (tell ...)
         within the function *)
on processCell(targetCell)
  tell application "Numbers"
    tell table 1 of active sheet of front document

      set yearString to my getYearString(targetCell) -- '2016', '2017', etc.
      set month1 to my getMonthString(targetCell) -- '1', '2', etc.
      set month2 to month1 + 1
      set pattern to "=" & my getRegExString(targetCell) -- e.g.: "=EXP:MISC*"
      set dateString1 to ">=" & month1 & "/01/" & yearString -- ">=01/01/2016"

      (* The second date string is one-month greater than the first,
         but if the value is greater than 12 (December), we have to
         change it to 01 and increment the year by one *)
      if month2 > 12 then
        set dateString2 to "<01/01/" & yearString + 1
      else
        set dateString2 to "<" & month2 & "/01/" & yearString
      end if

      set regexString to "=SUMIFS($Amount,$Date,\"" & dateString1 &¬
                         "\",$Date,\"" & dateString2 & ¬
                         "\",$Key,\"" & pattern & "\")"


      (* Put formula into the cell, which will calculate the desired result *)
      set value of targetCell to regexString

      (* Copy the cell's value onto itself, thus replacing the formula
         with the static value; reducing processing overhead *)
      set value of targetCell to value of targetCell

      (* Set format of cell to 'currency', unfortunately there does
         not seem to be a way to enable the Thousands Separator and/or
         Accounting Style options that are avaiblable within the
         GUI. There are ways to simulate this, but I haven't dealt
         with that [yet] *)
      set the format of targetCell to currency
    end tell
  end tell
end processCell

Hmm - it's conceivable that might have worked, I think I ran into this problem before I realized '&' was the means of concatenating strings.

I still would have needed column D to include the regex strings, and row 2 would have included date strings like "01/01/2016", "02/01/2016", etc. (American date format MM/DD/YYYY) to become:

=SUMIFS($Amount,$Date,">="&E$2,$Date,"<"&F$2,$Key,"="&$D6)

Which, appears to work - however, at the time I was trying to get this to work, I wasn't thinking about AppleScript, and this would have resulted in over 2000 formulas within a single table and performance would have been horrid every time Numbers re-calculate the contents of all those cells - so it was probably a good thing I didn't realize I could do that ;-)

Hope at least one person (other than myself) finds the above useful....