Optimizing app performance based on Google sheets "DB"

We prototype a simple tool based on gsheets resource in retool. The tool broadly does the following:

  • connect to a pre-processed google sheets;
  • allow user to update specific cells via the table editing or a custom component (map). For each edit, the tool does a bulk update and read data query to update and refresh records. Each edit save takes 5-7 seconds which is ok for now.
  • download CSV for later processing internally.

In our initial testing of ~2k rows and one user, the app works OK. But now we plan to expand to more users (2-3) editing concurrently and more records of ~20k. I'm concerned with the performance of the app.

I read through the App performance best practices but there's no clear reference example on how to do it with google-sheets.

Are there any specific tips/strategies to ensure good performance?
One option is to move to a dedicated db so we can do SQL filters natively when reading data but we have to make do with Google sheets for now.

Hey @Maning_Sambale!

It sounds like you'd most benefit from pagination, which isn't really possible with Google Sheets. Would something like Retool Database, which is currently in beta for Retool Cloud orgs, work for you? You can store up to 50,000 records or 1GB of storage (whichever comes first) for free.

You can export your Google Sheet into CSV and then import it to Retool Database. To do that, log in to your Retool account > click the Resources tab > click Retool Database. See the screenshot below:

A popup will appear like this once you click Create new:

where you can upload a CSV file. See Retool Database document here for further details. Hope this helps! :+1:

I saw the docs mention table pagination: Present data in tables

Will this work for Gsheets resource?

Hey @Maning_Sambale, you should be able to utilize the Limit and Offset fields in your query :slight_smile: https://docs.retool.com/docs/server-side-pagination#limit-offset-based

Let me know if you have any questions at all!