Template: Batch-schedule tweets from Google Sheets
This is the second post in a multi-part series on building a Twitter Scheduler Center using Microsoft Teams, Power Automate, and AtBot. See part 1 for context.
Batch-scheduling tweets is an easy way to keep a strong Twitter presence without having to constantly tweet and retweet in real time. A schedule of tweets provides a powerful foundation to your Twitter strategy.
This template uses a Google Sheet as the source of your tweets and schedules them at the time you choose for each tweet using Power Automate.
The template provides interaction with AtBot, making it an AtBot Skill, so you can easily kick off your schedule process directly in Microsoft Teams. This post also discusses adding your Sheet (the Batch Scheduler) as a tab in your Team, making it part of a Twitter Scheduler Center.
Create your Google Sheet
We’re going to use Google Sheets because — believe it or not — Power Automate’s integration with Microsoft Excel doesn’t support this functionality, but Google Sheets does. At some point, Excel will likely catch up, but for the time being that’s not the case. So we have to use Sheets.
Open drive.google.com, log in with your account (or create one for this use and provide access to your colleagues who will also use this account), open this template, and click File > Add to My Drive.
Here’s an overview of each of the columns and what they’re for.
Tweets
You’ll copy the tweets you want to batch-schedule from your Library in Excel and paste them in this column. You can take any or all of the tweets from your Excel Library, and you can put them in any order you’d like.
Order
If you didn’t feel comfortable reordering the tweets in the Excel Library, you can do it here. Each cell simply consists of the following formula:
=RAND()
If you have more rows of tweets than you do formulas in this column, drag the bottom-right corner of the last cell (that contains a value) down to the last row in the Tweets column.
Reorder this in A→Z and your tweets will now be random. If you want any specific tweets inserted among these randomly ordered tweets, paste them in the row(s) that’s applicable.
If you don’t want to reorder the tweets in Google Sheets, you don’t have to. Simply ignore this column. It doesn’t actually do anything other than let you randomize the order of the tweets.
Publish Time (Local-Calc)
This column will is used to calculate the date and time that each tweet will post. The column must be formatted as date and time (it can’t simply be “date”). Once you’ve ordered your tweets in the order you prefer (as in, the order of the tweets in the Tweets column is final), you need to provide dates and times for each of the tweets to post.
Generally, for a tweet batch, I don’t care when the tweets post, only that they 1) do in fact post and 2) aren’t so close together as to annoy my followers. You want your content to be useful, but not overbearing.
If you know when your content is most usually viewed (reports in Google Analytics for your website can give you a clue), you can add times that align with that. But that takes some time for not-all-that-much return on your investment.
Instead, I generally have Sheets calculate a random separation between my tweets. I like to stick to posting a new tweet between 2 and 5 hours after the last tweet. Sheets can easily do a random calculation for me and provide a safe space between tweets that appears natural. That means you’re not stuck posting at 1:00 pm, then 3:00 pm, then 5:00 pm. You can do 1:43 pm, 4:02 pm, then 7:18 pm.
It’s very easy to do this:
- Add a start date and time in cell C2. This is the date and time you want your batch to start tweeting.
- In cell C3, add the following formula: =C2+(RANDBETWEEN(2,4)+RAND())/24
- Drag the bottom-right of cell C3 down to the last row of tweets.
Sheets will have scheduled between 2.0000 and 4.9999 hours apart, in order. Let’s dissect the formula a bit:
=C2+(RANDBETWEEN(2,4)+RAND())/24
RANDBETWEEN(X,Y) will provide a random integer (i.e., whole numbers without values beyond the decimal point) between the two given values, X and Y, you provide. In this case, the values are 2 and 4. So the only random values it will provide are 2, 3, or 4. RAND() adds a random value between 0 and 0.9999. Basically, adding RAND() provides value after the decimal, which makes the spacing between tweets seem more organic. We’re dividing the RANDBETWEEN/RAND sum by 24 because date and time cells in Sheets are assumed to be in units of days. Dividing by 24 makes it hours.
Publish Time (Local-Static)
The down side to the calculated time listing is that any change at all that you make in the Sheet will cause the times to change. It’s not bad if you don’t really care when to publish these tweets, but if you want to know when the batch is done, or you want a historical record of when each tweet gets published, you need to take column C, copy the values, and paste them in column D.
In fact, each time a tweet is published, Power Automate adds a new value in column F (PowerAppsID) for that tweet, so even this change will cause all RAND calculations change. So your column C values will change with every post.
So to avoid this problem, copying and pasting the values from row C to row D will keep them static and you’ll have an accurate schedule of your tweets and know exactly when the batch will finish.
Publish Time (UTC)
The Pub Time UTC column translates your post times from your local time zone to Universal Coordinated Time (UTC), also known as Greenwich Mean Time. Power Automate runs on UTC, not your local time zone, so you have to translate for Power Automate. You either add or subtract the number of hours between your time zone and UTC.
Like columns C and D, this column must also be formatted in date and time, so update the format before you start working with this Sheet.
If you live in the western hemisphere, count the number of time zones you are away from UTC and add that to your local time in column D. If you’re in the eastern hemisphere, subtract that value. If you’re not sure, go to Google and search “difference between UTC and me”. Presuming you have location services enabled, it will tell you. If UTC is further ahead in time than you, you’ll add the difference; and vice versa.
For example, I live in the US Eastern Time Zone. Currently it’s standard time (not daylight saving time), so I’m five hours behind UTC. The formula I input in cell E2 is:
=D2+5/24
Remember from the last column that all values in time and date fields are in units of days by default, hence the 5/24. Drag the bottom-right corner of that cell to the last row of tweets and you’ll have the correct UTC time for all your tweets.
Create the Skill
A templated version of this Skill is currently under review for inclusion in the Power Automate website’s template area. At that time, I’ll update this post with the link. Until then, it’s easy enough to add the Power Automate steps as explained below.
1. Log in to office.com with your user name and password and add AtBot to Teams if you haven’t already.
2. Browse to the AtBot Power Automate Connector page and click ‘When an intent is used’.
3. Name your Flow.
4. Complete ‘When an intent is used’ step.
In When an intent is used, add a Trigger description to help your users understand what the Skill does; change Bot trigger type to Shared if you want colleagues to be able to use this Skill in AtBot; and update Keywords to a different trigger command if you’d like. (Keywords is what you’ll type to AtBot when you want him to kick off the Skill.)
5. Add a AtBot ‘Send Reply’ action.
I like to rename my steps so it’s clear what they do, so I renamed this step Let user know the batch schedule Flow has started. This step is confirmation from AtBot to the user that the request was received and is being worked.
Update Reply text with something like: “Sounds good. I’ve scheduled this batch of tweets. Each tweet will be posted at the time you specified in the Google Sheet. Please do not make any changes to the content of the Google Sheet.” You can customize this text to whatever you’d like.
In Reply Activity, add the Reply Activity dynamic content parameter.
6. Add a Google Sheets ‘Get rows’ action.
If it’s your first time using a Google Sheets connector, you’ll be asked to log in to your Google account. Do so.
In File, browse to the Google Sheet that you set up and select it. In Worksheet, choose the worksheet in the Google Sheet that contains your tweets and times.
7. Add an ‘Apply to each’ action.
In Select an output from previous steps, add Records value from the dynamic content parameters.
8. Add a ‘Convert time zone’ action in the ‘Apply to each’ loop.
In Base time, select Pub_x0020_Time_x0020_(UTC) from the dynamic content parameters. (You’re having Power Automate pull the UTC time from the Google Sheet.)
In Format string, select Round-trip date/time pattern.
In both Source time zone and Destination time zone, select your time zone. (No, you can’t actually use this step to correctly translate your local input time to UTC automatically. I’ve tried and it doesn’t work. Sorry.)
This converts the Google Sheets format to almost what Power Automate needs to understand the input date and time.
9. Add a ‘Delay until’ action in the ‘Apply to each’ loop.
In Timestamp, choose the Expression tab from the content pane. Add the following text into the fx (function) box and then click OK.
concat(body(‘Convert_time_zone’),’Z’)
This adds the last bit of formatting to your time so Power Automate understands it completely.
10. Add a Twitter ‘Post a tweet’ action in the ‘Apply to each’ loop.
If it’s your first time using a Twitter connector, you’ll be asked to log in to your Twitter account. Do so.
In Tweet text, choose Tweets from the dynamic content parameters.
11. Add a AtBot ‘Send reply’ action in the ‘Apply to each’ loop.
This step is optional. It makes AtBot send an update to the user every time one of the tweets is posted. It can be handy if you want to know when tweets go out, but could get annoying. You can ignore this step if you don’t want it. (It’s useful for testing, though. I can vouch for that.)
In Reply Text, add something like “The following tweet just posted:” and add Tweets from the dynamic content parameters. You’ll receive a notification that the tweet posted with the actual tweet content.
In Reply Activity, add Reply Activity from the dynamic content parameters.
12. Add a AtBot ‘Send reply’ action AFTER the ‘Apply to each’ loop.
This step has AtBot send an update to the user once the batch has completed. While not necessary, it’s very useful to be reminded when the batch is done.
In Reply Text, add something like “I’ve completed your batch tweet schedule. It might be time to schedule more tweets!”
In Reply Activity, add Reply Activity from the dynamic content parameters.
13. Save your Flow.
This is what the whole thing should look like from top to bottom.
Add your Batch Scheduler to Teams
If you’re using this Skill as one part of the Twitter Scheduler Center, you’ll want to add the Batch Scheduler as a tab in your Teams Channel. This gives you super quick access to an editable version of the Batch Scheduler directly in Teams, as shown in the finished Center below. (The Batch Scheduler is the second callout.)
To add the Batch Scheduler to Teams, we want an editable version of the file (so, that means we need to be logged in in the Teams tab), but we want the tab to point directly to the Batch Scheduler, not the Drive home page (since we’d have to browse every time to the Batch Scheduler). Here’s how to get that direct, but still protected, link to the file.
Browse to the Batch Scheduler in your browser, click Share > Get Shareable Link. Then click the Anyone with the link can view drop-down > OFF — only specific people can access > Copy link. This gets you the link without opening access beyond your account.
Next, jump back to your Teams Channel, click the + next to the Tweets Library tab (if you’ve already created it), choose Website, name the tab (keep it short), paste the link, uncheck Post to the channel about this tab, and click Save.
Your Batch Scheduler Sheet is now available — and editable! — directly in your Teams Channel.
Test it out
Browse to your Google Sheet, or to the Batch Scheduler tab in Teams. Add a few tweets and spread them out by only a few minutes; I used a three-minute separation in the Pub Time (Local-Static) column (otherwise you’ll be waiting hours just to see if they post).
Start a new conversation and @mention AtBot with the keyword Batch those tweets (or whatever you used). This kicks off the Skill. Each time AtBot posts a tweet, he’ll let you know. Once the batch is complete, he’ll let you know that too.
And here are the tweets in Twitter.
Things to keep in mind
- All of these tweets run as one batch in Power Automate. If you cancel the Flow, it cancels all the future tweets on your schedule.
- Do not schedule tweets out further than 30 days. Each Flow is limited to a run duration of 30 days. In fact, I wouldn’t schedule further out than a week or two, just for the sake of sanity.
- Twitter is picky about reusing tweets. I haven’t found it well documented anywhere, but I can tell you I’ve been hit with errors for trying the same tweet within the same day from this method. A denied tweet will cause an error that will cancel the whole batch. Keep that in mind.
- Twitter is applying new automated policies in its effort to curb fake news and this may affect automated scheduling. I haven’t seen it affect any of my batch-scheduled tweets, but I have gotten denial errors stopping me from tweeting an actual retweet of my own from Twitter on desktop.
- If any tweet produces an error, the entire batch will fail from that point on. And there is no way for you to know this except for checking on the running Flow every once in a while.
Remember, this is just the start
Sure, batch-scheduling tweets is useful, but there are a lot more possibilities you could play with. Some ideas:
- Add an error reporting mechanism wherein AtBot contacts you if your Flow fails for whatever reason, with the row number of the offending tweet.
- Set up this flow to recur every x days so you don’t have to manually set up your batch as often. This gets you past the 30-day run duration limit in Power Automate.
- Run many batches from multiple sources. Create a copy of your Google Sheet in case you want to run a special campaign separate from your source list. Or maybe you just don’t want to end the running campaign. Create a copy of the Skill and point it to the new source and run it from there.
Next up: create your Ad Hoc Tweet Scheduler
Check out the next step to completing your Twitter Scheduler Center: create an ad hoc tweet scheduler so you can add new tweets on the fly without having to re-batch your current schedule.
Next up: Template: Schedule a tweet when it’s added to SharePoint.
