GASでスプレッドシートのリンク切れを自動チェック with ChatGPT

まなび梯

2023年4月17日 20:20

Google Apps Script（GAS）を使って、Googleスプレッドシート上のリンク切れを自動確認します。コードはChatGPTで生成したので、ChatGPTに与えた指示（プロンプト）も紹介します。

自動生成ではなく、コードのことも理解して一から実装したい方は、恐れ入りますが他の記事をご参照ください。たとえば次の記事などがあります。

▍やりたいこと

次のようなシートがあります。

URLのリンク切れをチェックし、チェック日とチェック結果をします。それを毎日自動実行し、もしリンク切れがあればメール通知したいです。

▍ChatGPTを使用したコード生成

与えた指示は次のとおりです。スプレッドシートの構成を貼り付けること、要件や処理内容を具体的に書くことが重要です。なお、ここではChatGPTのバージョンはGPT-4を使っています。

GoogleスプレッドシートにあるURLのリンク切れを自動確認するスクリプトを作成したいです。

スプレッドシートの構成は以下です。
No タイトル URL 最終リンクチェック日チェック結果
1 テスト1 https://test1.example.com
2 テスト2 https://test2.example.com
3 テスト3 https://test3.example.com

要件は以下です。
・スクリプトはGoogle Apps Scriptで実行する。
・スプレッドシートのURLのリンク切れを確認する。リンク切れがなければOK、あればNGとする。
・実行のたびに一通りのリンク切れを確認する（過去にOKになっているものも確認対象とする）
・確認した日をスプレッドシートの最終リンクチェック日に記入する。
・確認結果をスプレッドシートのチェック結果に記入する。
・スプレッドシートへの読み書きを抑えるため、読み書きはシート単位で行う。
・チェック結果のうち1つでもNGがある場合、test@example.comにメールを送信する。

以上のコードを作成いただけますか？

生成されたコードは以下です。

function checkBrokenLinks() {
  const sheetName = "シート1"; // スプレッドシートのシート名を設定
  const emailRecipient = "test@example.com";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  const numRows = dataRange.getNumRows();
  const updates = [];
  let sendEmail = false;

  for (let i = 1; i < numRows; i++) {
    const url = values[i][2];
    const checkDate = new Date();
    let checkResult = "OK";

    try {
      const response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
      if (response.getResponseCode() !== 200) {
        checkResult = "NG";
        sendEmail = true;
      }
    } catch (error) {
      checkResult = "NG";
      sendEmail = true;
    }

    updates.push([checkDate, checkResult]);
  }

  if (updates.length > 0) {
    const updateRange = sheet.getRange(2, 4, updates.length, 2);
    updateRange.setValues(updates);
  }

  if (sendEmail) {
    const subject = "リンク切れの通知";
    const body = "スプレッドシート内にリンク切れが見つかりました。確認してください。";
    MailApp.sendEmail(emailRecipient, subject, body);
  }
}

要件通りに正しく動きますが、列の番号がハードコーディングされている点が気になるので、追加で次の指示を出しました。追加でコメントをつける指示もしていますが、これは要件の時点で伝えてもよいかもしれません。

列の番号を変数にしてもらっても良いですか？また、全体的にコメントもつけてください。

修正後のコードです。

function checkBrokenLinks() {
  const sheetName = "シート1"; // スプレッドシートのシート名を設定
  const emailRecipient = "test@example.com";
  const urlColumn = 3; // URL列の番号
  const checkDateColumn = 4; // 最終リンクチェック日列の番号
  const checkResultColumn = 5; // チェック結果列の番号
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  const numRows = dataRange.getNumRows();
  const updates = [];
  let sendEmail = false;

  // 各行のURLをチェック
  for (let i = 1; i < numRows; i++) {
    const url = values[i][urlColumn - 1];
    const checkDate = new Date();
    let checkResult = "OK";

    // URLのリンク切れを確認
    try {
      const response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
      if (response.getResponseCode() !== 200) {
        checkResult = "NG";
        sendEmail = true;
      }
    } catch (error) {
      checkResult = "NG";
      sendEmail = true;
    }

    updates.push([checkDate, checkResult]);
  }

  // 結果をスプレッドシートに書き込む
  if (updates.length > 0) {
    const updateRange = sheet.getRange(2, checkDateColumn, updates.length, 2);
    updateRange.setValues(updates);
  }

  // リンク切れがあればメールを送信
  if (sendEmail) {
    const subject = "リンク切れの通知";
    const body = "スプレッドシート内にリンク切れが見つかりました。確認してください。";
    MailApp.sendEmail(emailRecipient, subject, body);
  }
}

変数 `checkResultColumn` をその後使っていないなど小さな点は気になりますが、今回は動きさえすればよいのでそのまま採用しました。なお、シート名やメールアドレスは実際に使うものに変更しています。

後は、お好みの頻度でトリガーを設定して完了です。

▍おわりに

Googleスプレッドシートのリンク切れの自動確認を実装しました。テストも含めて所要時間は10分ほどでした。便利な時代になりましたね。

本格的なものを開発する場合にはそれなりのプロンプトエンジニアリングをするか、GitHub CopilotやAWS Copilotの導入になるかと思います。せっかくなので、開発のプロンプトエンジニアリングの参考を貼っておきます。

私たちのデジタル技術活用のメモが次のマガジンにあります。よろしければ覗いてみてください！