Timer functionality

Has anybody implemented a timer functionality that shows a message for 60 seconds or till a cell in Google sheet turns True? After 60 seconds, another message will be shown. Is there a way to implement a visibility condition based on time?
This is what I am looking for. A student submits a tutoring request. After 60 seconds, if no tutor accepts the request, a message saying "sorry "is shown.




This image will be shown for 60 seconds and if no tutor accepts the request, then another message is shown as below.

You might be able to do something like this. First, you’ll have to update your spreadsheet settings so that it performs calculations every minute:

Screen Shot 2020-02-19 at 9.02.01 PM

Then, based on the time stamp of submission, have a column that calculates the timestamp + 60 seconds (but I would do longer to account for the lagginess of the sheet calculations)

Then, have a column that checks if the calculation from the previous step is greater than or equal to Now() and return true (reached time limit) or false. Your visibility can then determine that the sorry message appears if tutor = false AND calculation is true.

This I think could work.

2 Likes

Thanks for the suggestion. I had tried this, by inserting a formula into the first cell of a column which calculates timestamp + 60 seconds using a google sheet function. Now, when I drag it to apply the formula to all the cells of the column, the next form data from the glideapp does not appear in the second row, but appears way below after 1000 rows. Is there a way to prevent this from happening?

Yes. First get rid of all those copies. You don’t want to drag/copy down because new form data will skip them. Instead, the first row needs to contain an array formula that will automatically apply the formula to all new rows (Array formulas are crucial and so easy to set up): https://www.spreadsheetclass.com/using-arrayformula-to-apply-a-formula-to-an-entire-column-in-google-sheets/

I tried ARRAYFORMULA, but it applies it to a blank cell and give a value, and the same problem appears of the next form data coming at the end of the rows applied.

You need to also put an IF statement in your arrayformula to only populate the row with a date if column E for example is not empty. Then you need to delete all empty rows because rows with arrayformulas are still filled rows and glide will only insert new rows at the end of the sheet.

=ARRAYFORMULA(IF(LEN(E2:E)=0,"", insert your formula here))

I have done exactly as mentioned. And the logic works excellently, thanks Jeff and Robert. And, I am able to see a Timeout variable change from FALSE to TRUE based on a time comparison formula in the google sheet. But, this change doesn’t affect the glide app. Is it because it is not an edit, and I have to go PRO in order for changes in google sheet based on google sheet functions to affect the glide app?

1 Like

Did you change your sheet settings to refresh every minute (see my first reply)?

1 Like

That’s a really small time limit for a glide app. If you do go Pro, I think Glide will look for updates from the sheet every 3 to 5 minutes. When using the free version of Glide, sheet updates will only make it to the app shortly after you make an edit in the app and any time Google sends an update to Glide. From what I’ve seen, certain functions like now() will not trigger Google to send the update to Glide and require some sort of change in the app to get the sheet updates.

I’d consider some sort of script on a timed trigger to make a change in your sheet every minute, or go pro and consider changing to request time for tutor’s, from 2 minutes to maybe 5 or 10 minutes.

It will change every minute if the sheet is closed.