Googleスプレッドシートを使用してリンク切れをチェックする方法

職場では、タスクを自動化するのが好きです。特定のタスクで1つまたは複数のステップを繰り返す自己を見つけた場合、私のプログラマーはスナップを鳴らし、その繰り返しを避けるための努力を捧げます。

仕事で得た仕事の1つは、大学の内部検索エンジンからURLのリストを取得し、壊れているか新しいリンクにリダイレクトされているリンクを見つけることです。

通常、これは非常に簡単なタスクです。 URL列のリンクをクリックして、機能しているのか、リダイレクトされているのか、見つからないのかを確認するだけです。ただし、100行を超えるリストを取得した場合はどうなりますか?実際のところ、すぐにチームの誰かが、壊れたリンクやリダイレクトされたリンクについてもキーワードの集計リストをチェックする必要があるという問題を持ち出しました。そして、そのリストは2000行を超えています。

Google Apps Script:概要

GoogleのGsuiteスクリプトプラットフォームであるGoogle App Scriptを入力します。 Google Appスクリプトを使用すると、Googleドキュメント、スプレッドシート、カレンダーなどのGoogleのGsuite製品とやり取りする独自のカスタムスクリプト/関数を作成できます。

この場合、Googleのスクリプトエディターを使用して独自のカスタムgoogleシート関数(= SUM()など)を記述し、スプレッドシートからリンクのリストへのhttpリクエストを作成します。関数はそのリクエストから返されたステータスコードを返します。このスクリプト機能について気に入っているのは、すべてのスプレッドシートに統合されており、コードをJavaScriptで記述できることです。

ステップ1:Googleスプレッドシートでスプレッドシートを作成/インポートする

簡単な部分。新しいGoogleシートを作成し、リンクのリストをインポートするか、必要な列に手動で追加します。

[URL]列のリンクの状態を確認する列を追加します。私の場合は、ステータスコードの列です。

ステップ2:スクリプトエディターでカスタム関数を作成する

これは魔法の部分です。ツールバーから[ツール]メニューに移動し、[スクリプトエディター]をクリックします。

これにより、カスタム関数を作成できる新しいページが開きます。デフォルトの関数名はmyFunction()です。これをよりわかりやすい名前getStatusCode()に変更します。

関数getStatusCode(url){
   var response = UrlFetchApp.fetch(url);
   return response.getResponseCode();
}

この関数の目的は、単一のURLを取得し、そのアドレスに接続するときに返されるHttpステータスコードを返すことです。したがって、関数にurlという単一のパラメーターを渡します。

次に、responseという変数を作成し、UrlFetchAppクラスのfetchメソッドを使用して、URLをフェッチするリクエストを作成します。これにより、Http応答オブジェクトが返されます。次に、getResponseCode()メソッドを使用して、Httpヘッダーから応答コードを取得します。

良好で正常なリンクを示す番号コードは200です。有名な404はページが見つからない/削除されないことを意味し、3xxはページのリダイレクトを意味します。他にもここで確認できるものがたくさんあります。

ステップ3:スプレッドシートで関数をテストする

これで、関数の動作を確認できます。最初のURLの隣のセルに「= getStatusCode(a2)」と入力し、Enterキーを押して関数を実行します。

テストのために、4つの異なるリンクを選択しました。それぞれのリンクのステータスコードは200、404、503、および301です。

関数を使用して他のリンクをテストすると、予期しない値が得られます。 404および503リンクは期待されたコードを返しましたが、関数は不便な#ERROR!を投げました。返されたコードを読むためにカーソルを合わせる必要があります。これは、fetch()関数が、基本的に2xxコードである成功した呼び出しのみを返すために発生します。

最後のリンクはどうですか? 301を返すことになっていないのですか??繰り返しますが、サーバーがシートセルの値を説明する200コードを最後に与えた新しいページの場所にリダイレクトするため、fetch()関数でこれを非難できます。

ステップ4:コーディングボードに戻る

コードに落とし穴があるので、テストしたこれらのエッジケースをキャッチするための調整を行う必要があります。まず、エラーをキャッチします。この場合は、他のHttpエラーコードです。また、fetch()がリダイレクトを追跡するのを停止し、最初のリダイレクトコードを返します。

そのためには、2番目のパラメーターをfetch()メソッドに渡して、そのプロパティの一部を調整する必要があります。

関数getStatusCode(url){
   var options = {
     'muteHttpExceptions':true、
     'followRedirects':false
   };
   var url_trimmed = url.trim();
   var response = UrlFetchApp.fetch(url_trimmed、options);
   return response.getResponseCode();
}

optionsという名前の2番目のパラメーターをオブジェクトとして作成します。変更する最初のプロパティは、trueの「muteHttpExceptions」です。これを行うことで、fetch()メソッドが関数フローを中断するエラーをスローしないようにします。ご想像のとおり、デフォルトはfalseであるため、これをtrueに変更しています。

変更する2番目のプロパティは「followRedirects」です。これは、fetch()メソッドに、最終的な宛先への3xxリダイレクトに従うかどうかを指示するプロパティです。これをfalseに設定して、前に取得した200にだまされないようにします。

URLを変更したことにお気づきかもしれません。コメントでグレン・スミスが指摘したように、先頭または末尾にスペースがあるURLはエラーを引き起こす可能性があります。その場合に対応するために、URL文字列で.trim()メソッドを使用して、先頭または末尾のスペースを削除します。

optionsプロパティが設定されたので、それを2番目のパラメーターとしてfetch()メソッドに渡し、コードを保存します。

最終的なコードは次のようになります

これで、スプレッドシートに戻って関数を再度テストすると、関数がそのugい#ERRORを返さないことがわかります。メッセージも、301リダイレクトによってだまされません。

取得できるのは純粋なHttpステータスコードのみです

以上です!これらの8行のシンプルなJavaScriptコードを使用して、Googleのアプリスクリプトを活用し、スプレッドシート内の数百または数千のURLで各リンクを手動でクリックすることなく実行できます。

この関数をカスタマイズするために使用できる他の多くの方法とトリックがありますが、それはすべてあなたが探している情報とそれで何をしたいかによって異なります。