While building a simple mobile app to keep track of certain processes around our office, I noticed how many times I would have to trigger queries after I would update spreadsheets. One update to a row on a spreadsheet may need to trigger quite a few queries to update the list, which would show on the client side and tend to make things a bit slower. It made me think of the repository pattern in .NET programming (seen here) which creates an abstraction layer between the data access layer and the business logic layer. How this would look in Retool (I would imagine) is making a copy of the spreadsheet client-side while the app is open, which would be used as the working copy that is shown to the client, and then when everything is done and updated it would write it back to the server, which for me is just Google Spreadsheets. Updating something like a local JSON string would make data retrieval and viewing a lot cleaner. Is there a simple way to implement this, or something like this, in Retool?
That's a tough one and a bit above my pay grade. Hopefully someone who has done something like that will be able to jump in and advise.
Retool doesn't really have a data access layer and business logic and UI layers, unlike an MVC framework. For example the GUI has resource queries written directly in SQL.
At a guess I would think that if you could download the spreadsheet into a local state object and then have the visual components write to that object, you might be able to then push it back to the original Google Sheets.
This would suffer from potential data conflicts and may require some locking. I suspect it would also be pretty ugly to code. We migrated all our google sheets to Retool's postgreSQL instance and while that prevents spreadsheet analysis, it's much more solid.
I've done something similar with a variable - you would setValue() on the variable with the spreadsheet data, then make your actions use setIn() to make updates to the variable, then you can send the value of the variable back to the spreadsheet (perhaps even intermittently to a temp copy just in case?).
Agree with @stewart.anstey on the challenges of data conflicts (ugliness of code is in the eye of the beholder ). In my particular instance, it was doable, but definitely your mileage may vary...
). In my particular instance, it was doable, but definitely your mileage may vary...