Check out my blog for more articles or Github for my free-to-read JavaScript Ebook that covers all the new features from ES6 to 2019
My girlfriend runs a Vietnamese language blog (which you should definitely check out, it's static site built with Gatsby that she designed and I built) and I wanted to help her be able to start a Word of the day project for twitter.
Having to go to Twitter every day just to post a new word is not ideal, repetitive tasks are the worst, they are boring and take up time that you could spend doing something else.
That's why I set up a simple Google Script to post a new tweet everyday from a Google Sheet. The whole process took me less than 2 hours with zero prior knowledge of how to use Google Scripts (which is very easy to use if you are familiar with JavaScript).
At the end of this tutorial you will know how to create a script to get rows from a Google Sheet and send them as tweets.
You can get the complete code for the script here
Firstly, before we start we will need a twitter developer account, to do that follow this link.
Once you apply it will take roughly a few hours to get approved.
What you want to do after that is create a new app and save both the Consumer API keys and the Access token & access token secret. Remember to not share them with anyone!
While your application is still pending approval from Twitter let's start creating our Google Sheet:
It's a simple sheet with 4 column: 3 for the content and 1 for the date that will be used to post that tweet.
Now, inside of Google Sheet click on Tools > Script editor and a new window will open where you can write your Google Script.
Before we start writing our code, let's import the Twitter library that we'll need. To do that you need to click Resources > Libraries and paste this code inside of the Add library field: MKvHYYdYA4G5JJHj7hxIcoh8V4oX7X1M_.
Before you save, rename the identifier of the library to Twitter, just so we can call methods on Twitter. instead of Twitterlib., not necessary but just a personal preference.
Writing the Google Script
Now it's finally time to write our script which we can break down in two parts:
- parse the Google Sheet content, get the correct row and construct our tweet
- authenticate Twitter and tweet our post
My Google Sheet is structured so that I will be able to tweet a different Word of the day everyday.
The first thing I need to do is to parse the Google Sheet content and find the correct row to post today.
Let's start by getting our sheet like this:
function sendDailyTweet() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var startRowNumber = 1;
var endRowNumber = sheet.getLastRow();
}
Now what we want to do is to loop over all our rows and see if the date of that row corresponds to today.
Our function now will look like this:
function sendDailyTweet() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var startRowNumber = 1;
var endRowNumber = sheet.getLastRow();
// variables to create our tweet
var vietnameseWord;
var englishWord;
var sentenceExample;
var identifier;
for (var currentRowNumber = startRowNumber; currentRowNumber <= endRowNumber; currentRowNumber++) {
var row = sheet.getRange(currentRowNumber + ":" + currentRowNumber)
.getValues();
// check that the fourth column (Date) is equal to today
if (isToday(row[0][3])) {
console.log(row);
break;
}
}
}
What we are doing now is loop over each row and get the content of it with sheet.getRange(currentRowNumber + ":" + currentRowNumber).getValues();.
What we get is a an Array containing an Array of values, one per column.
Since the date was stored in the 4th column, I'm getting it like this: row[0][3] and passing it as an argument to the isToday function that we can define outside of our primary function as:
function isToday(date) {
var today = new Date();
var dateFromRow = new Date(date);
return dateFromRow.getDate() == today.getDate() &&
dateFromRow.getMonth() == today.getMonth() &&
dateFromRow.getFullYear() == today.getFullYear()
}
This function is very simple and will help us compare the current date with the one stored in that particular row. If they match it will return true
If you try now to run your code everything should work fine and you can see the output of the console.log by clicking on the menu View > Stackdriver Logging.
Now, replace that console.log with the following lines:
vietnameseWord = row[0][0];
englishWord = row[0][1];
sentenceExample = row[0][2];
identifier = currentRowNumber -1;
We are simply assigning values to all the variables that we need based on the corresponding column.
Hopefully by now Twitter already approved your request so you can continue implementing the final part of this code.
After you create a new app on https://developer.twitter.com/ define a new Object inside of your sendDailyTweet function and replace my placeholders with your keys:
var twitterKeys = {
TWITTER_CONSUMER_KEY: "[your_key_here]",
TWITTER_CONSUMER_SECRET: "[your_key_here]",
TWITTER_ACCESS_TOKEN: "[your_key_here]",
TWITTER_ACCESS_SECRET: "[your_key_here]",
}
Next, right under the Object let's store those properties like this:
var props = PropertiesService.getScriptProperties();
props.setProperties(twitterKeys);
// we don't really need params for this example so we'll leave them empty
var params = new Array(0);
var service = new Twitter.OAuth(props);
See this: new Twitter.OAuth(props);, we are calling Twitter. because we renamed the identifier of the library to Twitter, otherwise it would have been Twitterlib..
Our final step will be to authenticate and finally send the tweet.
Right after our for loop, let's add this if...else:
if (!service.hasAccess()) {
console.log("Authentication Failed");
} else {
console.log("Authentication Successful");
}
Try running your code now, if the credentials you passed in props.setProperties(twitterKeys); are correct, you shouldn't be having any problem.
Inside of the else statement we will construct our tweet and send it:
var status = "Vietnamese Word #" + identifier + "\n\n" +
vietnameseWord + " - " + englishWord + "\n\n" + "E.g: " +
sentenceExample + "\n\n" +
"Comment with your sentence. Learn more @ elingos.com" +
"\n\n" +
"#wordoftheday #languagelearning #learnvietnamese #vietnameselanguage #elingosvietnamese";
try {
var response = service.sendTweet(status, params);
console.log(response);
} catch (e) {
console.log(e)
}
We are using the variables we defined above to create the status for our tweet.
Inside of our try...catch we try to send our tweet with service.sendTweet(status, params);, otherwise we will see the error logged in our console.
Awesome, we are done with Google Script, the only thing missing here is a trigger to make our script run everyday.
To do that we don't have to write a single line of code, from the menu click Edit > Current project's trigger and a new tab will open.
Click the button Add trigger in the bottom corner
These are the settings that I've chosen for my script:
-
sendDailyTweetas the function to run - time-driven as the event source
- day timer - 1am to 2am as the time frame
This script will run everyday and post a new tweet on the account twitter for Elingos. If you like languages, real ones and not just programming ones, and you want to learn a new one, check out her blog at Elingos.com.
You can get the complete code for the script here.
If you want to learn even an even more complex script, check out this great article on Medium.
Thank you very much for reading. Follow me on DevTo or on my blog at inspiredwebdev for more.
Top comments (24)
Hi Alberto, just another question. I want to create more than one tweet per day. One of the regular tweets will be a countdown until an event.
I could now do the same with another Google Spreadsheet but I would love to use the same document but another tab.
I checked the google information and I miss the idea how I could create an function that refers to an tab2 instead only tooks the first.
So I also don't understand what is an "active" sheet and what is not. Do you have any idea?
Next Challenge solved. Different tweets from the same source with different content. Works fine.
I'm really excited.
Awesome, what's your twitter profile?
the Google Sheet solution is working for "SaMDExpert"
It's a very new account and with an direct purpose to support Software as a Medical Device Industry, StartUps and NGOs products.
So, direct after I wrote the question I guess I found it. :)
developers.google.com/apps-script/...
So now I have to make some tests. :)
Awesome!
Done. Works. :) Is just not as complicated as thought. I love it.
@AngeloM Thank you for the inspiration.
How I have done it. Just created a second sheet and copied the original Script. I change the first rows to:
function sendDailyTweet2() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
SpreadsheetApp.setActiveSheet(sheet.getSheets()[1]);
....
Awesome, if you want to go further you can try adding images to your tweets.
I'm familiar with Markup but Dev.to is using something different so I didnt spend so much time for that.
But of course, I'll do it as soon as I figured out how it works. :)
Hi Alberto, I like that Tutorial. Thank you very much for the work.
I just enabled the Google Dev Account and I added the trigger. Unfortunately I cant choos the deployment "Head". It's just showing "Test Environement".
I guess because the account is not yet confirmed. Am I right?
I'm not sure but I read this on the documentation:
Note: There is only ever one head deployment for each Apps Script project. In order to use a head deployment the user invoking the script must have at least read access to the script project.developers.google.com/apps-script/...Interesting. Is my project, my app and my script. .... Hm, I will check that.
Let me know if you find out the reason so I can add a note to the tutorial
Of course, I will do it.
Today, I checked it and it doesn't worked out. I will give you more details when I checked the log and so on.
Alberto, the code ran today and created an error. But the error is nothing with the trigger.
It's just that I missed to initialize the "identifier".
Now it's done and lets see how it works. We keep in touch. :)
Awesome, something I forgot to implement was a check if "today" there's a tweet to post. You might want to put the code to send a tweet inside of an "if else".
Good point. I take it to my list of future improvements.
So I checked the googletwitterbot and now it works perfect! Thank you very much. '
I have the google sheet and can put in advance the content inside and can be sure that on the proper day the content is online.
Great is, that I can in advance create twitter reminders for any deadline and I can remind follower about the reaching end of a period.
Hello Alberto,
After a while now, I changed the bot a little bit. But not in the code but only in the table.
But now the automatic executions do not work anymore and it asks for a property.
Do you have an idea what I could do?
Nice one!!
I was thinking about building something similar, but then I discovered TweetDeck!
Looks cool but in this case I just wanted something simple that I could do by myself. If i needed something more complicate I would definitely use a tool like that one.
Very cool stuff information shared! it's nicely alternate to Tweet Deck, but there on Tweet Deck you need to automate manually, but with your script can deploy automatically, it's really time saver.
Wow Javascript
I need to get the row for today so I still need to loop over the rows to get the right one, no? Did you have something else in mind?
Is there a way to edit this script so you can use it with essential access?