GASでURLを自動クレンジング！コピペでOK

Webサイトのアクセス解析や広告の効果測定を行う際、「URLの表記揺れ」が原因で正確なデータを取得できずに困った経験はありませんか？

http:// と https:// が混在している
www の有無が統一されていない
URLの末尾に / が付いていたり、付いていなかったりする
広告用のパラメータが付与されたURLが大量にある

これらの表記揺れは、分析ツール上でそれぞれ「別のページ」として認識されてしまい、PV数やコンバージョン数が分散する原因となります。これでは、データに基づいた正しい意思決定は困難です。

この記事では、そんな面倒なURLの表記揺れを自動的に統一（クレンジング）する方法を、GoogleスプレッドシートとGoogle Apps Script（GAS）を使って解説します。あわせて、コピペですぐに使える「URL表記ゆれ修正テンプレート（スプレッドシート）」を無料で配布しています。

日々のアクセス解析レポート作成や広告効果測定など、「URLの揺らぎがネックになりがちな場面」でそのまますぐに使えるよう設計していますので、ぜひ貴社のデータクレンジング業務にご活用ください。

無料配布：URL表記ゆれ修正テンプレート
【解決フロー】コピペで使える！URLクレンジングGAS
【詳しく知りたい方へ】URLクレンジングの仕組みを徹底解説
- GASで統一する代表的な表記揺れパターン
- GAS（Google Apps Script）で全自動化する仕組み
  - コード全文
  - コードの解説
【おまけ】スプレッドシート関数でクレンジングに挑戦
まとめ

無料配布：URL表記ゆれ修正テンプレート

プロトコルやwwwの有無、末尾スラッシュ、index.htmlの有無、計測用パラメータの有無など、
よくあるURLの表記揺れをまとめて統一できるテンプレートを無料で配布しています。
プログラムを書く必要はなく、テンプレートをコピーしてすぐに利用可能です。

このテンプレート1つで、よくあるURLの表記揺れパターンをまとめて自動修正できます。

【テンプレートで自動化できること】

・http:// を https:// に統一
・https://www.example.com のようなURLから www. を削除
・URL末尾の / を削除（ルートドメインは除外）
・/index.html や /index.php などインデックスファイルの削除
・? 以降の計測用パラメータ（クエリストリング）の削除
・# 以降のフラグメント（アンカー）の削除
・全角英数字の半角化、前後の余計なスペースの削除
・すべて小文字に統一

普段の手作業によるURLクレンジングの負荷を大幅に削減できます。
以下のフォームから、今すぐテンプレートをお受け取りください。

この記事を読めば、以下のことができるようになります。

・コピペで使えるGASで、すぐにURLの表記揺れ統一を自動化できる
・GASのコードがどのような処理をしているか、その仕組みを理解できる
・GAS以外の方法として、関数を使った代替案も知ることができる

なお、本記事で紹介している手順は、すべてテンプレートに実装済みです。
「まずは自社のリストで試してみたい」「コードを書く時間がない」という方は、ページ上部のフォームからテンプレートをお申し込みください。

【解決フロー】コピペで使える！URLクレンジングGAS

ここからは、実際に課題を解決するための手順です。
今すぐ解決したい方は、以下の手順を使えばURLの表記揺れを解消できます。

具体的な設定方法や仕組みを知りたい方は、次の章【詳しく知りたい方へ】に進んでください。

Step1：事前準備

1. 対象データの用意

はじめに、表記揺れを修正したいURLデータ（ExcelまたはCSV形式）をご用意ください。
※本記事では、スプレッドシート上のサンプルデータを使用します。

2. スプレッドシートのコピー

ページ上部のフォームからダウンロードした「URL変換スプレッドシート」を開き、ご自身のGoogleドライブに「コピーを作成」してください。
　※フォーム送信後に届くメールからテンプレートを取得できます。

Step2：対象データの更新

コピーしたスプレッドシートに対象データを貼り付けます。

Step3：変換の実行

1. Google Apps Script（GAS）の実行許可（初回のみ）

「URL変換開始」ボタンをクリック後、
Google Apps Script（GAS）の実行許可を行います。

2. 変換の実行

許可後に実行すると、URLの表記が自動で修正されます。

以上で、URLのクレンジングは完了です。
次の章では、この自動化の裏側で動いているGASの仕組みについて詳しく解説します。

【詳しく知りたい方へ】URLクレンジングの仕組みを徹底解説

ここからは、スプレッドシートに組み込まれているGoogle Apps Script（GAS）が、どのようにしてURLの表記揺れを統一しているのか、その仕組みを詳しく解説していきます。

コードを少し変更するだけで、ご自身の業務に合わせてカスタマイズすることも可能です。

GASで統一する代表的な表記揺れパターン

今回のスクリプトでは、データ分析の精度を落とす原因となりやすい、以下の8つの代表的な表記揺れパターンを自動で統一します。

プロトコルの違い： http:// を https:// に統一
wwwサブドメインの有無： www. を削除するルールに統一
末尾のスラッシュの有無： URL末尾の / を削除
インデックスファイルの有無： index.html や index.php などを削除
URLパラメータの有無： ? 以降の文字列（パラメータ）を削除
フラグメントの有無： # 以降の文字列（フラグメント）を削除
大文字・小文字の混在： すべて小文字に統一
不要なスペースや全角文字の混入： 前後の不要なスペースを削除し、全角英数字を半角に修正

GAS（Google Apps Script）で全自動化する仕組み

それでは、実際にスプレッドシートに組み込まれているGASのコードを見ていきましょう。

GASは、JavaScriptというプログラミング言語をベースにしており、スプレッドシートやGmailなどのGoogleサービスを操作できる非常に便利なツールです。

コード全文

/**
 * URLの表記揺れをクレンジングするメイン関数
 */
function cleanUrls() {
  // 現在アクティブなスプレッドシートを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 「URL変換」という名前のシートを取得
  const sheet = ss.getSheetByName('URL変換');

  // データが入力されている最後の行を取得
  const lastRow = sheet.getLastRow();
  // データが3行目より下にあるかチェック
  if (lastRow < 3) {
    Browser.msgBox('A列の3行目以降に、整形したいURLデータを入力してください。');
    return;
  }

  // A列の3行目から最終行までのURLデータを取得
  const range = sheet.getRange('A3:A' + lastRow);
  const urls = range.getValues();

  // 変換後のURLを格納するための配列を用意
  const newUrls = [];

  // forループを使って、取得したURLを一つずつ処理
  for (let i = 0; i < urls.length; i++) {
    // url変数に現在の処理対象URLを格納
    let url = urls[i][0];

    // URLが文字列で、かつ空でない場合のみ処理を実行
    if (typeof url === 'string' && url !== '') {
      
      // 1. 前後の半角・全角スペースを削除
      url = url.trim().replace(/^[　\s]+|[　\s]+$/g, "");

      // 2. 全角英数字を半角に変換
      url = url.replace(/[Ａ-Ｚａ-ｚ０-９]/g, function(s) {
        return String.fromCharCode(s.charCodeAt(0) - 0xFEE0);
      });

      // 3. すべて小文字に統一
      url = url.toLowerCase();

      // 4. "http://" を "https://" に統一
      url = url.replace(/^http:\/\//, 'https://');

      // 5. "www." を削除
      url = url.replace(/^https:\/\/www\./, 'https://');

      // 6. パラメータ（?以降）を削除
      const questionMarkIndex = url.indexOf('?');
      if (questionMarkIndex !== -1) {
        url = url.substring(0, questionMarkIndex);
      }
      
      // 7. フラグメント（#以降）を削除
      const hashIndex = url.indexOf('#');
      if (hashIndex !== -1) {
        url = url.substring(0, hashIndex);
      }

      // 8. 末尾の "index.html" や "index.php" を削除
      url = url.replace(/\/index\.(html|htm|php)$/, '');

      // 9. 末尾のスラッシュを削除（ただし、"https://example.com" のようなルートドメインの場合は残す）
      if (url.endsWith('/') && url.split('/').length > 3) {
        url = url.slice(0, -1);
      }
    }
    
    // 処理後のURLを配列に追加
    newUrls.push([url]);
  }

  // B列の3行目から、変換後のURLデータを一括で書き込む
  sheet.getRange('B3:B' + (newUrls.length + 2)).setValues(newUrls);
  SpreadsheetApp.flush();

  // 完了メッセージを表示
  Browser.msgBox('URLのクレンジングが完了しました。');
}

コードの解説

専門用語が多くて難しく感じるかもしれませんが、一つ一つの処理は非常にシンプルです。
ここでは、コードの主要な部分をピックアップして解説します。

getSheetByName(‘URL変換’): 操作対象のシートを指定しています。
getRange(‘A3:A’ + lastRow): A列の3行目からデータのある最終行までを範囲として選択しています。
getValues(): 選択した範囲の値をまとめて取得し、urlsという名前の箱（配列）に入れています。
for (let i = 0; i < urls.length; i++): forは「ループ処理」を行う命令です。取得したURLの数だけ、{}の中の処理を繰り返します。
url.replace(…): これが文字列を置換する命令です。例えば、url.replace(/^http:\/\//, ‘https://’) は、「行の先頭にあるhttp://という文字列をhttps://に置き換える」という意味です。このような置換処理を繰り返して、URLを綺麗にしていきます。
setValues(newUrls): ループ処理で綺麗になったURLデータを溜めておいたnewUrlsという箱（配列）の中身を、B列に一括で書き込んでいます。一つずつ書き込むよりも高速に処理が完了します。

このように、GASは「シートからデータを読み込み」→「ルールに従ってデータを加工し」→「シートに結果を書き出す」という一連の流れを自動で行ってくれます。

【おまけ】スプレッドシート関数でクレンジングに挑戦

GASを使わずに、スプレッドシートの標準機能である「関数」だけでも、ある程度のURLクレンジングは可能です。

例えば、B3セルに以下の関数を入力し、オートフィルで下の行にもコピーすれば、A列のURLを変換できます。

=IF(A3<>"",
  TRIM(
    REGEXREPLACE(
      REGEXREPLACE(
        REGEXREPLACE(
          REGEXREPLACE(
            REGEXREPLACE(
              REGEXREPLACE(
                LOWER(ASC(A3)),
                "^http:", "https:"),
              "^https://www\.", "https://"),
            "\?.*", ""),
          "#.*", ""),
        "/index\.(html|htm|php)$", ""),
      "/$", "")
  ),
"")

関数でクレンジングしている — この画像では2行目からデータが入っているため、A2セルを参照する関数をB2セルに入力しています

この関数では、以下の処理を内側から順番に行っています。

ASC(A3): 全角の英数字や記号を半角に変換します。
LOWER(…): すべてのアルファベットを小文字に変換します。
REGEXREPLACE(…, “^http:”, “https:”): http:で始まる部分をhttps:に置換します。
REGEXREPLACE(…, “^https://www\.”, “https://”): https://www.で始まる部分からwww.を削除します。
REGEXREPLACE(…, “\?.*”, “”): ?以降のパラメータ部分を削除します。
REGEXREPLACE(…, “#.*”, “”): #以降のフラグメント部分を削除します。
REGEXREPLACE(…, “/index\.(…)$”, “”): 末尾のindex.htmlなどを削除します。
REGEXREPLACE(…, “/$”, “”): 末尾のスラッシュを削除します。
TRIM(…): 最後に、文字列の前後の余分なスペースを削除します。