search

zooniverse / zootools-sheets

Apache License 2.0
2 stars 0 forks source link

filter tool not working on large spreadsheets #75

Closed brittlundgren closed 3 years ago

brittlundgren commented 3 years ago

Hi,

I'm trying to develop a college lab in which students use ZooTools to analyze the latest compilation of exoplanet measurements.

This dataset contains ~4500 rows and 58 columns, which I have slightly modified only to replace blank cells with '-999'. When I use ZooTools to filter the table (e.g., requiring 'disc_year' = 2015), this usually returns the error: "Something went wrong: ScriptError: Service invoked too many times in a short time: exec qps. Try Utilities.sleep(1000) between calls."

Sometimes this same filtering command does work, but the resulting filtered table is unusable. It contains just one cell, with the following formula: "=QUERY(PSCompPars_2021.07.20_09.56.05!A:K, "select A,A,A,A,A,B,B,C,C,H,H,J,K where F = 2015", 1)"

If I reduce the dataset to a much smaller number of columns, the filter tool works fine. So the size of the dataset appears to be the problem. I'm wondering if it would be possible to add functionality to the tool so that it can accommodate larger files?

Thanks for your help! Britt Lundgren

Associate Professor Department of Physics and Astronomy University of North Carolina at Asheville

srallen commented 3 years ago

@brittlundgren I've done some research on the error and it's basically an error from hitting a Google API quota limit. It's possible for us to do some optimization to try to avoid this, but ultimately, it's still possible particularly with spreadsheets with a large amount of data since we do not pay for the Google APIs that the add-on is using. The add-on basically calls the Google Apps Script API to run functions against the Google Sheets API to perform setup for plots or build spreadsheet A1 notation for you.

I would recommend if possible to use the A1 formula notation directly if you're running into API quota limit errors as these wouldn't run using the Spreadsheet or Scripts APIs. The filter tool concatenates a QUERY function based on the parameters you select. Here is more info on that so you can try to write your own query directly:

https://support.google.com/docs/answer/3093343?hl=en https://www.benlcollins.com/spreadsheets/google-sheets-query-sql/

brittlundgren commented 3 years ago

@srallen OK, I see. Thank you for your help!