Data Source Performance Workaround FOUND!

Questions about Thunkable
,
1

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:

  1. For each column in the data source, manually use the LIST OF VALUES block to copy the entire column into a local list
  2. 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
  3. Insert the newly created object into an APP list as the last step in the loop

A few tip/hints/observations:

  1. 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.
  2. 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.
  3. 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
  4. 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.
  5. Every cell in every row and column must be populated for this technique to work correctly
    Here is a screenshot of one example:
    blocks
    blocks758×758 82.3 KB

I just thought I’d leave this here so others can learn from my experience

5 Likes
2

I have a problem
1-Why list of values take a long time to get values over 15 seconds
2-how can I make columns as variable or any solution because I have more than 20 columns and more than 5 sheets & want value in each column

3

@marketandsellbuy8jcb how many data points are you trying to fetch? Do you have a stable internet connection?

4

Thanks domhnallohanlon
I replaced the sheet with another sheet and try … getting data with 4 seconds…I don’t know what the problem and make over 300 rows … and my connection is very good

5

I think one issue is that google sheets isn’t meant to be used as an app database.

I use Firebase and it’s always less than 1 second to retrieve data. That’s their promise!

Firebase or firestore will be the best option. Or a mongoDB option too, would be great!

The cloud variable and saving objects to lists accomplishes a dope fast dB option

2 Likes