Google Apps Script to Post Tweets to Twitter from Google Sheet

AutoTweeter.gs

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);
  }


}


function clearScriptProperties() {
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.deleteAllProperties();
  Logger.log('All script properties have been cleared.');
}

function initProperties()
{
  const scriptProperties = PropertiesService.getScriptProperties();
  let twitterAccessToken = scriptProperties.getProperty("ACCESS_TOKEN");
  let twitterRefreshToken = scriptProperties.getProperty("REFRESH_TOKEN");
  let twitterAccessTokenExpiryDateStr = scriptProperties.getProperty("EXPIRES_AT");

  if (twitterAccessToken == null || twitterAccessToken=='')
  {
    twitterAccessToken = TWITTER_BEARER_TOKEN;
    scriptProperties.setProperty('ACCESS_TOKEN',twitterAccessToken);
  }

  if (twitterRefreshToken == null || twitterRefreshToken=='')
  {
    twitterRefreshToken = TWITTER_REFRESH_TOKEN;
    scriptProperties.setProperty('REFRESH_TOKEN',twitterRefreshToken);
  }
  
  if (twitterAccessTokenExpiryDateStr == null || twitterAccessTokenExpiryDateStr=='')
  {
    twitterAccessTokenExpiryDateStr = TWITTER_EXPIRES_AT;
    scriptProperties.setProperty('EXPIRES_AT',twitterAccessTokenExpiryDateStr);
  }
 
}

function getTokens()
{
  const scriptProperties = PropertiesService.getScriptProperties();
  const twitterAccessToken = scriptProperties.getProperty("ACCESS_TOKEN");
  const twitterRefreshToken = scriptProperties.getProperty("REFRESH_TOKEN");
  const twitterAccessTokenExpiryDateStr = scriptProperties.getProperty("EXPIRES_AT");
  twitterAccessTokenExpiryDate = new Date(twitterAccessTokenExpiryDateStr);

  return {
    accessToken: twitterAccessToken,
    refreshToken: twitterRefreshToken,
    expiresAt: twitterAccessTokenExpiryDate
  };
}

function refreshAndGetTokens()
{
  const tokenObj = getTokens();
  var currentDate = new Date();
  if (currentDate > tokenObj.expiresAt)
  {
    //need to refresh our token
    const response = refreshToken(tokenObj.refreshToken);
    if (response != null)
    {
      const newAccessToken = response.access_token;
      const newRefreshToken = response.refresh_token;
      const expiresIn = response.expires_in;

      const newExpiryDate = new Date();
      newExpiryDate.setSeconds(currentDate.getSeconds()+expiresIn);
      //now we save these into our script properties
      const scriptProperties = PropertiesService.getScriptProperties();
      scriptProperties.setProperty('ACCESS_TOKEN',newAccessToken);
      scriptProperties.setProperty('REFRESH_TOKEN',newRefreshToken);
      scriptProperties.setProperty('EXPIRES_AT',newExpiryDate.toISOString());
      return getTokens();
    }
    else
    {
      return null;
    }
  }
  else
  {
    return tokenObj;
  }

}


function refreshToken(refreshToken)
{
  var url = 'https://api.twitter.com/2/oauth2/token';
  var data = {
    "refresh_token":refreshToken,
    "grant_type":"refresh_token",
    "client_id":TWITTER_API_KEY
  };

  //Need to create base64 encoded header
  const unencodedAuthorizationHeader = TWITTER_API_KEY+':'+TWITTER_API_SECRET_KEY;
  const basicAuthorizationHeader = Utilities.base64Encode(unencodedAuthorizationHeader);

  // Convert the data to URL-encoded format
  var payload = [];
  for (var key in data) {
    payload.push(encodeURIComponent(key) + '=' + encodeURIComponent(data[key]));
  }
  var postData = payload.join('&');
   var options = {
    method: 'post',
    contentType: 'application/x-www-form-urlencoded',
    payload: postData,
    headers:
    {
      Authorization: 'Basic ' + basicAuthorizationHeader
    },
    muteHttpExceptions: true
  };
  var response = UrlFetchApp.fetch(url, options);
  if (response.getResponseCode()==200 || response.getResponseCode()==201)
  {
    const retVal = JSON.parse(response.getContentText());
    Logger.log("Successfully refreshed access token: '"+retVal+"'");
    return retVal;
  }
  else
  {
    Logger.log("ERROR: Unable to refresh access token.");
    Logger.log('Response code:' + response.getResponseCode());
    Logger.log('Response body:' + response.getContentText());
    return null;
  }

}

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;
        }
        

}