I created a Twitter Bot from nothing but a Google Sheet

And I didn’t even know the scripting language…

Twitter and Google Sheets: Best Friends

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:

  1. Repetitive stuff needs to be automated.
  2. The best way to learn to code is with a tangible, objective problem that needs a solution.
Apps Script. It’s like VBA, but good.

Discovering JavaScript

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 index, match, and vlookup. Generating arrays from simple — built-in — formulas, then, is a major advantage that Sheets has over Excel.

So I started tinkering around in Google Sheets for a few projects of mine. The only problem is that I couldn’t write any of the code to automate things in Sheets. After all, Sheets doesn’t use VBA, but rather it’s own scripting language — Google Apps Script (GAS) — which I quickly learned is basically JavaScript with added utilities for functions built into Sheets. The two are so similar that most of what I ended up finding on the internet was JavaScript resources. It should go without saying, I didn’t know JavaScript.

I took a look around at some reference sites for GAS / JavaScript and discovered it made a lot of sense to me; I just didn’t know the syntax. My way of solving that was to create something that required scripting and something that I would use. Going back to my lessons learned, I figured that I should find something repetitive to automate with some Google Scripts as a learning exercise.

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.

“If you have a garden and a library, you have everything you need.” — Cicero | Photo by Jorge Royan via Wikipedia Commons

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:

If you want to make a Twitter bot with GAS, or even JavaScript, it’s worth your time to look through this library to see how Twitter’s API is accessed. This library was a huge time saver and reading through it actually helped me understand a lot of the logic behind certain functions within JavaScript that otherwise confused me.

OAuth1 & OAuth2 are legacy additions, they are not necessary as the Twitter library has its own OAuth utility.


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

Twitter replies require the tweet and user ID to which a reply is responding

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:

  1. Responding to an inquiry. A user asks a question and the bot responds to the tweet with the answer.
  2. 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.

Adding Images

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 atry 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.uploadMedia — 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.

After using.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 params array.

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:

Each tweet is a new entry in our log sheet

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).

Finally, the 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.

A different kind of thread, but still… Photo by Janko Ferlič on Unsplash


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.

An 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).

Lastly, an 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.

  • twtMax is 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).
  • total establishes the total character count of our stringToTweet variable.
  • start is a placeholder for the starting character of the current section we’re parsing. This will make more sense when viewed in practice.
  • excess is the number of characters after the last space in a tweet-length section beginning at the start character of our stringToTweet variable. As we’ll see below, we trim the excess away from our 280 so that our tweets don’t cut words in half.
  • parsed is 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).

The 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 tweets array.

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-namedurl variable.

Now the magic happens. We establish our newTweet variable as a call of sendTweet passing our tweets text, 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.

The entire 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 sendTweet and 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.

Photo by Aaron Burden on Unsplash

Daily Readings

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:

  1. Using Google Sheet’s importXML function to pull in data from a website to an array of cells in a worksheet.
  2. Processing the data from those cells into usable text.
  3. Writing a script to use the data
  4. 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.

A screenshot of my OCAreadings tab

1. ImportXML

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 importXML function).

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:

=SUBSTITUTE(IFERROR(ImportXML(C2,"//h2//em"),"")," reading","")

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 ",
MATCH(A2, $A$2:$A$11,0),3),
&" "
"|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 dl & dd).

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.

My trigger for reading threads

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.

Prior to working on this project, I had no working knowledge of GAS or JavaScript. I made these functions by scouring the Twitter library and combining what I could see there with anything I could find on Stack Overflow or any other website that DuckDuckGo threw my way. I had a working knowledge of how functions work from my experience in VBA, but apart from general programming knowledge, I had no experience in this area.

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

  1. 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 sendTweet function. I’m slowly resolving these as they manifest.
  2. 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.
  3. In the event of a failure in the tweetThread function, the code simply ends with a break statement. I’d ultimately like to have an error catch that will essentially go back through each tweet in the thread already sent and .destroy it. 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.
  4. I’m always looking to improve. Please comment below with advice and constructive criticism. My primary goal was to learn some JavaScript, and that hasn’t changed just because I have this in production.
  5. 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: