Google – スプレッドシートの重複と類似を削除（SEOキーワードプランナーの整理に）

SEO
この記事は約7分で読めます。
スポンサーリンク

Googleスプレッドシートで重複を削除したい時、完全にセル内容が同じ場合は[データ] > [データ クリーンアップ] > [重複を削除]で削除できますが、例えば、「サプリメント ビタミン」と「ビタミン サプリメント」など、複数の単語がスペースを挟んで前後しているだけの違いだと削除対象になりません。

私の場合、ウェブサイトを運用していくうえで、SEO（検索エンジン対策）の基本的なツールとしてキーワードプランナーを利用する機会が多いのですが、記事に関連したキーワードを入力して調べた結果から、月間検索数や競合などを参考にするわけです。その際、結果をGoogleスプレッドシートとして、Googleドライブに保存して分析しています。
調べるキーワードは1単語のビッグキーワードから複数語のスモールワードまで、一度に10セットまで入力できるわけですが、問題は、その結果に重複や類似内容が多くあることです。
完全に重複している場合はありませんが、複数の単語がスペースを挟んで前後しているだけであったり、「に」「と」などの助詞を含んでいるだけであったりするので、整理して削除したほうが解読しやすくなります。
この重複や類似を削除するために、スプレッドシートの拡張機能を使います。拡張機能の Google Apps Script にスクリプトを作成して実行します。

SEOを行うにあたり、私の場合はキーワードプランナーの前にまずラッコキーワードのサジェスト検索でキーワード候補を探るのですが、そのデータを整理する場合にもこのスクリプトは使えます。

スポンサーリンク

ラッコキーワードとキーワードプランナーの結果を整理してSEOに活用

私のSEOキーワード選定手順は、まず、ラッコキーワードでターゲットとするキーワードのサジェストを検索します。そのCSVファイルをダウンロードしてGoogleドライブに保存し、スプレッドシートとして開きます。
重複や不要なセル（行）を削除して整理し、キーワードプランナーで調べるキーワード群のみにし、10セルずつキーワードプランナーにコピペして結果を表示させ、GoogleスプレッドシートとしてGoogleドライブに保存します。
Apps Scriptを用いてスプレッドシートの重複と類似を削除して簡潔にし、SEOに利用します。

Apps ScriptでGoogleスプレッドシートの選択範囲からスペースや助詞を挟んで同じ語句が重複しているセルを削除

  1. 編集したいGoogleスプレッドシートを開いて、メニューの上部にある「拡張機能」をクリックし、「Apps Script」を選択します。

  2. デフォルトで表示されるコード（function myFunction() {}）を削除し、下記のスクリプトをコピーしてエディタに貼り付けます。

Apps Script
function removeSimilarEntriesInSelection() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveRange(); // 現在選択されている範囲を取得
  var values = range.getValues();
  
  // 無視する単語リスト（スペースを含む）
  var ignoreWords = [' の ', ' と ', ' は ',' が ', ' に '];
  
  // ほとんど同じ内容を比較するための正規化関数
  function normalizeAndSort(text) {
    var normalizedText = text.toLowerCase().replace(/\s+/g, ' ').trim();
    ignoreWords.forEach(word => {
      var regex = new RegExp(word, 'g');  // スペースを1つ残す
      normalizedText = normalizedText.replace(regex, ' ').replace(/\s+/g, ' ').trim();
    });
    return normalizedText.split(' ').sort().join(' ');
  }
  
  var normalizedValues = values.map(row => row.map(cell => normalizeAndSort(cell)));
  var rowsToDelete = [];
  
  for (var i = 0; i < normalizedValues.length; i++) {
    for (var j = i + 1; j < normalizedValues.length; j++) {
      for (var k = 0; k < normalizedValues[i].length; k++) {
        if (normalizedValues[i][k] === normalizedValues[j][k]) {
          rowsToDelete.push(range.getRow() + j);  // 選択範囲の開始行番号を考慮
          break;
        }
      }
    }
  }
  
  // 重複排除して逆順にソート（下から削除していくため）
  rowsToDelete = [...new Set(rowsToDelete)].sort((a, b) => b - a);
  
  for (var r = 0; r < rowsToDelete.length; r++) {
    sheet.deleteRow(rowsToDelete[r]);
  }
}

スクリプトの補足説明

無視する単語リスト
今回使っているキーワードプランナーのデータは、キーワードに助詞が入っていると必ずその助詞の前後にスペースがあるので、スペースを含んでいます。このリストを変更することで削除する語句を決められます。
ほとんど同じ内容を比較するための正規化関数
関数 normalizeAndSort は、与えられたテキストを標準化（正規化）し、単語の順序が異なる場合でも同じ内容と見なせるように準備するためのものです。
  • 大文字小文字を区別しないように統一する（すべて小文字に変換）。
  • 不要なスペースを取り除く。
  • 特定の単語（ignoreWordsリストに含まれる単語）を無視する。
  • 単語の順序をアルファベット順に並び替える。
text.toLowerCase()
テキスト内のすべての文字を小文字に変換します。
.replace(/\s+/g, ' ')
テキスト内の連続するすべてのスペースを1つのスペースに置き換えます。
.trim()
テキストの先頭と末尾にある余分なスペースを削除します。
ignoreWords.forEach(word => { ... })
ignoreWords というリスト内の各単語について、以下の操作を実行します。
new RegExp(word, 'g')
リストに含まれる無視したい単語を、グローバルな正規表現として定義します。例えば、' の ' が ignoreWords に含まれている場合、この単語を含むすべての部分を検索します。
normalizedText.replace(regex, ' ')
正規表現に一致するテキストを見つけ、それをスペースに置き換えます。例えば、"犬 の 涙 やけ" は "犬 涙 やけ" に変換されます。
.replace(/\s+/g, ' ').trim()
置換後、再度連続するスペースを1つのスペースに置き換え、テキストの先頭と末尾のスペースを削除します。
.split(' ')
テキストをスペースで分割して、単語の配列に変換します。
.sort()
配列内の単語をアルファベット順に並べ替えます。
.join(' ')
並べ替えた単語をスペースで結合し、再び1つの文字列に戻します。

  1. スクリプトエディタの左上にある「無題のプロジェクト」をクリックしてプロジェクトに名前を付け、「保存」アイコンをクリックして保存します。ここでは「removeSimilarEntriesInSelection」と名前を付けます。

  2. スプレッドシートに戻り、重複・類似チェックを行いたい範囲を選択します。

  3. スクリプトエディタに戻り、上部にある「関数を選択」のドロップダウンメニューから「removeSimilarEntriesInSelection」を選択し、「実行」ボタンをクリックします。

  4. 初回実行時には、Googleアカウントの承認が必要です。指示に従って承認を完了します。
    Chromeの場合は、「権限を確認」→「アカウントの選択」→「removeSimilarEntriesInSelection（安全ではないページ）に移動」→「許可」

  5. スクリプト「removeSimilarEntriesInSelection」が実行され、特定の助詞やスペースを無視し、単語の順序が異なるだけのセルを含む行を削除することができます。

  6. スプレッドシートを確認すると、「犬　涙　やけ」と類似・重複する「涙　やけ　犬」や「犬　の　涙　やけ」が削除されています。

以上、ご参考になれば幸いです。
皆さまにすべての良きことが雪崩のごとく起きますように。

スポンサーリンク

コメント

タイトルとURLをコピーしました