There are 2 main limitations:
- Size of your spreadsheet
- Max runtime for scripts and add-ons
Currently, a spreadsheet can hold max 5 million cells. If a query is about to increase your spreadsheet above 5 million cells, the downloader will throw an error before query results start to be inserted into the spreadsheet.
When it comes to the runtime limit, add-ons can currently run for 6 minutes max. The runtime limit is imposed by Google and we cannot do anything about it, unfortunately.
In the 6 minutes period, all of this needs to happen:
- A query job is started in BigQuery.
- A query job is running in BigQuery.
- A query job is finished in BigQuery.
- Results are downloaded to the spreadsheet.
- This is repeated for all your “enabled” queries one by one.
If your query is very simple, i.e. phases 1-3 do not take more than a few seconds, you should be able to download around 1.5 million cells (for example 70k rows with 25 columns) into your spreadsheet. If your query is complicated and it needs to run for a minute or two first, then the downloader will be able to paste less data into the spreadsheet because it will only have 4-5 minutes to complete the download process of the query results. So keep this in mind and always try to consume data from static tables instead of complicated views in BigQuery.