Delay in timing

[NE0NBLACK]

I have noticed that if i start the bot to run on the hour in 'every' mode there is sometimes a delay when it actually sends the tweet. Running 3 bots simultaneously, one was on time, one instance delayed 16 minutes, another 27 minutes. What ever the cause is, it affects every trigger thereafter and is not compensated (e.g. first tweet at 1:16pm, next 2:16pm - instead of on the hour). I suppose those delays sum up so that they wont be processed at the intended time at all after a while, messing up your schedule.

[DocCyber]

I start mine on the hour and it runs at 28 minutes past the hour, the timing is different at different speeds (it still won't tweet faster than once an hour) but if you write down your start times (to the minute) you can use them to note your tweet times, and they appear to be constant to whatever degree they're off starting at the top of the hour ALWAYS gets my tweets out at 28 minutes past the hour

[NE0NBLACK]

Thanks for that. Sure that's an immediate workaround. But seeing that it appears to be constant, the devs might want to look into it. Furthermore adding a start time for the bot would be a huge help as you wouldn't have to do that manually at the exact time. Immediately start tweeting not just start the countdown would kind of make sense as well.

For the sake of completeness: the one i explained to be on time, was actually 1 hour late set at 12 hours.

[antgiant]

I think you might be slightly misunderstanding the underlying issue.

When you choose an "every" timing, no matter which one, and click "Run" the spreadsheet immediately sends Google a request to set that up. However, Google does not immediately act on that request. Instead they choose a random time between now and however long from now you selected in "every" to actually run the request. Once that random time arrives and Google runs the request all subsequent runs will be exactly at that time. So there will be no "stacking" of the delay. Also, it is completely out of the spreadsheet dev's hands to fix this. Sorry.

Having said that there is a sort of workaround. Instead of using the every sheet you can use the scheduled sheet. The scheduled sheet asks you to enter a tweet date and time and does it's best to tweet exactly then based on the timing setting. So if you set the timing to hour you will still have the problem, but if you instead choose a faster timing like per minute it will every minute if the scheduled time has arrived and tweet when it does. My recommendation is to use the Auto timing as it will automatically change up the timings to hit the scheduled time within the minute requested while also working to reduce the total number of "runs". Thus saving on your 90 min/day quota usage.

Or put another way the scheduled sheet lets you set a start time. :-) Hope that helps.

[NE0NBLACK]

Say i'd choose a workaround by starting the bot at the intended time minus the delay recorded from the previous run (like DocCyber suggested). By your explanation the action is random, so it should virtually impossible to get the same delay time in the next run. Yet it appears it fixed it for him and the delay remained constant. Also, when it could start randomly somewhere in between 0 and 12h, on a 12h interval, it is very curious that it comes so close to the intended time at all. The longer the interval, the longer the delay it appears. However if the delays do indeed remain constant for whatever reason, for each interval, they could be subtracted in the calculation. Schedule it won't work for me, since they should loop.

[antgiant]

Feel free to try that. My experience has not been consistent timing gaps, but if you try enough times the odds are in your favor. 🙂

Technically, scheduling will work for you as you can use formulas to create self updating times. I do that for a project I run. However, your approach of stop and start the time until it does what you want is much less complicated. 🙂

[antgiant]

Just for kicks I checked some of my logs on a project I run. It had set a 1 hour timer 7 times across a few days. The actual delays were 20 minutes 24 minutes 43 minutes 14 minutes 59 minutes 32 minutes 8 minutes

So pretty random to my eyes. Similar results for all the other timers too.

[NE0NBLACK]

That's f-ed up. Did you also happen to notice that some were too early? I have this on a 6h period. Start 9pm Exec 12:16am (next day) Same bot threw me "Exception: address not available: https://api.twitter.com/1.1/statuses/update.json" after 3 turns.

[antgiant]

I'm not sure what you mean by too early. They always run sometime between when I click run and the specified time.

So in your example you clicked run at 9pm on a 6 hour timer. It actually ran 3 hours and 16 minutes later. That is the expected behavior.

As for the error, yeah Twitter does occasionally error out. (Honestly pretty rarely in my experience though.) Usually you can immediately try again and it will work fine. Another reason to use the Schedule sheet as it will handle that auto retry for you. 🙂 Otherwise it will try again in an hour.

[NE0NBLACK]

By too early I mean on a 6 hour timer it should not start before that 6h have passed. Apparently most of the times it indeed doesn't and is mostly late, while the goal should be to fire exactly after 6 h. I can't say I understand the logic here for the first tweet. Is it like in a queue on googles side and fires whenever it is ready? And how is it that scheduled is not affected by this? If you press start and immediately send the tweet, instead of just starting the timer, wouldn't that solve it? My thinking is not just that the interval is constant is important but the exact timing even more so. You would want to hit twitter peak hours and distribute your bots reliable over a daily schedule. The error didn't breake the run and it continued with the tweet that failed to send at the next turn.

[antgiant]

Yeah sorry this can be a bit confusing. Hopefully this will help.

When you click "Run" on an every timer here is what happens behind the scenes.

1. The spreadsheet figures out that you want a once an hour action.
2. The spreadsheet then tells Google to setup an action to occur once every hour.
3. The spreadsheet is then done
4. Sometime between now and 1 hour from now Google will run that action for the first time. (I suspect that this randomization is to spread the load out on Google's servers.)
5. Until told to stop Google will forever continue running the action exactly 1 hour after it ran it the first time.

So, to answer your specific questions. Yes the spreadsheet could be updated so that step 3 above is send tweet now and then be done. However, that would not change the rest of the process and would result in the first tweet being at a completely different time than all subsequent tweets. (This is actually functionally identical to click Send Test Tweet immediately before clicking run on the every hour tweet.) Hopefully it is clear now but the timing within the hour has nothing to do with Twitter at all. It is 100% Google's scheduled processor. That processor doesn't even know it is tweeting, it just knows that it needs to do something once an hour, and it turns out that something is to send a tweet.

Hope that helps clarify.

[NE0NBLACK]

I think i get it now. If it would be possible to use everyMinutes(n) with any integer, the possible frequency and deviation would lower from 1 hour to 1 minute, right? Then again, the discrepancy should never be > 1h, no?

Let me show you my test setup (24h format): The red font indicates, when I started the bot. Green background is active, red inactive. Underneath the actual time it posted, elapsed time and deviation. I have no idea what's going on here.

[antgiant]

Ahh, I see what you are trying for. I think the every X amount of time is going to cause you quite a bit of pain for that approach. (In short, each column in your example gets its own timer (Step 2 above) with its own offset.) Yes, you could have it tweet every minute and then carefully construct your spreadsheet to only have something for it to actually tweet when the hour arrives, but then you have just recreated the scheduled sheet.

So....I would highly recommend that you use the Scheduled sheet with auto timer.

Here is an example I quickly threw together demonstrating how you might do that. If you look at the Desired Tweet Time you will see that it is a formula that will always show today's date with the time from the Time/Bot column. As a result, it will tweet the same things every day exactly like the every hour bot would with your screenshot above. Additionally, you can change the tweet time to be whatever you want, so if for instance you wanted to tweet at 11:20 instead of at 11:00 you could just set the time to that, and it would do so.

Additionally, by setting the timing to auto it should take care of all the behind-the-scenes timer manipulations required to accomplish the tweets at those particular times with minimal quota usage. However, if you don't care about quota usage then just set it to run every minute and it will work pretty much exactly like the setup you described in your comment.

[NE0NBLACK]

Oh, for some reason i didn't think a formula or anything else than a integer in Schedule won't work. I'll have to look into that.

[NE0NBLACK]

I have to admit running into trouble getting the intended function to work, with my bad Excel skills. If you look at the scheduled sheet, it is pretty much exactly like a daily scheduled bot, like my setup above. But the trouble is that you have to fill all the dates and times for each. Furthermore it will not loop out of the box. This only makes sense if you use various tweet times and intervals.

I think a hybrid of 'every' and 'scheduled' would make perfect sense, where you just enter the first tweet time and interval. It would fix the 'unreliable timing' of 'every' and would make 'scheduled' a lot easier. I suppose this is probably the most desired feature in a bot in the first place.

[antgiant]

You should be able to copy and paste the sample sheet I linked to earlier into the scheduled sheet and have everything work just like you described. I added the formulas for you. (You can just copy all the columns, it does not mess up the scheduled sheet to have extra columns to the right.)

Yeah I've thought about creating a combined sheet like you described, but it has been a few years since the owner of this repo (@Zachwhalen) has responded to any of my questions or pull requests to add new code. Maybe eventually if he stays silent I'll fork this repo and manage it myself, but it won't be soon if I do.

[NE0NBLACK]

Thank you. But sorry I don't understand where to add these columns, and where the tweet text will go. Your approach seems to combine multiple bots into one file. I'd rather keep them separate so that it will be a reusable template. Here's the simplified gist: Would need to add a script to reset the start time to next interval when it reaches the last tweet.

[antgiant]

Does your bot cycle every 24 hours or can it run more or less than 24 hours before cycling?

[NE0NBLACK]

I am toying around with a lot of different strategies to fill the day. I have one bot that is supposed to tweet the same text every 24 hours, so yes basically it is cycling every day. The others are packed with texts. I have one with 365 texts tweeting once every 24h. The fastest is at a 6h interval with 124 tweets total.

[antgiant]

Hmm, you are right getting the formulas right in the Scheduled sheet when not cycling every 24 hours is a lot trickier than I thought.

I was able to get it working with one big catch in the sample sheet I linked to earlier.

The big catch is that once you start tweeting you cannot add or subtract tweets without it messing up your current position in the cycle.

So, for instance if you have 10 tweets lined up and start tweeting, and let's say you get 5 tweets in and add a tweet to the scheduled sheet. Your next tweet to go out could be anywhere in the cycle. Including repeating tweet 5. Hopefully that isn't a problem in your use case. If it is I'll have to think about it some more.

(Note: This would be easy to fix in the behind the scenes code, but is really challanging to fix with spreadsheet formulas as the fix requires keeping track of when you last started the cycle. Without that data the only way to determine cycle start time is to calculate it every time and changing the number of tweets changes the next start time calculation.)

[NE0NBLACK]

Cool cool. I see what you mean, but unfortunately it is above my skills already.

One thought I have is, why are you checking if a tweet row is empty? My idea was to simply calculate the next interval from the start time for as long as you drag that cell down, regardless if it is empty. Not dynamically. I suppose sending an empty tweet won't work and it might throw an error, thus this is a precaution.

I also noticed the "next-->" is missing. No idea if it is an actual bookmark for the code or just a helper. But could it be used to keep track in the queue? For example if it is moved to the next row, could it be used to trigger that check if that tweet row is empty? And if it is, it would be time to restart the cycle with a new start time (calculated from the last tweet time + the interval).

So you never re-calculate or adjust the schedule unless the start time is updated. And that only happens if you reach the end or you change it manually. I hope you understand what i mean :D

[antgiant]

One thought I have is, why are you checking if a tweet row is empty?

I'm actually just counting up how many tweet rows have tweets so I know when to cycle over to the next start time.

My idea was to simply calculate the next interval from the start time for as long as you drag that cell down, regardless if it is empty. Not dynamically. I suppose sending an empty tweet won't work and it might throw an error, thus this is a precaution.

This could work as long as you are ok with coming in and manually updating the start time every cycle. Otherwise this technique will only run once.

If you look close at the formula version I shared you will notice that it is constantly cycling. All the tweets except the next one (and sometimes the last one) are permanently in the future.

I also noticed the "next-->" is missing. No idea if it is an actual bookmark for the code or just a helper. But could it be used to keep track in the queue? For example if it is moved to the next row, could it be used to trigger that check if that tweet row is empty? And if it is, it would be time to restart the cycle with a new start time (calculated from the last tweet time + the interval).

The next--> is provided by the code as a visual helper. It only shows up with the automation is running. The current formulas correctly cycle already. They just can't properly handle the case of adding in a new tweet to the list of tweets after the process is running. Is that a problem?

So you never re-calculate or adjust the schedule unless the start time is updated. And that only happens if you reach the end or you change it manually.

That is the goal. 🙂 Sadly, I haven't figured out a way to write a spreadsheet formulas that only runs when the end of the cycle is reached.

I can think about it some more, but do you really need it to do that? Do you plan to add tweets into the cycle over time or will your initial setup lock in the number of tweets in the cycle going forward?

[NE0NBLACK]

Don't feel obliged to do this for me. I am already happy to have found this and it works how it does. It is just my 2 cents on how i think it should work.

I can think about it some more, but do you really need it to do that? Do you plan to add tweets into the cycle over time or will your initial setup lock in the number of tweets in the cycle going forward?

For my personal immediate need everything is set up, just need better control of the timing. But yes, I plan to add more tweets on some. I could place the tweets already sent at the bottom on the next cycle so that they don't repeat so quickly. Having other users in mind without that knowledge, it will be very confusing.

My idea was to simply calculate the next interval from the start time for as long as you drag that cell down, regardless if it is empty. Not dynamically.

I always have a bad feeling in Excel doing extensive formulas in cells with vital data. Instead I use a helper cell, that wont mess everything up with one bad formula. Since you're counting the tweets, would it be an option to calculate the total run time (tweets x interval) and run a separate timer that updates the start time in a fail safe helper cell?

[antgiant]

Hmm you might be right that I can use the next--> pointer to properly handle mid-cycle additions to the loop. I may take a swing at that. For now though the existing formulas will work just will be annoying when updating the list.

[antgiant]

Oh and I actually am doing the counting and totaling time. Sadly that is the root of the problem.

For instance if I have 5 tweets on a 4 hour timer that makes the cycle exactly 24 hours. Adding a tweet makes the cycle 28 hours. The problem is once you have gone more than one cycle that changes everything. For instance the second tweet on the second cycle runs at 1 Day + 4 hours after tweeting started. However on the second instance that same time slot goes to the first tweet due to the longer cycle. Guess I'll see if I can use next--> to work around this issue.

[antgiant]

I was able to get the next--> trick to work. So, now the scheduled sheet should work just like you want it to. Let me know if you run into issues.

[antgiant]

If this has resolved your issue please mark this issue as closed. Thank you

[NE0NBLACK]

Sorry didn't have time to try it yet. Will do so then. Thanks!

[NE0NBLACK]

Do I have to copy the sheet in the original, replacing the 'scheduled' and set the constructor to 'scheduled'? Anything else?

[antgiant]

Yep that should do it. I would also recommend setting the schedule to Auto or 1 minute to ensure maximum accuracy on posting time.

[NE0NBLACK]

Mmmh... It worked for a while, but then something went wrong. It appears it suddenly reduced the interval which each tweet. Timing is on auto. And in the column the last tweet time appeared for some reason.

Oh i also just noticed the calculated time is two months late(?!) But still tweeted

[antgiant]

Oops that's on me and my formulas. Sorry, I'll trace it down and update here. To help me trace down what happened did it tweet the same thing multiple times or did it purely tweet the next tweet early?

The rest of this behavior is actually expected.

The constant changing of schedule time is expected on auto. Basically it changes the schedule too as far out as it can and still end up before the next tweet. Then wherever Google actually runs it it checks the time and repeats the process. This should always result in a tweet within 1 minute of the desired tweet time, and should look exactly like the last 6 rows in your first screen shot. (You can see the scheduled time climbing down to 1 minute and the tweeting. Then it bounces back to a 12 hour timer exactly like expected. The problem is when it actually ran that 12 hour timer about an hour and 20 minutes later my formulas were wrong causing it to tweet when it should not have.

In the second screenshot the last tweet Time is showing you when it actually tweeted. The idea is so that you can see how close the actual tweet time was to the desired tweet time. However, with the formulas constantly changing the tweet time I can see why that would be confusing. Sorry.

And in the final screenshot that is my bad in naming that column. It is not showing you the next tweet time but the next time the entire cycle of tweets will start over. So, the difference between current start time and next start time is (interval)*(tweet count). Since in this case the interval is 12 hours you must have 127 tweets in your cycle. (12 hours) X (127 tweets) = 63.5 days to tweet them all and start over (or a little over two months).

I can add back the next tweet time indicator if you would like that up top as well. I had it there for a while when testing but then removed it to reduce clutter.

[NE0NBLACK]

Oh i changed the tweet text in the log afterwards, it went down the queue like it should.

I figured it would set the timing, but if you look at it, it does not seem to be reproducible. I mean i suppose it should cycle through the timings under settings in decreasing order (12h, 8h, 6h, 4h, 2h, 1h, 30min, 15min, 10min, 5min, 1min) but it appears it skips some instances. There should be a repetitive log entry in that order or not?

In the second screenshot the last tweet Time is showing you when it actually tweeted. The idea is so that you can see how close the actual tweet time was to the desired tweet time. However, with the formulas constantly changing the tweet time I can see why that would be confusing. Sorry.

Oh that's cool, it just happend to be the tweet that misfired and was confusing.

And in the final screenshot that is my bad in naming that column. It is not showing you the next tweet time but the next time the entire cycle of tweets will start over. So, the difference between current start time and next start time is (interval)*(tweet count). Since in this case the interval is 12 hours you must have 127 tweets in your cycle. (12 hours) X (127 tweets) = 63.5 days to tweet them all and start over (or a little over two months).

Makes sense. It was confusing at first. Also to have the new times for the tweets already sent and not the actual sent time. My expectation was to update the whole sheet at the end not continuously. I guess once the whole thing is explained and proves to work, no need for this additional info.

[antgiant]

I figured it would set the timing, but if you look at it, it does not seem to be reproducible. I mean i suppose it should cycle through the timings under settings in decreasing order (12h, 8h, 6h, 4h, 2h, 1h, 30min, 15min, 10min, 5min, 1min) but it appears it skips some instances. There should be a repetitive log entry in that order or not?

Correct. The skipping is due to the random firing issue. So with a 12 hour timing it first sets a 12h timer. However, when that actually fires it figures out how much time is left to hit an actual 12 hour interval and sets the longest possible timer to hit that. Might be 8 hours, might be 1 minute. All depends on when it actually fired. That process repeats until it actually hits just the right time to fire. Then it starts over for the next 12 hour time. I suppose in theory it might even eventually get lucky and exactly nail the 12h cycle. In that case it would completely stop updating the timer as it would be doing exactly what is desired. 🙂

My expectation was to update the whole sheet at the end not continuously

In the interest of meeting expectations the sheet now does this. 🙂 I also did much better QA this time so the only time I am worried about is when the cycle restarts and I think I fixed all of those challanges.

You should be able to simply copy the new formulas into your sheet like before. (I changed all three and added in "Next Tweet Time", so all four need to be updated.)