Webサイトのアクセス解析や広告の効果測定を行う際、「URLの表記揺れ」が原因で正確なデータを取得できずに困った経験はありませんか？
- http:// と https:// が混在している
- www の有無が統一されていない
- URLの末尾に / が付いていたり、付いていなかったりする
- 広告用のパラメータが付与されたURLが大量にある
これらの表記揺れは、分析ツール上でそれぞれ「別のページ」として認識されてしまい、PV数やコンバージョン数が分散する原因となります。これでは、データに基づいた正しい意思決定は困難です。
この記事では、そんな面倒なURLの表記揺れを自動的に統一（クレンジング）する方法を、GoogleスプレッドシートとGoogle Apps Script（GAS）を使って解説します。
データ分析の精度を上げ、業務を効率化したいWeb担当者やマーケターの方は、ぜひ最後までご覧ください。
【解決フロー】コピペで使える！URLクレンジングGAS
ここからは、実際に課題を解決するための手順です。
今すぐ解決したい方は、以下の手順を使えばURLの表記揺れを解消できます。
具体的な設定方法や仕組みを知りたい方は、次の章【詳しく知りたい方へ】に進んでください。
Step1：事前準備
1. 対象データの用意
はじめに、表記揺れを修正したいURLデータ（ExcelまたはCSV形式）をご用意ください。
※本記事では、スプレッドシート上のサンプルデータを使用します。
2. スプレッドシートのコピー
URL変換スプレッドシートを開き、コピーを作成してください。
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)$", ""),
"/$", "")
),
"")
この関数では、以下の処理を内側から順番に行っています。
- ASC(A3): 全角の英数字や記号を半角に変換します。
- LOWER(…): すべてのアルファベットを小文字に変換します。
- REGEXREPLACE(…, “^http:”, “https:”): http:で始まる部分をhttps:に置換します。
- REGEXREPLACE(…, “^https://www\.”, “https://”): https://www.で始まる部分からwww.を削除します。
- REGEXREPLACE(…, “\?.*”, “”): ?以降のパラメータ部分を削除します。
- REGEXREPLACE(…, “#.*”, “”): #以降のフラグメント部分を削除します。
- REGEXREPLACE(…, “/index\.(…)$”, “”): 末尾のindex.htmlなどを削除します。
- REGEXREPLACE(…, “/$”, “”): 末尾のスラッシュを削除します。
- TRIM(…): 最後に、文字列の前後の余分なスペースを削除します。
このように、複数の関数を組み合わせる（ネストする）ことで、GASと同様の処理を実現できます。
しかし、ご覧の通り関数が非常に長くなり、どの部分がどの処理に対応しているのか一見して分かりにくいというデメリットがあります。ルールの追加や修正も困難になりがちです。
手軽に試せる点は関数のメリットですが、繰り返し行う作業の自動化や、複雑なルールでの処理、メンテナンスのしやすさを考慮すると、GASの利用が断然おすすめです。
まとめ
今回は、GoogleスプレッドシートとGASを使って、散在しがちなURLの表記揺れを自動でクレンジングする方法について解説しました。
手作業で一つずつ修正していたURLの表記揺れも、一度この仕組みを導入してしまえば、数クリックで瞬時に完了します。
これにより、データ集計や分析にかかる時間を大幅に短縮できるだけでなく、データの精度が向上し、より的確なマーケティング施策の立案につながるでしょう。
ぜひ、今回のスクリプトを日々の業務にお役立てください。
※Googleサービスは、Google LLC の商標であり、この記事はGoogleによって承認されたり、Google と提携したりするものではありません。
