Send daily automated tweets from a Google Sheet by using Google Scripts

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:

• sendDailyTweet as 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.

Get my ebook on Amazon and Leanpub