CRAZY USECASE: Overloading app in a level that created hours delay in writing to Sheet

Hi gliders,

Decided to leave the topic as “Uncategorized” as I don’t really know where to put this story.

TL"DR - 200 user sent 20 answers to trivia game in a period of 30 min caused huge dealy (hours) in writing data to the sheet and update the app back,

Full story:
I built a trivia app as a small entry project for what could have a been a huge customer for me on the glide platform, a municipality here in Israel.
The game itself was relatively simple, 20 questions (answers were submited using choice components), few images, a users sheet where all the user’s data is being handled (details and answers+result calclation)and question and answers sheets and a process where the user first sign in and then sign up using filtered (by email) details screen and buttons (increment actions) to “move” to next stage of playing the game.

key point: all fields were writing to the sheet as they were filled by the user both on the signup and during the game itelf (i.e.choose answer and it’s being written to the sheet in real time)

the game itself presented a feedback to the player on each answer if it was right or wrong while in the sheet it was calculating the score and when the player finish the 20 questions present the total score back.

we tested the app on few tens of people and everything was working perfectly.

on Wednsday we launch the app officially in the municipality’s pffocoal facebook page… OH boy… did not see what happened comming…

on the first 30 min we had about 150 users entering the app (+50 after) to play and because each of them was sending about 25 “arrows” of information (personal info+20 answers) from glide to the sheet and that created a kind of overload which resulted in the data delayed in writing into the sheet which lasted from 10 min over 3 hours until all the data was finally written in the sheet.

Because I was using choice components and my correct/wrong logic was built on visibility in glide as well as using increment a user specific column to move to next question the app itself work perfectly and gave the user the correct feedback (i.e. right/wrong respond) and moved perfectly between the questions. The problem was on the end when a user finished the game and got the final score which was wrong (most cases presented 0) as it wasn’t written to the sheet, not calculating the right/wrong answers and thus automatically showing 0.

We got over a 100 comments on the game post (in the official municipality’s facebook page…) of people complaining that the game isn’t working and that the result is wrong. this created an embarrassment to the city and for me which resulted with the city in deleting the post of the game alltogether and take the app off the air.

I contacted glide team in real time for help and got a respond that there was a lot of data comming at the same time and that it created a queue and that all the data will arrive eventually.

from corresponding with the team I learn that it would be better to work on a glide table which would eliminate the delay or a user specific to store the data and once the user finishes the game send all the data at once (like a form in a way).

On my personal point of view, I would expect the app to work correctly and didn’t think that this would be consider a “lot of data” (there were suppose to play total of 500 users) which can create such a huge delay (over 3 hours from the last person entering data until it was written in the sheet) but the point is that you need to think very good on how you collect the data and how you save it to your sheet especially if you plan to have ,any users on the same tine writing and editing data.

As many of you know me, you know I’m one of glide’s biggest fans as well as leading both IL and global facebook communities for glide and no-code and will continue using glide and I still think this is one of the best and most flexible platform around but this made me be more carfull in the future of what I’m using glide for and how I build each app to make sure this kind of thing doesn’t happen again.

Thanks for reading.

18 Likes

That’s why I push as hard as I can to do as much as I possibly can within Glide.

Technically we can be working with up to 3 separate database copies at the same time.

  • First the local database on the user’s phone, which handles all of the computed glide columns to calculate everything instantly and in real time. This syncs with the Glide Server Database
  • Second, the Glide Server Database, which syncs data between user devices and also syncs with google sheets. Obviously here, glide’s priority will be to keep the user devices synced up between the users and the glide server database. More so, than keeping google sheets and the glide server database synced.
  • Third, the google sheet which I think of as more of a final backup destination compared to a real-time database. Glide has full control of data transfer between devices and the glide servers, but they are at Google’s mercy when it comes to sending and receiving data from the google sheet.

I only use google sheet formulas if absolutely necessary and as a last resort. If I can do the same thing using Glide’s tools, then I absolutely will.

8 Likes

Yep…I noticed the same behavior when 60 teachers were taking attendance for their 20 kids by increment actions to cycle through attendance codes. So, at the top of every hour the app was handling 1200-2000 button presses that were updating the sheet live.

It was a disaster…took HOURS to write the data to the sheet and back. Glide managed to fix the issue within a few days. Such a stellar engineering team!

4 Likes

Thank you for sharing this @yinon_raviv , I know a lot of people will benefit from lessons learned with this experience including myself.

I’m currently working on a book in app with the potential of multiple users editing data at one time.

For your app, was all of your data in GSheets? I still find myself troubled over where to house data (GSheets or Glide Tables) depending on the case. For the app I mentioned, user data is in GSheets because I need to mirror the content for user privacy while still showing public user profiles. Most other items are captured in a Glide Table.

2 Likes

Sometimes Google Sheets will slow us down, limiting us to 1 action every few seconds. To avoid this:

  1. Use Glide Tables — we don’t have any limits on actions here
  2. Google has a quota per Google Account for how often you can update a Google Sheet each minute, so if you have many apps connected through the same Google account, these are all sharing the quota. So, if you have apps for 100 different clients under your My Apps, these are all sharing a single update quota tied to your Google account. If you can use your client’s Google account (e.g. have them create the initial app from the sheet), they will get their own quota

We are working on:

  1. Making it easier to replace a Google Sheet with a Glide Table without rebuilding your app
  2. Batching actions sent to your Google Sheet, so 100 actions could run at once, for example
  3. Always improving the performance of Glide Tables
21 Likes

All my data was in the sheet but I could built it in glide table as it really didn’t have any complicated functions, just a matter of being use to do it like this for a long time.

What I did now in order to make sure that the 2nd round won’t get in to he same problem is to build all the data on user specific incliding the calculation and only in the end I have one add row action that will write the data into the sheet so I can send the summary to the customer but the user’s experience will be only based in glide’s user specific and computed columns so hopefully all will work correctly and the users will not face any issue and thus even if there will be a delay in writing the data to the sheets it won’t be a problem

4 Likes

Thanks David.

I shared this as a learning for everyone as this could be avoid if you build things differently and rely less on the Google sheet for feedback and more on glide tables and computed columns.

5 Likes

:smiling_face_with_three_hearts:

4 Likes

Thanks for sharing your learnings @yinon_raviv. Not the most pleasant experience, I’m sure.

Opening up Glide tables to export or offering some type of periodic backup at a premium would probably encourage more people to use it.

Edit: maybe pull data from API at some point?

1 Like

This is a great lesson to share. Sorry that you had to experience it and thank you for sharing so that we can learn from it.

The hold up for me personally in going full steam ahead with glide tables is the inability to upload and download mass data. Once this is ready, glide tables all the way.

You can export all of your app’s data, including all Glide Tables, in your Data Sync settings now. I agree doing it for individual tables would be better!

6 Likes

Definitely missed that update. Thanks for mentioning.

Can not wait :raised_hands:

I’ve recently started adopting this as a personal mantra. I think it’s incredibly good advice.
I still use a lot of GAS, but I’m a lot more selective about when and where I use it for Glide apps than I used to be.

3 Likes

If we were also able to replace a glide table with a Google sheet, that would really make me more likely to try using a Glide table. Really, we need capability both ways. Sometimes, the Google sheet has an advantage, other times, the Glide table is much better.

There’s been times I’ve started with a Glide sheet, only to realize that I need it in a Google sheet, then have to rebuild the whole thing!

This means I am very cautious and hesitant to start in a Glide table, just in case I end up needing an array formula, or need to pull multiple rows of data @ once in a webhook or something like that.

Now I am aware about this concept, too. That if I need some tables to write up faster, at the table design step i would decide whether to move up to Glide table instead. Cases that I often move up to Glide tables nowadays are:

  1. If the table don’t need to be monitored by admin/backoffice. Move up.
  2. If it’s a kind of logs/pool table where things are keep adding in so often.
  3. if it seems like few user-specific columns are needed within a same table. To use the most of Glide’s power I move it up.
  4. if the table has too many joints (lots of relationship) I move up.
1 Like

This is not the only issue (data extract), but also the ability to use webhook based on condition met.

I’ll explain,
In my sheet I have a script that is time based and is running every morning on my data set and if a certain column’s value is true the script triggers a webhook to send SMS to the customer.
I was not able to set this kind of mechanism in glide so have no choice but to keep using the sheet as I use this mechanism for reminders and until glide can have a solution to support this I’ll need to keep using the sheet and find a way to send data to the sheet in one update and not every data entered as I have set in most my apps.

4 Likes

A 3rd party service such as Integromat will handle this sort of situation, potentially even on it’s free tier.

Yup. I was able implement this just this week to send email alerts for SLA breaches. Time based trigger runs every minute, and if it detects an SLA breach, it fires a web hook to Intregomat, which in turn sends an email. Remarkably easy to set up :+1:

1 Like

although I only have two pro apps going, I am not starting to re-build parts that can be done in Glide Table…