I am trying to automate update of a “dues paid” calendar for my rugby club, which uses a monthly subscription model based in Square. The reporting calendar is a Sheets worksheet with columns labeled “Jan”, “Feb”, etc, so twelve columns of interest to this use case. When a dues transaction is processed in January I want to update the “Jan” column for the payer’s row with the dues paid amount. When that person’s February payment is processed I want to update the “Feb” column with the paid amount, and so on.
The best solution I’ve come up with is a series of Paths that filter the MMM of the transaction for each successive month, with a mapping to the appropriate column in each path. My problem is that I am limited to 10 paths in my design, making it impossible to capture the entire year.
Can anyone suggest a workaround or a design more elegant than forever manually managing a rolling ten month window of paths? I’ve done some reading on variables (e.g. &month) but don’t see a way to use that notion given the way the “Update Spreadsheet Row” step is implemented.
Thanks in advance for any counsel.
