下記記事を参考にQiita API v2を使用してQiitaのデータ(view, like, stock)を取得するGoogle Apps Script(GAS)を作成しました。
【Qiita API】いろんな方法で Views、Likes、Stocksを取得(JavaScript、Google Script、Python, Vue.js)
GASではHTTP GETリクエストを送信してスプレッドシートに追加する関数を時間ベース(1日1回)でトリガー設定して実行していました。
しかし、しばらく運用していたところレスポンスで403 Forbiddenエラーになることがあり、データを取得できないことが多々ありました。
参考として私の環境ではエラー率が60%を超えていました。(2022年9月25日時点)
ただ、早朝の時間に変更するとエラーはほぼ起きていないため、同じようにエラーが起きる場合はまずは時間帯の変更を試してみるのが良いかと思います。
実行数画面(エラー時のログ)
トリガー画面
また、エラー情報(Exception)はエラー発生日の翌日にgmailで送信される仕様のためエラーが発生してスレッドシートへのデータ追加が失敗したことに気づけないことがありました。
そこで機能改善として以下の機能に対応したので紹介します。
- エラー発生時のメール送信
- スマートフォンからの実行指示
仕様
上記改善と参考にさせていただいた記事との差異に関する仕様は下記の通りです。
- 初回起動時を想定し、関数実行で
control
,stock
,like
,view
,sum
シートを追加する。 - スプレッドシートにはAPIリクエスト時の日付データ(YYYY/MM/DD)と取得したデータをセットにして、
view
,like
,stock
シートに追加する。 - APIリクエストはGASの関数実行(手動、定期、チェックボックス操作)で送信する。
- チェックボックス操作はPC、スマートフォンに対応し、
control
シートのチェックボックスを有効(TRUE)にするとスマホからAPIリクエストを送信し、正常終了、異常終了時に無効(FALSE
)に変更する。 - 複数回データ取得を行う場合は日付データを基準に重複する日付を特定して、行単位で最新データのみ残す。
- エラー発生時は設定したメールアドレス宛にエラー内容を送信する。
ソースコード
GitHubにもコミットしています。
1.初回起動時を想定し、関数実行でcontrol
,stock
,like
,view
,sum
シートを追加する。
initSheet()
で実行します。
ポイントとしてはisExistingSheet(sheetName)
です。シート名を特定するためにはスプレッドシートIDを指定する必要があるため個別に関数化して対応しています。
2.スプレッドシートにはAPIリクエスト時の日付データ(YYYY/MM/DD)と取得したデータをセットにして、view
,like
,stock
シートに追加する。
myFunction()
の以下で対応しています。Dateオブジェクトを整形しているだけですが、月と日は3桁になることがあるため.slice(-2)
で調整しています。
3.APIリクエストはGASの関数実行(手動、定期、チェックボックス操作)で送信する。
4.チェックボックス操作はPC、スマートフォンに対応し、control
シートのチェックボックスを有効(TRUE)にするとスマホからAPIリクエストを送信し、正常終了、異常終了時に無効(FALSE
)に変更する。
定期実行ではトリガーにmyFunctionを登録して実行します。
手動実行ではmyFunction()を直接実行する方法とチェックボックスからのmyFunction()を実行する方法に対応しています。
チェックボックスからのmyFunction()に関連する処理は以下の通りです。
初期値(チェックボックス)をinitUniqueSheet()
で追加します。
HTTP Responseを受けて受信処理をした後にチェックボックスをFALSEに変更して処理を完了しています。
チェックボックス操作ではシートを更新(チェックボックスの変更)するためにイベントハンドラー(InstallableTriggers)を実行しています。
初めにスプレッドシートのセルを更新時に実行されるonEdit()
を実装しましたが、
Exception: SpreadsheetApp.openById を呼び出す権限がありません。 必要な権限: https://www.googleapis.com/auth/spreadsheets
エラーとなり実行できませんでした。
エラー内容の通り、スコープ(appsscript.json
)にhttps://www.googleapis.com/auth/spreadsheets
を追加しましたがエラー内容は解消されませんでした。
こちらの原因を調べたところ、SimpleTriggersには制限があることがわかりました。
また、こちらの記事の通りSimpleTriggersで実行されるメソッドでは他のスプレッドシートを参照することができないため、createEditTrigger()
によりInstallableTriggers(onEditCell(e)
)を追加して、チェックボックス操作でmyFunction()
を実行する形式にしました。
<補足>InstallableTriggersも他のトリガー同様にトリガーの一覧に表示されます。削除する際はメニューから削除することで対応できます。
5.複数回データ取得を行う場合は日付データを基準に重複する日付を特定して、行単位で最新データのみ残す。
checkSheetDateAndDeleteRowDuplicateDate(spreadsheet)
とdeleteRowDuplicateDate(sheet,val)
で対応します。
checkSheetDateAndDeleteRowDuplicateDate(spreadsheet)
ではシート情報と日付を作成し、その値をdeleteRowDuplicateDate(sheet,val)
の引数に指定して実行します。
deleteRowDuplicateDate(sheet,val)
ではシートから重複する日付データを取得してその行数を配列に詰めます。
重複する日付データは要素数1以下の場合は以降処理しないで終了します。
配列には重複する行数が格納されているため、deleteRow(要素数)
で指定して削除します。
なお、2行以上の重複がある場合は一番後ろのデータは残すためにduplicateDateList.length-2
からデクリメントして削除します。
6.エラー発生時は設定したメールアドレス宛にエラー内容を送信します。
エラー処理はmyFuction()でコールするfetch関数実行時のHTTP リクエスト送信時および レスポンスを受信時に発生するため、
try~catch分でエラー情報取得し、エラーログの出力及び、メール送信を実行します。
qiitaV2API.gs(エラー情報取得)
erros.gs
sendMail.gs
<注意事項>
エラー情報(Exception)発生時にログを出力したことにより、トリガー上のエラー率には反映されなくなります。
これはエラー時にcatch文が実行されることでスクリプトとしては正常終了とカウントされるためと考えられます。
エラー率を集計したい場合は個別に計算する必要があると思います。エラー率としてカウントできる仕組みや関数があるかもしれませんが、未確認です。
まとめ
参考記事のコードをベースに以下機能に対応したGASを紹介しました。
1. エラー発生時のメール送信
2. スマートフォンからの実行指示
本対応によりエラーの有無をメールから確認することができること、また、チェックボックス操作による関数実行スマートフォンから操作することができました。
これで漏れなくデータの集計が可能になると思います。
参考
以下の記事を参考にさせていただきました。
1. 【Qiita API】いろんな方法で Views、Likes、Stocksを取得(JavaScript、Google Script、Python, Vue.js)
2. 【解決】SpreadsheetApp.openById を呼び出す権限がありません。というエラー
3. スマホからGAS起動