I haven’t seen that either. That message is occurring when we call the v4 API to fetch the Transactions and Categories sheets. How large is your Transactions sheet, @al1?
I found a thread on Stackoverflow about this. The thread seems to identify the bottleneck as a "limitation of UrlFetchApp()". The thread also hints that overlong formulas and named range names could contribute to the large response size.
Is there an easy way for you to prune or archive some of the data that is not needed for the query?
There are code solutions that involve fetching large Transactions sheets with multiple batched queries, but I’m afraid I cannot prioritize that change right now.
@randy Transactions tab are almost 72k rows… this includes the year before the requested P&L report… the year requested in almost 45k rows. In case you are wondering why the large dataset in the transaction tab is because I use Tiller for my business reporting. All sales, expenses, basically every single transaction is in the transactions tab.
What is the query for the P&L report? I entered the filters in the wizard to only include 2021 data. Are you saying to remove 2020 data for pruning?
Ill take a look at the thread on Stackoverflow but I dont have access to even know what the P&L report wizard is doing when it is submitted… would be nice if this stuff was open source to be able to dig into the workflow…
I feel your pain, @al1, and agree that this should work better. As you probably know, the workflows (including reports) in the Tiller Community Solutions Add-on are best-effort, unsupported beta workflows. They seem to work well for most users but your case appears exceptional, presumably due to the large dataset.
The reports are coded using Google Apps Script (they don’t run a query). I agree that open sourcing this code (especially since it is not “supported” by our product team) could make sense but we aren’t in position to do that right now.
Two things you might try:
Have you tried running this type of report with a pivot table?
I think there might be in-cell formula based tooling you could use to build this yourself relatively quickly. I’d consider cloning a copy of the Yearly Budget. In the hidden area, there is a QUERY() that pulls actuals across multiple months— you can probably use this query more or less as is. From there, you might be able to hack on the formulas that pull that data into the body of the Yearly Budget to format it similar to the P&L report. (The Yearly Budget is very similar visually and data-wise to the P&L report.)
Sorry I don’t have a better solution for you.
Randy
I’m sorry, @al1, but I can’t. The P&L generator is just a few hundred lines of code but it depends on several shared libraries that are integral to our Feeds/core product.
I think the Yearly Budget approach could be a winner.
I think that could be done in 30 minutes.
Just getting back to some community threads, @al1. I mocked up the idea of adapting the Yearly Budget template into a Live P&L Report— which is now published in the Tiller Community Solutions Add-on
Glad you like it, @al1. I’m hoping that folks use it as a starting point for some cool additions— like cashflow (as you suggest) or multi-year comparisons… etc.
