🕊️
【ChatGPTに訊く】GASを使用してグーグルスプレッドシートのデータから定期的にポスト(ツイート)する方法
はじめに
以前、X（旧Twitter）でゲームソフトの記念日をポストするbotをPHPを使って作りました。
その後、TwitterからXに変わったり、APIv2が公開されたり、色々ありましたが、ふと気付いたら使ってたライブラリが機能しなくなっていました。
原因究明～改修せねばと思いつつも放置を続け2年…ようやく重い腰を上げ、ChatGPTの力を借りながらbot復活に動きます。
今回の記事の内容
タイトルのとおりGAS（GoogleAppScript）の内容も含まれますが、ChatGPTへ投げた指示をそのまま貼りますので、技術的記事ではなく、どんなことをChatGPTへ聞いてコードを完成させたかの側面が強くなっています。
GASやXのAPIv2の使い方を知りたい方は事前準備の項で紹介している別記事をご確認ください。
叶えたいこと
GASを使用して以下の点を実現します。
- 任天堂製品の発売日をまとめたグーグルスプレッドシートからその日に発売されたソフトの情報を取得したい
- 取得した情報をそのソフトの発売日の朝6時に定期的にポストしたい
- 実行履歴を残し、実行エラーかどうか、成功している場合は何をポストしたのかを後から確認できるようにしたい
連係イメージ（アーキテクチャ図）
以前は自作アプリの中で定期的にSQLを実行し、取得結果をPHPを使用して自動ポストしていました。
今回は手動でデータベースからデータ抽出を行う過程を挟み、自動部はGoogleAppScriptを使用します。スプレッドシートの最新化は必要になりますが、GASを使ってみたかった、アプリの稼働状況に左右されない、今後アプリを停止したとしてもスプレッドシートさえメンテ続ければ大丈夫、などのメリットからこの形式としています。
事前準備
既存アカウントのDeveloper権限の取得、APIv2に必要なパラメータなどは以下の記事を基にします。これで、今回の要件の8割は完成です。
1つ目の記事の初回認証用のメソッドの項に記載のとおり、表示されたURLへアクセスし、画面に従って初回の認証を済ませると以下のような結果が返ってきます。
これで認証の準備は完了です。後は、ポストの内容を準備します。
ポスト内容の作成
ポスト候補データ
運営しているアプリで使用しているデータベースから今回の対象となるデータを取得します。
SQL実行ツールでこんな一覧をエクセルに吐き出し、コピペでスプレッドシートへ移行します。
コーディング
事前準備でできたものは単に指定された文字列をポストする部分だけです。今回は、ポスト候補データから今日発売かつ5年ごとの節目にあたるソフトの一覧を取得する必要があります。
取得した内容を事前準備で作成したポスト用のファンクションへ渡すことで、叶えたいことを実現します。
ということで、ChatGPTに聞きます。
ChatGPTへの指示
以下のような表があります。release_dateがA1セルです。
この表からrelease_dateが条件に合致する行の情報を取得するGASを作成してください。
GASにて作成された文字列をポストする際に使用する予定です。
# 条件
- release_dateが今日の日付であること。例えば、今日が4月29日であれば、年を問わず、4/29の情報を取得します。
- 取得した中から今日の年からrelease_dateの年を減算し、その結果が1もしくは5で割り切れる行のみ残します。
- 残った行の情報を「ツイート文字列」に当てはめます。
- ツイート文字列を呼び出し元へ返却します。
# ツイート文字列
{release_date}:{name}({platform}) {今日の年-release_dateの年}周年 #{name} {url}
# 表
| release_date | name | platform | url |
| ------------ | -------------------------------- | --------- | ------------------------------------------------------------------------------------------------------------------------------------------------ |
| 1996/01/01 | BSゼルダの伝説 MAP2 第2話 | SFCSV | |
| 1998/01/01 | レッキングクルー'98 | SFC/SFCNP | [https://www.nintendo.co.jp/n02/shvc/bwcj/index.html](https://www.nintendo.co.jp/n02/shvc/bwcj/index.html) |
| 1999/01/01 | POWER倉庫番 | SFCNP | [https://www.nintendo.co.jp/n02/shvc/bpsj/index.html](https://www.nintendo.co.jp/n02/shvc/bpsj/index.html) |
| 1999/01/01 | POWERロードランナー | SFCNP | [https://www.nintendo.co.jp/n02/shvc/bplj/index.html](https://www.nintendo.co.jp/n02/shvc/bplj/index.html) |
| 1996/01/03 | BSゼルダの伝説 MAP2 第3話 | SFCSV | |
ChatGPTからの回答
ChatGPTからの回答
function getAnniversaryTweets() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const data = sheet.getDataRange().getValues();
const header = data[0];
const releaseDateIndex = header.indexOf("release_date");
const nameIndex = header.indexOf("name");
const platformIndex = header.indexOf("platform");
const urlIndex = header.indexOf("url");
const today = new Date();
const todayMonth = today.getMonth(); // 0-based
const todayDate = today.getDate();
const thisYear = today.getFullYear();
const tweets = [];
for (let i = 1; i < data.length; i++) {
const row = data[i];
const releaseDate = new Date(row[releaseDateIndex]);
if (releaseDate.getMonth() === todayMonth && releaseDate.getDate() === todayDate) {
const releaseYear = releaseDate.getFullYear();
const diff = thisYear - releaseYear;
if (diff > 0 && (diff % 1 === 0 || diff % 5 === 0)) {
const name = row[nameIndex];
const platform = row[platformIndex];
const url = row[urlIndex] || "";
const tweet = `${formatDate(releaseDate)}:${name}(${platform}) ${diff}周年 #${name} ${url}`;
tweets.push(tweet);
}
}
}
return tweets;
}
// YYYY/MM/DD形式に整形
function formatDate(date) {
const yyyy = date.getFullYear();
const mm = ('0' + (date.getMonth() + 1)).slice(-2);
const dd = ('0' + date.getDate()).slice(-2);
return `${yyyy}/${mm}/${dd}`;
}
指示のポイント
1点目、2点目はもはや当たり前で、ポイントでもありませんが、念のため。
- 表はマークダウンへ変換して使用
プロンプト次第ではエクセルやスプレッドシートのコピー内容をそのまま使用できると思いますが、私はマークダウンへ変換して与えています。 - 指示や参考情報などをマークダウンを使用して指示
見出し、リストなどを使用してどれがインプット、どれが指示などを示してあげます。 - 丁寧に接する
「ホントかよ…」と思うかもしれませんが、丁寧に接した方が回答の精度が上がる噂もあり、丁寧に接することによる損も特にありませんので、日常で人を相手にする時と同じ接し方をしています。
- 多少曖昧でもなんとかなる
プロンプトエンジニアという職種もあるくらい、AIを完璧に使いこなすのは難しいです。
指示が多少曖昧でもAIはちゃんと文章の要点をまとめて回答をしてくれます。コード生成であれば、欠けている箇所は追加の指示をして改善していけばいいので、まずは中身が人だと思って、会話形式でやってみても何も問題ないと思います。
その後の指示
さて、ここまででも動作するのですが、多少の齟齬がありますので、続けてChatGPTには以下のような情報を与え、改善を重ねます。アコーディオンにまとめました。
その後の指示
nameの空白や#,&の記号は取り除いてください。
return tweets;ではなく、作成した文字列分、postというメソッドを実行してください。
postには文字列の引数を渡してください。
では、次に実行結果を残します。
実行結果はpost_historyというシートに残してください。post_historyのヘッダーはdate,content,resultの3つです。dateはA1セルで、content,resultもそれぞれB1,C1セルになります。
2行目からデータ入力をはじめ、dateにはpostを実行した日時、contentにはpostに渡した内容(今回はtweetに入っている文字列)、resultには、postメソッドの実行結果(postメソッドからは実行結果が文字列で返ってきます。)を入れます。
2回目以降は前回の実行結果も記入されているので、実行結果を残す際は空行から始めてください。
実行後post_historyに残すdateは時分秒も加えてください。
修正点があります。
- diff % 1 === 0はdiff === 1としてください。
postメソッドはpost.gsにあります。今回作成してもらったgetAnniversaryTweetsはpostAnniversary.gsに作成しました。
postAnniversary.gsのgetAnniversaryTweetsを実行し、post.gsのpostメソッドを呼び出します。
ありがとうございます。もう少しです。Twitterの仕様上、同じツイートを短い間隔で連続ツイートは禁止されています。
そのため、例えばdataに2件以上入っている場合は、dataの1件目、2件目を5分ずらしてpostを実行したいです。
トリガーも交えて実現してください。
ありがとう！完成です。最後にトリガーを設定したいです。
毎朝6時にpost_listシートに対して、prepareAnniversaryTweetsを実行させてください。
最終的にできあがったコード
そして、最終的にできあがったコードがこちら
最終形態
// 最初に呼び出す関数（毎日実行する想定）
function prepareAnniversaryTweets() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('post_list');
const data = sheet.getDataRange().getValues();
const header = data[0];
const releaseDateIndex = header.indexOf("release_date");
const nameIndex = header.indexOf("name");
const platformIndex = header.indexOf("platform");
const urlIndex = header.indexOf("url");
const today = new Date();
const todayMonth = today.getMonth();
const todayDate = today.getDate();
const thisYear = today.getFullYear();
const tweets = [];
for (let i = 1; i < data.length; i++) {
const row = data[i];
const releaseDate = new Date(row[releaseDateIndex]);
if (releaseDate.getMonth() === todayMonth && releaseDate.getDate() === todayDate) {
const releaseYear = releaseDate.getFullYear();
const diff = thisYear - releaseYear;
if (diff === 1 || diff % 5 === 0) {
const name = row[nameIndex];
const cleanedTag = cleanTag(name);
const platform = row[platformIndex];
const url = row[urlIndex] || "";
const tweet = `${formatDate(releaseDate)}:${name}(${platform}) ${diff}周年 #${cleanedTag} ${url}`;
tweets.push(tweet);
}
}
}
if (tweets.length > 0) {
// 投稿待ちリストを保存
PropertiesService.getScriptProperties().setProperty('pendingTweets', JSON.stringify(tweets));
// 最初のツイート処理を開始
postNextTweet();
}
}
// 1件ツイートして次回を予約
function postNextTweet() {
const props = PropertiesService.getScriptProperties();
const pendingTweets = JSON.parse(props.getProperty('pendingTweets') || '[]');
if (pendingTweets.length === 0) {
// もうツイートがない場合
props.deleteProperty('pendingTweets');
return;
}
const tweet = pendingTweets.shift(); // 先頭を取得＆削除
const now = new Date();
const result = post(tweet); // ※事前準備で作成したメソッドを発火
logPostHistory(formatDateTime(now), tweet, result);
if (pendingTweets.length > 0) {
// 残りがあるなら保存し直して5分後に再実行
props.setProperty('pendingTweets', JSON.stringify(pendingTweets));
ScriptApp.newTrigger('postNextTweet')
.timeBased()
.after(5 * 60 * 1000) // 5分後
.create();
} else {
// もう残ってないならクリア
props.deleteProperty('pendingTweets');
}
}
// post履歴を保存
function logPostHistory(date, content, result) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
let logSheet = ss.getSheetByName("post_history");
if (!logSheet) {
logSheet = ss.insertSheet("post_history");
logSheet.appendRow(["date", "content", "result"]);
}
const lastRow = logSheet.getLastRow();
const dataRange = logSheet.getRange("A2:A" + lastRow).getValues();
let insertRow = 2;
for (; insertRow <= lastRow; insertRow++) {
if (!dataRange[insertRow - 2][0]) break;
}
logSheet.getRange(insertRow, 1).setValue(date);
logSheet.getRange(insertRow, 2).setValue(content);
logSheet.getRange(insertRow, 3).setValue(result);
}
// YYYY/MM/DD形式にする
function formatDate(date) {
const yyyy = date.getFullYear();
const mm = ('0' + (date.getMonth() + 1)).slice(-2);
const dd = ('0' + date.getDate()).slice(-2);
return `${yyyy}/${mm}/${dd}`;
}
// YYYY/MM/DD hh:mm:ss形式にする
function formatDateTime(date) {
const yyyy = date.getFullYear();
const mm = ('0' + (date.getMonth() + 1)).slice(-2);
const dd = ('0' + date.getDate()).slice(-2);
const hh = ('0' + date.getHours()).slice(-2);
const mi = ('0' + date.getMinutes()).slice(-2);
const ss = ('0' + date.getSeconds()).slice(-2);
return `${yyyy}/${mm}/${dd} ${hh}:${mi}:${ss}`;
}
// nameから不要記号を除去
function cleanTag(name) {
return name.replace(/[\s#&@!$%^*()=+\[\]{}|\\:;"'<>,.?/~`]/g, '');
}
トリガー
その後の指示の中で指示しているとおり、時限起動させるためのトリガーの設定方法もChatGPTに聞いています。
以下のような回答が返ってきました。
ということで、実際にGASの画面で設定します。
実行履歴
トリガーと同じくその後の指示の中で指示しているとおり、実行履歴を別シートに残す処理の実装も指示しています。
指示の結果追加されたのが最終的にできあがったコードにあるlogPostHistoryメソッドです。
これによって、ポスト時に以下のように履歴が残るようになりました。
さいごに
これで全ての要件が揃い、完了です。
ChatGPTを使用したおかげで事前調査を含めて1時間程度で完成しました。
自分の手でイチからコードを組み上げようとすると恐らく1週間程度はかかったと思います。かなりの工数削減に繋がることを実感しました。
今回作成したスプレッドシート内容をポストする仕組みを流用すれば、予約投稿も実現できると思うので、もう少し手を入れて便利にしていこうと思います。
Discussion