方言を話すおしゃべり猫型ロボット『ミーア』をリリースしました(こちらをクリック)

[X(Twitter) bot] How to create an X Bot that automatically posts from a spreadsheet in GAS: (2) Text

x-bot-create-a-text-using-gas
This article can be read in about 23 minutes.

A reminder on how to create a Twitter bot in Google Apps Script (GAS) that automatically posts from a spreadsheet to Twitter via X (formerly Twitter) API v2.

In the previous article below, we finished the process of Twitter API authentication for creating a Twitter Bot. If you have not finished setting up yet, please do so from the following article.

Now, let’s actually proceed with the text and image submissions.

text post

First step: display text in GAS scripts

The first step is to embed the Twitter post text directly in the GAS script, not from the Google spreadsheet, to verify that it will be posted to twitter.

Changed the contents of the main function used in the previous Twitter API authentication as follows. Function name also changed to sentTweet() (not required)

The main() function here.

GDScript
function main() {
  const service = getService();
  if (service.hasAccess()) {
    Logger.log("Already authorized");
  } else {
    const authorizationUrl = service.getAuthorizationUrl();
    Logger.log('Open the following URL and re-run the script: %s', authorizationUrl);
  }
}

Extended to the following sendTweet() function

GDScript
function sendTweet() {
  var payload = {
    text: 'Test tweet from API!!!!!'
  }

  var service = getService();
  if (service.hasAccess()) {
    var url = `https://api.twitter.com/2/tweets`;
    var response = UrlFetchApp.fetch(url, {
      method: 'POST',
      'contentType': 'application/json',
      headers: {
        Authorization: 'Bearer ' + service.getAccessToken()
      },
      muteHttpExceptions: true,
      payload: JSON.stringify(payload)
    });
    var result = JSON.parse(response.getContentText());
    Logger.log(JSON.stringify(result, null, 2));
  } else {
    var authorizationUrl = service.getAuthorizationUrl();
    Logger.log('Open the following URL and re-run the script: %s',authorizationUrl);
  }
}

A variable called “payload” is prepared and the text “Test tweet from API!!!!!” is to be posted as a Twitter bot.

When finished, save the GAS script, change the execution function to sentTweet, and run it.

Then, the following appeared in the GAS execution log, and the Twitter account also displayed the tweet.

Tweet text from Google Spreadsheet

Now it’s time to get ready to TWEET the text from the Google Spreadsheet.

First, this time the script prepares the following columns in the spreadsheet

The character count is used to manage the character limit for Twitter posts and is not necessary for scripting purposes.
image_url is also used when you want to post images published on the web, so it is not necessary if you do not post images.

Put the appropriate text for a Twitter post in the “Post Content” column of column C.

Copy and paste the following GAS script

GDScript
// CLIENT_IDとCLIENT_SECRETはtwitter developerサイトから取得した値を用いる
const CLIENT_ID = 'XXXX'
const CLIENT_SECRET = 'XXXXX'


function getService() {
  pkceChallengeVerifier();
  const userProps = PropertiesService.getUserProperties();
  const scriptProps = PropertiesService.getScriptProperties();
  return OAuth2.createService('twitter')
    .setAuthorizationBaseUrl('https://twitter.com/i/oauth2/authorize')
    .setTokenUrl('https://api.twitter.com/2/oauth2/token?code_verifier=' + userProps.getProperty("code_verifier"))
    .setClientId(CLIENT_TEST_ID)
    .setClientSecret(CLIENT_TEST_SECRET)
    .setCallbackFunction('authCallback')
    .setPropertyStore(userProps)
    .setScope('users.read tweet.read tweet.write offline.access')
    .setParam('response_type', 'code')
    .setParam('code_challenge_method', 'S256')
    .setParam('code_challenge', userProps.getProperty("code_challenge"))
    .setTokenHeaders({
      'Authorization': 'Basic ' + Utilities.base64Encode(CLIENT_ID + ':' + CLIENT_SECRET),
      'Content-Type': 'application/x-www-form-urlencoded'
    })
}

function authCallback(request) {
  const service = getService();
  const authorized = service.handleCallback(request);
  if (authorized) {
    return HtmlService.createHtmlOutput('Success!');
  } else {
    return HtmlService.createHtmlOutput('Denied.');
  }
}

function pkceChallengeVerifier() {
  var userProps = PropertiesService.getUserProperties();
  if (!userProps.getProperty("code_verifier")) {
    var verifier = "";
    var possible = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-._~";

    for (var i = 0; i < 128; i++) {
      verifier += possible.charAt(Math.floor(Math.random() * possible.length));
    }

    var sha256Hash = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, verifier)

    var challenge = Utilities.base64Encode(sha256Hash)
      .replace(/\+/g, '-')
      .replace(/\//g, '_')
      .replace(/=+$/, '')
    userProps.setProperty("code_verifier", verifier)
    userProps.setProperty("code_challenge", challenge)
  }
}

function logRedirectUri() {
  var service = getService();
  Logger.log(service.getRedirectUri());
}

function main() {
  const service = getService();
  if (service.hasAccess()) {
    Logger.log("Already authorized");
  } else {
    const authorizationUrl = service.getAuthorizationUrl();
    Logger.log('Open the following URL and re-run the script: %s', authorizationUrl);
  }
}



function sendTweet() {
  var tweetData = pickUpTweet("twitter_text"); // ツイートの内容を取得
  var payload = { text: tweetData }; 
  console.log(payload);
  
  var service = getService();
  if (service.hasAccess()) {
    var url = `https://api.twitter.com/2/tweets`;
    var response = UrlFetchApp.fetch(url, {
      method: 'POST',
      'contentType': 'application/json',
      headers: {
        Authorization: 'Bearer ' + service.getAccessToken()
      },
      muteHttpExceptions: true,
      payload: JSON.stringify(payload)
    });
    var result = JSON.parse(response.getContentText());
    Logger.log(JSON.stringify(result, null, 2));
  } else {
    var authorizationUrl = service.getAuthorizationUrl();
    Logger.log('Open the following URL and re-run the script: %s',authorizationUrl);
  }
}



function pickUpTweet(sheetName) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const lastRow = sheet.getLastRow();
  const data = sheet.getRange("A1:F" + lastRow).getValues();

  const availableTweets = data.filter(function(value) {
    return value[1] == '';
  });

  if (availableTweets.length == 0) return { text: '', imageUrl: '' };

  const weights = availableTweets.map(function(value) {
    return value[4]; // Assuming the weight is in column E
  });

  const alias = new AliasMethod(weights);
  const selectedIndex = alias.next();

  const selectedRow = availableTweets[selectedIndex][0];
  Logger.log("Selected row: " + selectedRow); // Log the selected row number

  sheet.getRange(selectedRow + 1, 2).setValue(new Date());

  return availableTweets[selectedIndex][2];
}


class AliasMethod {
  constructor(weights) {
    this.prob = [];
    this.alias = [];
    this.n = weights.length;

    const small = [];
    const large = [];
    const scaledWeights = weights.map((w) => w * this.n);

    for (let i = 0; i < this.n; i++) {
      if (scaledWeights[i] < 1) {
        small.push(i);
      } else {
        large.push(i);
      }
    }

    while (small.length > 0 && large.length > 0) {
      const l = small.pop();
      const g = large.pop();

      this.prob[l] = scaledWeights[l];
      this.alias[l] = g;

      scaledWeights[g] = scaledWeights[g] + scaledWeights[l] - 1;

      if (scaledWeights[g] < 1) {
        small.push(g);
      } else {
        large.push(g);
      }
    }

    while (large.length > 0) {
      const g = large.pop();
      this.prob[g] = 1;
    }

    while (small.length > 0) {
      const l = small.pop();
      this.prob[l] = 1;
    }
  }

  next() {
    const i = Math.floor(Math.random() * this.n);
    return Math.random() < this.prob[i] ? i : this.alias[i];
  }
}

When this code was executed, the following was displayed in the execution log and was successfully tweeted.

Also, if you look at the spreadsheet, the date and time of posting is inserted in column B.

Text Submission Algorithm Explained

Brief explanation of the GAS script algorithm

  • Among the contents listed in the contents of the postings in column C, those postings that do not have a date in column B (date and time of posting) and have a high value in column D (weight) will be extracted and posted with priority.
  • When a post is posted to Twitter, the date and time of the post will appear in column B at that time, and will not be posted in the future unless the date in column B is deleted (to prevent duplicate posting of previously posted content).
  • Any integer number can be entered in column D (weight).

Countermeasure for GAS timeout error regarding priority submission

GAS can only be run for a maximum of 6 minutes at a time, and if it exceeds 6 minutes, it will result in a timeout error. So, if you want to weight and prioritize the posting frequency according to the posting content, and you want to extract one from thousands of texts on a spreadsheet, the usual weighting algorithm (cumulative sum) will result in a timeout error.

In this case, the above measure is used to execute Walker’s Alias Method in the AliasMethod class of the GAS script. In this case, it can be done with O(n) for table construction of preparation and O(1) for random selection of submitted text. Incidentally, no timeout error occurred even when the method was executed with 3000 lines of text.

If you would like to know more about Walker’s Alias Method, please refer to the following article, which was easy to understand.

Walker's Alias Methodの箱の作り方のわかりやすい説明 - Qiita
はじめに指定された重みに従って離散的な値を確率的に選択したい、ということがよくある。例えばという配列が与えられた時、確率10%で0、40%で1、50%で2というインデックスを返すよう…

The main weighted sampling algorithms are cumulative sum, binary tree, and Walker’s Alias, each of which requires the following table construction, search, and partial update effort. In this case, Walker’s Alias is considered suitable when the number of lines of text to be extracted increases to several thousand and so on, since the weights do not change each time the sampling is done.

table constructionsearchpartial update
cumulative sumO(N)O(log N)O(N)
Walker’s AliasO(N)O(1)O(N)
binary treeO(N)O(log N)O(log N)

Click here for a detailed article comparing each algorithm.

重み付きランダムサンプリングアルゴリズム - Qiita
重み付きランダムサンプリングアルゴリズムはじめにあらかじめ指定された重みに従って離散的な値を確率的に選択したい、ということがよくある。例えばという配列が与えられたら、確率10%で0…

Postable content: text, links, tags, Twitter account, images of past posts

Items that can be submitted with this GAS script are

Text
Link
Tags
Twitter account mentions
Images of past Twitter posts

five main ones.

For example, if you enter the link, tags, and Twitter account (@tesu35416661269 in this case) in the post content field and run GAS, the tweet will appear as shown below.

Image Submission

In this issue, we will introduce the following two patterns regarding image submissions.

Re-posting images previously posted on Twitter

Post images that have been made publicly available on the web (publicly accessible images)

You want to re-post images that you have posted in the past on Twitter by incorporating them into the bot.

Click on the three-dot leader symbol in the upper right corner of a post with an image, then click on “Embed Post”. However, “Embed Post” will not appear unless the account is public (unlocked).

Then, a new tab will open and you will be redirected to the following screen.

The content of the copied code is as follows

GDScript
<blockquote class="twitter-tweet"><p lang="ja" dir="ltr">サーボモーター比較 <a href="https://t.co/vc3RfQmtsC">pic.twitter.com/vc3RfQmtsC</a></p>— てす (@tesu35416661269) <a href="https://twitter.com/tesu35416661269/status/1703560901708890465?ref_src=twsrc%5Etfw">September 18, 2023</a></blockquote> <script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>


The link starting with pic.twitter.com is the part that contains the image information. In this case, pic.twitter.com/vc3RfQmtsC, and if you actually open it, you will see that the Twitter post image is displayed. The content of the link also contains text, but if you describe it in the spreadsheet and not post it, it will be an image-only post.

When this link was included in the posting content section of the spreadsheet as in the case of a normal link, and GAS was executed, the image was successfully posted.

Posting images published on the Web

Prepare a column named “image_url” in column E of the spreadsheet with the publicly accessible image URLs.

This time, test with the following image URL
https://kazulog.fun/wp-content/uploads/2023/08/dc-servo.jpg

However, currently, Twitter API v2 does not have an image upload function and requires Twitter API ver1.1, and OAuth2 only supports API v2, so a new Oauth1 library must also be installed, etc. It is complicated. See this article.

コメント

Copied title and URL