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 RepliesTwitter replies require the tweet and user ID to which a reply is respondingNext, 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 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 ImagesTwitter 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.
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.
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 TweetNow 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 sheetIf 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 UnsplashThreadsOddly 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.
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” PartIf 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 UnsplashDaily ReadingsLike 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 importXML function 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 dataCreating 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 tab1.
ImportXMLThe 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):=importxml("https://oca.
org/readings","//div[@id='main-col-contents']//li")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:=importxml("https://oca.
org/readings","//div[@id='main-col-contents']//li//@href")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:=IF(B4="","",CONCATENATE("https://oca.
org",B4))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 DataThe 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:=IFERROR( IF( TRIM( ARRAYFORMULA( CONCATENATE( ImportXML( INDEX( $A$2:$C$11, MATCH(2,$A$2:$A$11,0), 3), "//dl//dd") &" ") ) )="", "", SUBSTITUTE( CONCATENATE( "Today's #Orthodox #DailyReading ", D2, " is ", A2, "(thread)|", TRIM( ARRAYFORMULA( CONCATENATE( ImportXML( INDEX( $A$2:$C$11, MATCH(A2, $A$2:$A$11,0),3), "//dl//dd") &" " ) ) ), "|Daily readings courtesy of @ocaorg ",C2), char(10), "" ) ), "")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.
Writing a Script to Use the DataNow, 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.
Creating a TriggerThe 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 threadsOne Last ThingIf 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 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 NoteI 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.
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 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 .
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.
I’m always looking to improve.
Please comment below with advice and constructive criticism.
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.
.. More details