I created a Twitter Bot from nothing but a Google Sheet

And I didn’t even know the scripting language…

Peter Berthelsen
Jan 22, 2019 · 24 min read
Image for post
Image for post
Twitter and Google Sheets: Best Friends

“laziness breeds innovation”

Image for post
Image for post
Apps Script. It’s like VBA, but good.

Discovering JavaScript

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

The Fun Begins!

Twitter GAS Library:
MKvHYYdYA4G5JJHj7hxIcoh8V4oX7X1M_
Image for post
Image for post
OAuth1 & OAuth2 are legacy additions, they are not necessary as the Twitter library has its own OAuth utility.

Image for post
Image for post

Access


Handling Tweet Replies

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

Adding Images

sendTweet("Here are some great memes!",0,"bigchungus.jpg,instaegg.png,");

Sending The Tweet

Image for post
Image for post
Each tweet is a new entry in our log sheet
Image for post
Image for post
A different kind of thread, but still… Photo by Janko Ferlič on Unsplash

Threads

Image for post
Image for post

The “Bot” Part

Daily Readings

Image for post
Image for post
A screenshot of my OCAreadings tab

1. ImportXML

=importxml("https://oca.org/readings","//div[@id='main-col-contents']//li")
=importxml("https://oca.org/readings","//div[@id='main-col-contents']//li//@href")
=IF(B4="","",CONCATENATE("https://oca.org",B4))
=SUBSTITUTE(IFERROR(ImportXML(C2,"//h2//em"),"")," reading","")

2. Processing The Data

=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),
""
)
),
""
)

3. Writing a Script to Use the Data

4. Creating a Trigger

Image for post
Image for post
Image for post
Image for post
My trigger for reading threads

One Last Thing

Image for post
Image for post


Things To Note


JavaScript In Plain English

New articles every day.