I’ve been passionate about coding for a while now. When I got my first job after graduating from college (with a philosophy degree, by the way) I worked a very basic data entry position at a shipping company. The job was fairly simple: use the WMS software to generate up-to-date excel spreadsheets and update our ‘Master’ sheets with certain data points for given products.
It was repetitive, mundane, boring. I thank God for that job every time I think back on how I started off. It was because of that job I coined the term that got me into coding, “laziness breeds innovation.” I simply didn’t want to put forth the effort to spend 40 hours a week copy/pasting the right lines from one excel sheet to another if a better solution was available, so I made a couple hundred web searches. Within a month, I had written some (very sketchy) code that housed the incoming data in a tab of the workbook, mined it for data, and updated the appropriate cells in the Master sheet. Over the course of the next few months, I kept searching the web and found ways to make my code more efficient and stable. In the end, I found out that SAP — the software we used to track shipping data — had a macro recorder just like Excel. So I recorded a few of my usual transactions and had the audacity to insert the recorded code with a few modifications right into my Excel VBA. The crazy part? It worked.
So the philosophy grad turned data entry tech took an entire 40-hour work week and automated it down to about 5 minutes. I simply got to work and ran my code. It cycled through all our product groups, opened up an SAP session in the background, generated the reports, and put them into my spreadsheet which did the rest. Everything was updated for all of our shipping data by about 8:15 am on Monday morning.
“laziness breeds innovation”
A long story short: I didn’t keep the job for long. I managed to scrape together some other projects that kept me around for a few more months, but after a while, there wasn’t much need to keep around the guy who basically coded his entire job. So I had to move on.
That job taught me two very important lessons:
- Repetitive stuff needs to be automated.
- The best way to learn to code is with a tangible, objective problem that needs a solution.
Most developers cringe when I tell them I like VBA. Perhaps I’m still naive enough to simply enjoy the nostalgia of it. It was the first language I learned, and I’ve found some pretty exciting things to do with it.
Lately, though, I’ve been drawn towards the Google Office Suite, and specifically, Google Sheets. Personally, I like it more than Excel, and it has some functions that are built in that just make sense and could have helped me a lot on certain projects I had to make in Excel. For instance, Google Sheets has a
split function that splits a string along a delimiter — or multiple delimiters — and puts the resulting string(s) into an array. If used alone, this array is actually printed out in multiple cells. This multi-cell printing from functions is a feature that makes Sheets extremely valuable for a lot of different applications that I appreciate. This array, however, can also be referenced by other functions like
vlookup. Generating arrays from simple — built-in — formulas, then, is a major advantage that Sheets has over Excel.
It was then I decided to go all-out and make a Twitter Bot. Having no relevant experience or clue of what I was doing, my goal was simple: send a few tweets with relevant quotes, readings, and information for Orthodox Christians. This is a niche market on Twitter, for sure, but one that has static, daily resource needs. Such a bot is also a tangible end goal that allowed me to focus. I wanted to take the next step, so I decided to get my own Twitter Developer account and do all the API calls myself. I also didn’t want to spend any money on hosting the code, so I was determined to keep it in Google Sheets. I submitted my application and got access to create an App, complete with Access Keys and Tokens.
The Fun Begins!
The first thing I had to do was get the code for accessing the Twitter API. Luckily, there’s a Twitter Library for GAS, which can be added to any Script project (Resources > Libraries, Add a Library).
Twitter GAS Library:
In order to access the Twitter API, an App’s Access Key & Token need to be used. These are generated when you create an App in Twitter’s dev portal, so I won’t go into too much detail about that here (there are hundreds of guides available online that can walk you through step-by-step).
Once the Keys and Tokens are available, they are used within the code to validate access. There’s a lot of conflicting information out there on how exactly to connect to Twitter’s API and send a tweet through GAS, but thanks to Amit Agarwal, I was able to find a very slim chunk of code that I got all the basics without excess complexity. Taking this and modifying it as I needed, I had a foundation upon which I could build the bot.
Note: the line
var twit = new Twitter.OAuth(props); refers to the “Twitter” Library. If you name it something else when importing the library, this needs to change. I mention it here because it gave me a headache for a few hours because I didn’t know any better. 👏 Always 👏 know 👏 your 👏 variables!
Now, let’s write some code!
In order to generate a simple tweet, the first step is validating access. Here’s what our code looks like after modifying the code from the link above:
This code relies heavily on the Twitter Library. After generating some variables in lines 3–11 (for logging, sheet reference, and Twitter App credentials), we need to set up
props and set the properties (12–13). This is useful for passing all our credentials as an object to Twitter’s authentication function (15). If the credentials work out, the
service object will have the
.hasAccess value set to
true (take a look at the Twitter Library, twitter.gs, lines 34–177 to see how this process grants access).
If there’s an access issue, we simply log the failure and the process ends (22–23).
Otherwise, if access is granted, the rest of the process is nested in the
else statement. First off, we’ll log the success, then set the
status variable to the
TweetText variable passed through the function.
Note: The reason I take the variables passed through the function and assign them to different variables is two-fold: first, it’s cleaner as the values inserted into the function generally need to be altered, and second, the Twitter Library
.sendTweet function takes in a status message (tweet text) and a ‘params’ array which includes all of the other arguments possible (Twitter Library, twitter.gs, lines 321–329).
Handling Tweet Replies
Next, we’ll process if the function has been given a
IdInResponseTo. This is how an App can communicate with Twitter’s API to reply to a specific tweet. Every tweet is assigned a specific ID and any time you reply to a tweet on Twitter, your response tracks the ID of the tweet to which you’re replying. This value is used for when a bot replies directly to any tweet. The bot uses this for two key purposes:
- Responding to an inquiry. A user asks a question and the bot responds to the tweet with the answer.
- Tweet threads. Tweet threads are basically just the same user replying to tweets in order. For some longer text items, we will make a function to break down the string and form it into a thread.
The code here is rather simple. We’ll first check if there’s no reply passed through the function or if the value is 0, which is included as a placeholder in the case of multiple values needing to be used with the function. If there’s no tweet ID to reply to, we’ll simply log it and move on (lines 1–3).
If there is an ID given, we set our variable, log the ID, and insert the ID into
params. This is an array we set up earlier (OrthoBot1, line 14) to handle the
params portion of the
.sendTweet function in the Twitter Library (twitter.gs, lines 321–329).
Finally, we need to add
.auto_populate_reply_metadata = true; to
params. This is due to a bug/feature of Twitter’s API. If we send a tweet replying to another tweet, but there is no mention — or tagging — of the user who sent the original tweet, our tweet fails to properly “reply”. It will still post to Twitter, but it will be a stand-alone tweet. Luckily, with this option, we can auto-populate the user ID of the person who sent the original tweet.
Twitter allows each tweet to contain up to 4 images. I decided that since I was working with a Google Sheet — entirely online — I should probably use image URLs for my image sources. Since multiple images are a possibility, I wanted the functionality to be able to use all 4 if I wanted to, so I decided that the single variable for the
.sendTweet function should just be a comma-separated string value. Keeping the value(s) as a single string prevented any messiness from commas that may cause the function to think they’re different variables. Here’s an example of a successful call of the function to illustrate:
sendTweet("Here are some great memes!",0,"bigchungus.jpg,instaegg.png,");
Obviously, those are not valid URLs, but it illustrates the point. The
status is a string value, followed by the tweet ID for replies, followed by a single, comma-separated, string value for the image URLs. Now, here’s how it’s processed:
After checking if there’s an image value and logging if there isn’t (lines 1–3), we’ll attempt to process the images. This is nested a
try statement because the errors that kept it from working for me — this was by far the hardest part to troubleshoot — often return different data. I wanted a way to capture each of these (29–31).
First things first, we need to establish the array which will be ultimately added to the tweet
params. This is
mediaId (6). In order to populate the
mediaId array, we need to split the string value of our URLs (7) and process each in turn (9–26).
We’ll start by creating a “blob”. I’ll admit, I had a good laugh when I found out this was a real object. A quick web search gave me the underlying code for making a blob object from a URL (11) which we can pass through the
service being the variable established earlier for Twitter Library Functions (OrthoBot1, line 15) — function of the Twitter Library (twitter.gs, lines 200–236). This generates a Media ID, which is referenced by the Twitter API.
.uploadMedia to generate an ID from the blob, we check if an ID has successfully been generated (13). If it has, we add the image ID to
mediaId(15). Given that the media IDs need to be comma-separated, we’ll add an additional step for the second, third, and fourth images which includes the preceding comma (16–17). There is a fail-safe built in that only allows 4 images to be uploaded — numbered 0–3 — so we only add items to
mediaId if less than 4 images have been processed (16). If for some reason we mistakenly pass more than 4 images, the rest are ignored and a message is added to the log (19–21).
After logging our IDs (27), I add the
mediaID value(s) to the
Sending The Tweet
Now that we’ve authenticated our connection to the API and established our tweet text, reply status, and images, all that’s left is to put it all together and (finally) send a tweet!
The actual sending of the tweet is rather simple. Since we’ve processed all of our data and added it to the
params it’s really a matter of calling
service.sendTweet with our tweet text (OrthoBot1, line 26) and
params(lines 2–3). Setting
var response is an easy way to track if the Twitter Library function “returns” anything (twitter.gs, line 340). If there is an error the Library’s
.sendTweet function will not return a value, and thus our
response variable will be NULL.
Thus, when we check for a response (4–16) we can easily determine if our tweet attempt was successful or not. In the event of a successful tweet, the Twitter API sends back a lot of data which is assigned to various attributes of the
response variable. As such, we can use our
logsheet — established at the beginning of the function (OrthoBot1, line 4)— to keep a record of all our tweets (7–15), which looks like this:
If no response is given, that is simply logged as there’s no information we can parse from a NULL response (17–18). This is one drawback of Twitter’s API not returning any value for a failed tweet attempt. However, we can pull an error more broadly from our
try statement (line 2). In the event of an error happening here, we can sometimes log valuable information (20–22).
sendTweet function here — not to be confused with
service.sendTweet from the Twitter Library — will return the ID of the successful tweet (16). The reason for this is very simple: if we want to create a tweet thread, we need to use the
sendTweet function but also house the ID we just sent so that we can send another tweet in response to it.
For reference sake, here is the full code for the sendTweet function.
That’s it! We have successfully sent a tweet from a Google Script! While that’s great, we can do more. Using the tools we’ve built thus far, we can really open up what Twitter is capable of by adding thread support.
Oddly enough, Twitter’s API does not have a built-in function for generating threads. From the impression I got in the forums, it seems like they may not actually want this to happen. Regardless, it’s possible, and here’s how we can do it:
The function is relatively simple. We pass
stringToTweet as our desired text we want to tweet. Generally, this will be over the allotted 280 character limit, though it will work just fine for single tweets as well. Next, we can pass a
delim value to serve as an extra delimiter for the string. This is useful for bookending threads. For instance, if we want to send a thread, but use the first tweet to give it a title and announce that a thread is incoming, we can simply insert a delimiter character into the string — I use pipes, “|”, frequently for this purpose — to signal to the function that an additional split is needed.
img value can be passed as well. This is a URL of an image to attach to the first tweet of the thread. With a little — or a lot — of extra work, I could implement a way to use multiple images and determine to which tweets they attach, but that was beyond the scope of my intended purpose. As of right now, a comma-separated string value can be passed to the
img value, and each tweet will contain the relative image in the string value (first tweet, first image; second tweet, second image, etc).
r value is used to determine the tweet ID to which this thread should reply. This can be left out to make the thread its own stand-alone content.
The first step, as with
sendTweet is to establish our variables.
twtMaxis equal to the maximum characters allowed by Twitter (this has proven to actually be a variable in recent years, so we can change it with ease here).
totalestablishes the total character count of our
startis a placeholder for the starting character of the current section we’re parsing. This will make more sense when viewed in practice.
excessis the number of characters after the last space in a tweet-length section beginning at the
startcharacter of our
stringToTweetvariable. As we’ll see below, we trim the excess away from our 280 so that our tweets don’t cut words in half.
parsedis the final “tweetable” string of text extracted from
stringToTweet. This is added to our thread array.
Next up, we need to check the length of our desired string. The way this works is to initially split the
stringToTweet value along the
delim value into the
thread array (line 4).
Now we cycle through the
thread array and check to see if any of the sections are longer than 280 characters. If they are, we then parse them.
For content longer than 280 characters, we loop through this process (line 2). If our start character is less than our oversized text length, we still have content to parse.
First, we establish our total tweet’s length (3), figure out how many extra characters are after the last space (4), and establish a parsed substring from our
thread item which starts at our
start value and returning a string with a length equal to 280 minus our excess (5). This string is now our “tweetable” section of the
thread item. To preserve it, we add it to our
tweets array (7). In order to ensure that our next run through this loop is successful — or at least different from this one — we need to change our
start variable. We do this by adding the length of our
parsed string to our
start value plus 1 (to ensure we start on the next character, line 6).
else statement here refers to earlier (OrthoThread2, line 14). Basically, if our
thread item does not require the above parsing, we log it and add it to the
tweets array (14–19). Before moving to the actual tweeting process, we need to process our
img value, if present.
Since the bulk of the image processing from a URL is handled in the
sendTweet function, we don’t need to do much here other than parse out the URL list, which can be done with a single
split call (line 3). This will generate an
imgs array from the
img value (note the plural vs. single).
Now we have a
tweets array of tweet-length strings we can send out and a
url value with a set of images. The final step is to actually send the tweets!
After a quick check to see if we are sending our first tweet in reply to another (indicated by the presence of an
r value initially populating our
replyTo value, lines 2–6), we loop through our
First, we see if there is an image in
imgs that corresponds to our tweet number (this is the reason that only one image is allowed per tweet, lines 10–16). If there’s an image present, we establish the image URL as our creatively-named
Now the magic happens. We establish our
newTweet variable as a call of
sendTweet passing our
replyTo (if present), and our
url(21). It is important that the
sendTweet function be assigned to a variable, rather than just called, as we will see shortly.
Given the speed at which this script processes, I ran into some errors tweeting multiple times in a single script, so we need to
sleep for a bit after calling
sendTweet. 250 milliseconds is quite enough for our purposes here. This allows enough time for the script to reach out to the Twitter API and receive a response. This response (returned from the Twitter Library function) is stored as our
newTweet variable. This is critical because now each
newTweet simultaneously sends a tweet as well as stores the information regarding that tweet.
You’ll recall that above, our
sendTweet function has a
return which returns the string value of the sent tweet’s ID. as such, our
newTweet variable is automatically set to the ID of the tweet it sends. The reason this is so useful is that we can set our
replyTo value to that ID (27). If an error presents itself, we can break the loop which tweets our thread (28–30).
Now we simply continue our
for loop with the next tweet in
tweets. Luckily, our
replyTo value has been updated with our previous tweet, so when we call our next
sendTweet we are set to reply to our previous one, creating a tweet thread.
tweetThread function has a
return as well (line 35). This is the
begins variable, which is set to the ID of the very first tweet in the thread. I have found this useful for printing the ID of my most recent threads in Sheets.
So that’s it as far as threads are concerned. For reference, here is the entire function.
The “Bot” Part
If you’ve followed along with the above content, you may be thinking, “that’s great and all, but this just covers the Twitter API, what about automating the whole thing?” Fear not! I’ve got you covered. The trick is setting up additional functions that can determine and pass values from your spreadsheet into the
tweetThread functions we’ve already created.
This is where the extra functions of Google Sheets really comes in handy. There’s a lot that can be done, but I’ll give one example that encompasses everything we’ve covered in this article so far.
Like I stated earlier, my Bot tweets Orthodox content. One of the main features is sending out tweet threads containing the daily readings. For the sake of instruction, the content isn’t relevant, but I want to give an example of how our code looks in action. Here’s what we’re doing:
- Using Google Sheet’s
importXMLfunction to pull in data from a website to an array of cells in a worksheet.
- Processing the data from those cells into usable text.
- Writing a script to use the data
- Creating triggers for specific Scripts to run automatically.
This process can be modified to fit your needs, but I’ll provide a look inside my Google Sheet so you can see how it’s made and how I use these functions.
The first step is to grab data from our website. I use the Orthodox Church of America site as it is reliable, updates daily, and has clean HTML that I can easily navigate with an XPath query (the required second element of the
In my A column (Reading) I have the following function written in the first row of the table (A2):
I won’t go into much detail about how importXML works since plenty are available, but the quick version is this: you go to the website you specify and query the HTML with the XPath specified. This returns an array of values and prints them in the column. The formula is in the first cell, and it simply prints out down the column.
In this case, I’m looking for links (in XPath,
li) within the
div main-col-contents section. This query will pull the link text, not the hyperlink. This is useful because OCA’s site always has the next name as the link text.
In the B Column (Slug), I have the following function:
This is very similar, but the addition of
//@href to the XPath query will give us the actual hyperlink of the link itself rather than the text of the link.
From there, in the C Column, a simple
concatenate gives us the full web address:
Now in each row in the D column, I have another
importXML to find the heading (in XPath:
h2) of the reading page, removing a few redundant words:
2. Processing The Data
The E Column shown is experimental and I never ended up using it, so finally we have the F Column, which is the string value which we pass through our
tweetThread function. This is a bit difficult to parse out, so I’ll add the line breaks in:
"Today's #Orthodox #DailyReading ",
" is ",
"|Daily readings courtesy of @ocaorg ",C2),
This may look a bit intimidating, but if you have Excel experience, you’ve probably seen worse. The whole formula is duplicated because it’s nested in an
if statement that is dependent upon there being a value in A & B columns. If there isn’t, we need the cell blank so the script sending our tweets doesn’t tweet some error text (blank values will throw a tweet error and nothing will happen).
After that, we are using
importXML to pull in the website URL from the C column (this is the Index/Match section. The XPath query is bringing in the text contents from the individual readings pages (HTML elements
All this data is concatenated with spaces (hence the need for the
ArrayFormula around the
Concatenate). After that, we just bookend the beginning thread statement with our pipe delimeter and our closing tweet giving credit where credit is due.
3. Writing a Script to Use the Data
Now, with our table set up in Google Sheets, we can write a script that takes the data directly and runs it through our previous functions. Here’s the script I wrote for sending the daily reading threads:
After setting our variables for the Spreadsheet (line 2), date, hour, and minute (3–5), and our delimiter (6) it’s time to see if we’re sending a tweet.
In the event that no change happens on the OCA website — which would be rare, but possible — the contents of the website wouldn’t change, and neither would our table. This would mean that we’d tweet day-old readings, and we don’t want that, so if it’s before 3:00am, we clear the table (10–13). Don’t worry, if there’s legitimate data, it’ll repopulate.
For maximum exposure on Twitter, we don’t want to start tweeting too early, so we’re waiting until between 8:00am and 7:00pm (14). If we fall within that window, we need to grab one thread per hour — to space out our tweets — by setting our
thread variable to the string value in our F column. We determine the row by our
h value minus the number of hours we’re waiting. So at 8:00am, we are sending off the value in F2 (line 16).
If we have a value in the cell specified above, we set the
tweetThread function to a variable to track our
response. If not, we simply log that there’s nothing to be found and wait until the next time the script triggers (19–27). You may note that in the event of this failure, there’s an attempt to retweet the thread from 12 hours ago. This is a function we haven’t covered, but it’s written off of the Twitter Library (twitter.gs, lines 380–399) and is pretty easy to figure out if you’ve made it this far.
After we successfully call the
tweeetThread function, we print the return — which is the first tweet’s ID — in the G Column.
4. Creating a Trigger
The last step is to make a trigger for this script. Simply navigate to “Current project’s triggers” — this will open a new tab — and create a new trigger.
Select the script to trigger (in our case,
readingThread), choose “Time-driven” for a source, then “Hour timer” for type. Lastly, choose “Every hour”. Now, this trigger will run our script every hour. Our script is built to handle this as it controls for a specific hour range. It helps to have in mind how you’ll trigger the script when writing it.
One Last Thing
If you plan on using the actual Sheets to process data as I have done above, you will need to set your spreadsheet to automatically recalculate. This will ensure that the formulas in your sheet will calculate on a basis you can choose. Normally, Sheets updates formulas when you open it, however, if you want a complete automation experience — particularly if you’re importing data from external websites — you can set your formulas to calculate up to every minute. Here’s how you can do it.
That’s it! Now you can watch as your bot tweets content for you. Here’s the first tweet of the thread from the above screenshots:
This was a tweet generated entirely by my Google Sheet communicating with Twitter’s API via the ‘OrthoBot’ App I created.
You will notice that when people send tweets, Twitter will display the source of their tweet (if viewed with the Twitter mobile app). If they enter it from the website, you will see “Twitter Web Client”. When you create your Twitter App, bear in mind that the name will appear on every tweet and choose your name accordingly.
So there you have it! A (relatively) simple Twitter bot hosted absolutely free in a Google Sheet.
I did this primarily as a learning experience, and as a way I could add some content to my Twitter feed that wasn’t people griping about politics. On both counts, I think this was a major success. I wanted to share my process because I’ve seen a number of places on the web where people have offered Google Sheets that use their registered Twitter App, but don’t give you much control or customization. For those of you who can think of a use for a simple Twitter bot, but don’t want to spend money for hosting the necessary website to house the backend, this may be the solution for you. And as cliché, as it sounds, if I can do it, so can you.
Things To Note
- I still haven’t worked out all the kinks with how my sheets relate to my scripts. If you visit OrthoBot on Twitter, you’ll see the occasional tweet that just says “#N/A”. This is an issue with my Sheets formulas not accounting for all possible errors. It’s not an issue with the
sendTweetfunction. I’m slowly resolving these as they manifest.
- I’d like to work through a way to determine where various images will appear in a tweet thread and add support for multiple images per tweet. This hasn’t been a huge priority, but it’s something I’d like to add if I end up creating this as a template in the future.
- In the event of a failure in the
tweetThreadfunction, the code simply ends with a
breakstatement. I’d ultimately like to have an error catch that will essentially go back through each tweet in the thread already sent and
.destroyit. This is an available function in Twitter’s API, but it would require creating another array for all tweets sent out, and cycling through them in the event of an error. Luckily, after some stabilization, I haven’t had any thread failures that have resulted in only partial threads being posted, but it’s always best practices to have the most available error handling, even if it’s for rarities.
- If you made it this far, THANK YOU! I hope it was as much of a joy to read as it was to write. It’s my intention to add more functions and continue writing on this subject as I learn.
UPDATE: I’ve created a template for creating your own bot with Google Sheets and written a quick start guide: