はじめに

ごあいさつ

日本全国の「ちょっとした自動化ファン」のみなさま、
おつかれさまです。

Chromebook派として暮らしています、
マジセミ担当者Aでございます。

ちょっと自動化してみた内容について紹介します。

※本記事は、担当者Aの主観により作成されていますので、なにとぞ、そういうことでよろしくお願いします。

ことのあらまし

とあるデータ移行

ちょっとした「データ移行の用事」がありました。

リダイレクト設定用に
「データ移行元のURLリスト」が必要なので、
「560件のURLリスト」を作成しました。

正しくないURLがある

しかし、
「そのままでは正しくないURL」が、だいたい10%ほど存在していることが分かっています。

「正しいURL」に修正するために、
「正しくないURL」を抽出する必要があります。

正しいURLなのか？手動チェックは大変すぎる

「560件のURL」をポチポチ手作業で確認するのは、かなり大変です。

10件で90秒くらいかかるとして、
「1.4時間の地味作業」になってしまいます。

もはや『自動化したい』という野望が生まれざるを得ないのです。

ChatGPT4先生に聞いてみたところ『できますよ。』

担当者A『URLが正しく開くのかチェックは、できますか？』
ChatGPT4先生『できますよ。こちらがPythonのコードです。』
担当者A『GASでも、できますか？』
ChatGPT4先生『できますよ。こちらがGASのコードです。』

ということで、
「GAS×ChatGPT4」を使って、
「スプレッドシート上の560件のURL」が正しく開くのかチェックする機能
を作成してみることとなりました。

【参考】「GAS」とは

概要

「GAS」(Google Apps Script)とは、
Googleサービスを自動化するためのスクリプト言語です。

「Gmail」「Googleスプレッドシート」「Googleドキュメント」「Googleカレンダー」などの、
GoogleWorkspaceアプリケーションを相互に連携させる自動化処理を作成できます。

Googleアカウントがあれば、無料で利用できます。

→workspace.google.co.jp　→intl　→ja　→products　→apps-script

ユースケース

・Google Workspaceの自動化
・Googleスプレッドシートでのデータ処理や分析の自動化
・カスタム関数の作成
・Webアプリケーションの作成
・トリガーによる自動実行
・サードパーティ製のアプリ(サービス)と連携　など

『URLオープンチェック関数』作成

チェックシート

スプレッドシートの様子です。

「チェック対象URL」列に入力されているURLに対して、
「URLオープンチェック」列に、「◯」or「✕」を入力してもらいます。

関数コード生成用プロンプト

ChatGPT4にGAS関数を作ってもらうために、
プロンプトを作成します。

プロンプト

■指示
GAS用の関数を作成してください。

■関数仕様
①「チェック対象URL」にアクセスする
②「チェック対象URL」を開けた場合、「URLオープンチェック」列に「◯」を出力
③「チェック対象URL」を開けなかった場合　もしくは　エラーが発生した場合、「URLオープンチェック」列に「✕」を出力

■スプレッドシート説明
・1行目＝項目タイトル
・1列目＝「チェック対象URL」列
・2列目＝「URLオープンチェック」列

■備考
・関数名＝「check_url_open」
・コメントの次の行にコードを記述してください。

プロンプトを実行

ChatGPT4が作成したGAS関数コード「check_url_open」

“

function check_url_open() {
  // スプレッドシートを取得
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // 各行に対して操作を行う
  var rows = sheet.getDataRange().getValues();
  for (var i = 1; i < rows.length; i++) {
    var url = rows[i][0]; // 1列目が「チェック対象URL」列
    try {
      var responseCode = UrlFetchApp.fetch(url).getResponseCode();
      if (responseCode >= 200 && responseCode < 300) { // HTTPステータスコードが200番台ならページは開ける
        sheet.getRange(i + 1, 2).setValue(""◯""); // 「URLオープンチェック」列に「◯」を出力
      } else {
        sheet.getRange(i + 1, 2).setValue(""✕""); // 「URLオープンチェック」列に「✕」を出力
      }
    } catch (e) { // エラー発生時
      sheet.getRange(i + 1, 2).setValue(""✕""); // 「URLオープンチェック」列に「✕」を出力
    }
  }
}

”

コードをコピペ

Googleスプレッドシート　→メニュー　→拡張機能　→Apps Script

コードをコピペして、
保存アイコンをクリックします。

GAS関数コード「check_url_open」を実行してみる　→成功！

実行

権限確認

「アクセス権限が必要です」となるので、権限を確認して、許可します。

処理開始

処理が開始されました。

処理完了

10分ほどで完了しました。

◯✕が出ました

◯✕が出力されました。
セルの色指定は、スプレッドシートの「条件付き書式機能」を使っています。

確認してみると、
・◯のやつ＝開ける
・✕のやつ＝開けない
ということで、
成功です。

【反省会】どれだけ時間節約できたのか？

手動の場合

■(想定)「1.4」時間

今回の自動化の場合

■「0.35」時間
・コード作成：10分
・コード実行：11分

まとめ

・1時間ほどお得になった
・地味作業から解放される喜び
・判定は(おそらく)間違ってない(はず)
・コードを作ってもらうのがおもしろい
ということで、
それなりの成功です。よかったです。

【追加調査】AIチャット対抗コード生成選手権

せっかくなので、
同じプロンプトを、他のAIチャットにも投げて、
コード作成をお願いしてみようと思います。

参加AI選手　および　結果発表

Bing選手

コード生成

・◯：作ってくれました。
・✕：コメントは書いてくれませんでした。

コード

“

function check_url_open() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var urls = sheet.getRange(2, 1, lastRow - 1).getValues();
  
  for (var i = 0; i < urls.length; i++) {
    var url = urls[i][0];
    try {
      var response = UrlFetchApp.fetch(url);
      if (response.getResponseCode() == 200) {
        sheet.getRange(i + 2, 2).setValue(""◯"");
      } else {
        sheet.getRange(i + 2, 2).setValue(""✕"");
      }
    } catch (e) {
      sheet.getRange(i + 2, 2).setValue(""✕"");
    }
  }
}

”

実行結果

正しく動きました。

グッジョブです。

Bard選手

コード生成

・◯：作成してくれました。
・◯：コメントも書いてくれました。

コード

“

function check_url_open() {
  // チェック対象URLを取得する
  var checkUrl = SpreadsheetApp.getActive().getRange('A2').getValue();

  // チェック対象URLにアクセスする
  var response = UrlFetchApp.fetch(checkUrl);

  // チェック対象URLが開かれたかどうかをチェックする
  if (response.getResponseCode() === 200) {
    // チェック対象URLが開かれた場合
    SpreadsheetApp.getActive().getRange('B2').setValue('◯');
  } else {
    // チェック対象URLが開かなかった場合
    SpreadsheetApp.getActive().getRange('B2').setValue('✕');
  }
}

”

実行結果

✕：ダメでした。

「1つ目の◯✕」しかセットされません。

「◯✕をセットするセル位置」が固定されているどころか、
ループしていないので、1つ目で終わりです。

プロンプトの書き方が、いまいちなのかもしれませんが、
もうちょい汲んでほしかったところですね。

改善を期待しています。

Perplexity選手

コード生成

・◯：生成してくれました。

” >

コード

“

function check_url_open(url) {
  var response_code = UrlFetchApp.fetch(url).getResponseCode();
  if (response_code == 200) {
    return ""◯"";
  } else {
    return ""✕"";
  }
}

”

実行結果

✕：ダメでした。

「URLを引数として受け取る」タイプであり、
「思ってたのと違うやつ」になりました。

プロンプトの書き方が、いまいちなのかもしれませんが、
改善を期待しています。

まとめ

GPT系譜である「ChatGPT4」と「Bing」が見事にコードを生成してくれた
という結果になりました。

「たまたまの結果」なのかもしれないので、
いろいろ試してみるのも、よさそうですね。

【お知らせ】「マジセミ」のご紹介

「マジセミ」では、日々、多くのIT関連セミナーを開催しています。

多くのセミナーの中から「ご自身のスキルやビジネスを強化できるセミナー」を見つけて、ぜひ１度、参加してみてください。

マジセミ関連プラットフォーム

※「ご意見」「ご感想」「誤字がありましたよ」「こんな使い方もあるみたいですよ」などありましたら、
マジセミSNS(Facebook or Twitter)まで、お知らせください。