Are You Using AI In Your Job?

We want to understand the real-world applications of AL and ML in business and the impact it will have on all our jobs.

Want to help? Complete the survey, your insights could make a big difference. It will just take one minute.
You'll be the first to get access to the final report.-->

How to Create a Twitter Bot in Google Sheets Using a Google Apps Script

Bobby Gill | August 3, 2023

In this post, I will outline how to create a very simple Twitter bot (or is it now an X-bot?) that automatically posts pre-written Twitter content from a Google Sheet to a specific Twitter feed once a day with no user intervention.

This example uses OAuth2 and Twitter’s API v2 endpoint for managing Tweets, it is written as a Google Apps Script attached to the Google Sheet. Google Apps Script is a derivative of Javascript, so if you are comfortable JS you will feel right at home with Google Action Script.

The full source code for this example can be found here.

Goal: Simple, Easy, Automated Tweets

The sheet is very simple, Column A will be the list of Tweets I would like to automate, Column B will be a boolean flag that the script will use to track whether or not it has issued that Tweet. Whenever the script runs, it will randomly select a row in my Sheet, post the Tweet and then flip the value in Column B to “TRUE”.

Prerequisites

  • You will need a Twitter developer account, you can use a free account for the purposes of this example.
  • You will need a tool to be able to complete an OAuth 2.0 PKCE authorization code flow to generate an access token/refresh token pair the sheet will need. I choose to use Postman which makes it super simple to complete an OAuth 2.0 authorization code flow.
  • Create a new Google Sheet that has a 2-column structure as shown in the screenshot above.

1) Create a Twitter App

You will need to setup a new Twitter app in the Twitter Developer Portal. Ensure that in the “User Authentication Settings” you select Read & Write app permissions.

You will need some mechanism to complete the OAuth authorization code w/ PKCE the first time to generate an initial access token and refresh token set. I chose to use Postman to generate an access token and refresh token pair that I used to seed my Google Action Script with. If you are going to use Postman for this, then make sure to add “https://oauth.pstmn.io/v1/callback” as a Callback URI.

Under Keys and Tokens, you will need to take note of the OAuth 2.0 Client ID and Client Secret which will be needed later.

2) Complete OAuth 2.0 Authorization Code Flow in Postman

In order for our Google sheet to post to Twitter, we need to generate a OAuth bearer token that has access to my Twitter feed. To do this you need to complete the OAuth 2.0 w/ PKCE flow. Once you have gone through this flow once, you are returned a refresh token which allows your code to automatically refresh the access token anytime it expires from within the Google Action Script.

To generate the initial access and refresh token pair create a new Postman request and under the Authorization tab configure it with the following options:

  • Type: OAuth 2.0
  • Add auth data to: Request headers
  • Header Prefix: Bearer
  • Grant Type: Authorization Code (with PKCE)
  • Callback URL: select “Authorize using browser” which will preset the callback URI to “https://oauth.pstman.io/v1/callback”
  • Auth URL: https://twitter.com/i/oauth2/authorize
  • Access Token URL: https://api.twitter.com/2/oauth2/token
  • Client ID: <OAuth 2.0 Client ID obtained from your Twitter app setup>
  • Client Secret: <OAuth 2.0 Client Secret obtained from your Twitter app setup>
  • Code Challenge Method: Plain
  • Scope: tweet.write tweet.read users.read offline.access
  • State: <input any randomly generated string less than 30 characters>
  • Client Authentication: Send as Basic Auth header

Press “Get New Access Token”, this will open your browser and ask you to login to Twitter and authorize your Twitter app to access your account. Click “Authorize”.

This will redirect back to Postman where it should capture and display the new ‘access token’, ‘refresh token’ and ‘expires in’ in a dialog. Copy these values as we will need them later.

3) Setup Google Action Script to Post to Twitter

In the Google Sheet, go to Extensions → “Apps Script”, which will open up a code editor window. Since we’ve already completed the OAuth 2.0 w/PKCE flow in Postman, there is no need for any external script library to implement our logic.

The main logic of our script exists within the method named generateTweet(), the code for this:

var TWITTER_API_KEY = '<Twitter OAuth 2.0 Client ID>';
var TWITTER_API_SECRET_KEY = '<Twitter OAuth 2.0 Client Secret>';
var TWITTER_BEARER_TOKEN ='<Access token returned in Postman>'
var TWITTER_REFRESH_TOKEN = '<Refresh token returned in Postman>'
var TWITTER_EXPIRES_AT= new Date('<Expiry date based on the expires_in returned from Postman>');


function generateTweet()
{
  
  initProperties();
  const twitterTokens = refreshAndGetTokens();
  if (twitterTokens == null)
  {
    Logger.log('ERROR: Did not receive Twitter tokens, aborting execution.');
    return;
  }


  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  
  // Check if there are any rows to process
  if (lastRow < 1) return;

  // Get all values iN Column A, the tweets
  var tweetRange = sheet.getRange(2,1,lastRow,1);
  var tweetValues = tweetRange.getValues();

  // Get all values in Column B
  var range = sheet.getRange(2, 2, lastRow, 1);
  var values = range.getValues();

  // Filter rows where Column B is a boolean value
  var unpostedTweets = [];
  for (var i = 0; i < values.length; i++) {
    if (values[i][0] == false) {
      unpostedTweets.push(i);
    }
  }

   // If no rows have false values, exit
  if (unpostedTweets.length === 0) return;

  // Randomly select one of the boolean rows
  var randomIndex = unpostedTweets[Math.floor(Math.random() * unpostedTweets.length)];
  var selectedTweet = tweetValues[randomIndex][0];

  Logger.log('Selected row number '+randomIndex+ ', Tweet: '+selectedTweet);

  //now we tweet it
  var didSucceed = sendTweetToTwitter(selectedTweet, twitterTokens.accessToken);

  if (didSucceed) {
    // Toggle the boolean value
    values[randomIndex][0] = true;

    // Update the sheet with the new value
    range.setValues(values);
  }


}

Take note you will need to paste the initial values for the ‘access token’, ‘refresh token’, ‘expires_in’ in the constants at the top of the file. Those are only used once to initially populate the state within the ScriptProperties. After the first execution, the most up to date values for these variables will be read and written to ScriptProperties only.

Our script logic is very simple and contained within the generateTweet() method:

  • It extracts the last known access token and refresh token from the Google sheet’s ScriptProperties collection.
    • If the access token is found to have expired, it will make a call to the Twitter OAuth endpoint to issue a new access token with the refresh token.
    • It saves the updated access token and refresh token back into the ScriptProperties dictionary.
  • The script then identifies all rows in the sheet where Column B is “FALSE”. It then randomly selects one of those rows.
  • The script posts the Tweet to the Twitter feed using a UrlFetch call to the Manage Tweets v2 endpoint. The logic for that is in the sendTweetToTwitter() method.
  • Finally when the Tweet is successfully posted, the script then marks Column B for the selected row to “TRUE”.
function sendTweetToTwitter(message, accessToken) {
        var url = 'https://api.twitter.com/2/tweets';
        var payload = {
            "text": message
        };
        var options = {
            method: 'POST',
            contentType: 'application/json',
            payload: JSON.stringify(payload),
            headers: {
                Authorization: 'Bearer ' + accessToken
            },
            muteHttpExceptions: true
        };

        var response = UrlFetchApp.fetch(url, options);
        if (response.getResponseCode()==200 || response.getResponseCode()==201)
        {
          Logger.log("Successfully posted Tweet: '"+message+"'");
          return true;
        }
        else
        {
          Logger.log("ERROR: Unable to post tweet: '"+message+"'");
          Logger.log('Response code:' + response.getResponseCode());
          Logger.log('Response body:' + response.getContentText());
          return false;
        }
        

}

4.) Create a Timed-Trigger to Execute Script Once Per Day

In order for the script to automatically trigger and post a Tweet once per day, you then need to setup a Trigger to call the script.

In the left menu of the Apps Script editor, click on Triggers (icon of an Alarm Clock). Then click on “+ Add Trigger”.

In the dialog box select the following options:

  • Choose which function to run: generateTweet
  • Select event source: Time-driven
  • Select type of time based trigger: Day timer
  • Select time of day: 9pm to 10pm

Then press “Save”.

Sit back and watch the Tweets Flow!

That’s it! Now you can drop in any number of Tweets you want to have scheduled into the Google Sheet and the script we created will execute once per day and post a Tweet.

You can find the full source code for this example here.

Bobby Gill
Co-Founder & Chief Architect at BlueLabel | + posts

Get the latest from the BlueLabel’s blog in your inbox

Subscribe

* indicates required