Anyone who has tried to use the data source GET VALUE block to retrieve an entire row from a google sheet data source will have noticed the abismal performance. I’ve developed a work-around with resonable performance, albiet block intensive. The basic process is:
- For each column in the data source, manually use the LIST OF VALUES block to copy the entire column into a local list
- Use the LENGTH of one of the created lists for a COUNT WITH block and build a JSON object using the CREATE OBJECT and SET PROPERTY blocks
- Insert the newly created object into an APP list as the last step in the loop
A few tip/hints/observations:
- THE LIST OF VALUES block takes almost the same amount of time as the GET VALUE BLOCK regardless of the number of rows so the performance is MUCH better. I now retrieve thousands of rows in just a few second.
- Combining more than 2 or three LIST OF VALUES blocks into a single CREATE OBJECT or SET PROERTY OF OBJECT block causes instability and crashes.
- Building the JSON OBJECT and/or LIST in a CLOUD variable incurs a major peformance cost. If the list is more than a few rows long, it is faster to use an intermediate APP level variable, then copy it to a cloud variable when completed
- Using the REALTIME_DB to create the CLOUD variable allows you to view the JSON in the firebase realtime db viewer, which is great for debugging.
- Every cell in every row and column must be populated for this technique to work correctly
Here is a screenshot of one example:
I just thought I’d leave this here so others can learn from my experience