Every 7am Mexico City time I run through all the rows in the Profiles sheet, then pickout only the ones that has the same âReminder weekdayâ as the âNowâ weekday of Integromat, then send them emails.
With your method here, Iâm thinking about an extra offset column to let me calculate the âcurrentâ time of each user, in the Sheet. I would run the scenario every hour to check for rows that have the âcalculatedâ time as 7am, for example.
So it comes from sending everyone emails at 7am Mexico City, to being able to send emails at 7am their time.
Yeah, I think that would work. Trying to run it through my head, but Iâm sure thereâs a lot of parts to it.
So basically you are figuring out the userâs current day. I fall into the same time as Mexico City. So for example, I assume you are 12 hours ahead. I get confused with the international date line, but as of right now you are a day ahead from me, but at 7:00am my time, we would still be within the same day. With that I wonder if you would have a problem at all. But ignoring that, lets say your process runs in the next half hour and you picked Thursday as your day. As it stands, it would not pick up your day because Mexico is still on Wednesday. So if you had your offset of +7 hours combined with the -5 for Mexico, you would have a +12 hour offset, so you would need to add 12 hours to Mexico local time and then check the weekday of that calculated date. In theory, I think it should work out pretty well.
Some timezones have daylight savings so the time shifts by an hour, so Iâm not sure if that would cause some rare situations where the calculated date would jump by an extra hour, but hopefully that wouldnât be much of an issue.
@Jeff_Hager, here is my solution for this issue although I think, both solutions complement each other.
In my case, I have a Google Sheet with a timezone XX and I try to have on APP the time based on that GS timezone and not the user timezone which is used by Glide.
If my GS is using âEurope/Berlinâ timezone (GMT +01) and my APP has multizone users around the world (USA, Brazil, Spain or Venezuela) buying tickets or making a booking Iâm going to need to have transactions timestamp based on my GS (Berlin) always to have a real order and know the sales queue correctly (based on time).
Otherwise, If I use the Glide time (based on device/user timezone) my transaction log will be hard to understand and sort out.
The key in my case is write and use the GS timezone into a cell using the Now() function. This will rule everything regarding time and date. Itâs the only thing you will need to use in your GS.
⌠.
Later, in GDE you will have these 3 new columns with a simple logic/math:
In my case, Iâm in Venezuela and have -6 hr with Berlin and @Mark_Turrellâs house .
With it, any transaction done by me (or any user) from APP will be logged using Berlinâs time instead of local time (user timezone).
Although few people and in few times the GSâ timezone will be changed frequently, I created a choice list to carry out it automatically and test better my solution (using a small script as well). None of you needs to have it on your APP to get this functionality, this is not mandatory.
Again, I created the timezone choice list and my script to make this APP demo more friendly only.
Yes, it looks like the @Simon_Hillâs idea but there is one difference:
Simon needs to send any value previously to his GS to update the time and later use it in any operation. If your APP has a Pro plan, it isnât mandatory because of Glide will update APP from GS each 4-5 min (including everything associated to Now() function).
But even using a Pro plan, Simon still needs to send a value to GS (via an Increment action) to avoid losing up to 4-5 min caused by Glideâs automatic update.
My solution doesnât have this problem with a Pro Plan. Once APP has read the time from cell, the new Glide time is updated every 10 sec by Glide internally and automatically.
Instead, if your APP uses a free plan, you will need to do something like what Simon does but there is a time window about 1 hr in where you still have the synchronized time without problems.
Just to clarify, whatever your plan so long as you change something in the GS then the entire sheet will be updated instantly. I donât use âincrementâ anymore. Instead I use âset columnâ and push the current Date/time into any redundant cell. Itâs pretty easy to add these âtrip wiresâ into any app so that common user actions automatically trigger a GS time update.
Thatâs a good solution too. I was going to ask about how the sheet time was updated in glide, but now that makes sense with the background refresh, or setting a value to resync. So in theory, you should never have a time drift of more than 5 minutes, so your rounded offset calculation will always fall to the nearest hour.
Just something to be aware of, there are those rare timezones that differ by an additional 30 or 45 minutes. May not matter much depending on the scope of your app, or where the userâs are primarily located, but something to consider.
@Simon_Hill no problem, I understand what you meant. What we are looking for (in my case) is a reliable time using an automatic way without firing a manual action in the middle. Also, if we can do it using Glide tools 100% we would make a home run but sometimes we wish and sometimes we fail
@Jeff_Hager youâre absolutely right⌠te odio!
You remind me my mom, she always paid attention to details and those rare scenarios that make an exception. I had noticed that problem with timezones with -/+ 30 or 45 min thinking in my country (currently Venezuela quit using -4:30 offset and now we use 4:00).
Having said this, my workaround is useful for 93% of cases
Just to clarify, my solution is 100% Glide. 4 columns in a glide table to determine GMT, which could probably be condensed into 2 columns, to determine GMT or any other time zone (GMT+2 or GMT-2) if needed.
@Jeff_Hager the medium format of date/time breaks the logic because it is localized and might include some words that are not âunderstoodâ by your logic (in my case itâs âthĂĄngâ - which means âmonthâ).
I think he means 93% in that it covers all timezones except those rare ones that fall on the 1/2 hour or 3/4 hour, which could throw off the result by an hour for part of an hour. But only in those odd timezones.
@ThinhDinh Ok, I changed the Local Date Time to the Short Format for the calculation and added a new math column so I could reformat it to the Medium Format for display purposes.