🤮

【GAS】ラッコキーワードの「見出し抽出」でダウンロードしたCSVファイルから記事リストを作る方法

2024/06/24に公開

SEO

ラッコキーワードには『見出し抽出』という機能があります。
この機能は、指定した検索キーワードで上位表示している記事20件のタイトルとURL、見出しを抽出できる機能です。

この機能をつかって、検索キーワードごとに上位表示している記事を洗い出す作業を担当しているマーケターは多数いらっしゃるのではないでしょうか。

ただこの機能の難点が1つあります。

それは、記事のタイトルとURLが横に並べられてしまうことです...
ラッコツールのCSV

エクセルやスプレッドシートは、文字列が縦に並んでいる方が、フィルタ機能や並べ替え機能がつかえて便利なことが多いです。

以下のように縦に記事をならべてリスト化したいと考えた人も多いでしょう。
縦並べ

手動で特殊貼付けで行と列を転置して貼り付けすることも少数のシートであれば可能です。
ただ、私がやったタスクは、1000キーワード以上の調査でした。

CSVファイル1000枚分を手動で転置貼り付けするのは鬼畜の拷問であり、この作業だけで1週間以上かかってしまうでしょう。

本記事ではGoogleAppsScriptを使って、ワンクリックで転置貼り付け1000枚のCSVファイル分を行う方法を解説します。

本記事はノンプログラマーでも、手順通り従うだけで記事リストがカンタンに作成できるように書きました。
GASがわからなくても、この手順通りに実行してみてください。

完成イメージ

以下のようにラッコツールの「見出し抽出機能」は記事一覧が横に並んで出力されます。
ラッコツールのCSV
このcsvファイルを以下のように、縦のリスト形式に並べ替えます。
縦並べ

これをGASでワンクリックで処理するコードを以下に紹介します。

1．スプレッドシートにCSVファイルをアップロード

スプレッドシートにCSVファイルをアップロードします。
今回は記事を書くために5枚のファイルをスプレッドシートにアップロードしました。

ラッコキーワードからダウンロードしたCSVファイル名は以下のようなものになっています。
headlines_薄毛_simple_20240621194143.csv
このファイル名を薄毛.csvにしておくと後々展開しやすいです。

2．GASで転置して並べるコードを作成

GASで転置して並べるコードを作成しました。
以下のコードの「スプレッドシートのURL」をあなたのスプレッドシートのURLにしてください。
（※共有リンクである必要はありません。）

また、複数のシートの情報を集約するシートの名前を「転置」として設置してみてください。
↓
シートを転置に命名する
この転置というシートにリストが完成されていきます。

tenchi.js

function copyAndPasteRowsAndRankKeywords() {
  var spreadsheetUrl = 'スプレッドシートのURL';
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  var sheets = spreadsheet.getSheets();
  var targetSheet = spreadsheet.getSheetByName('転置'); // 「転置」を指定

  var startRowB = 2; // B列の開始行
  var startRowC = 2; // C列の開始行

  sheets.forEach(function(sheet) {
    if (sheet.getName() === targetSheet.getName()) {
      return; // 貼り付け先シートは除外
    }

    var lastColumn = sheet.getLastColumn();
    if (lastColumn < 2) {
      return; // データが存在しない場合はスキップ
    }

    var row1Values = sheet.getRange(1, 2, 1, lastColumn - 1).getValues()[0];
    var row1Backgrounds = sheet.getRange(1, 2, 1, lastColumn - 1).getBackgrounds()[0];
    var row3Values = sheet.getRange(3, 2, 1, lastColumn - 1).getValues()[0];
    var row3Backgrounds = sheet.getRange(3, 2, 1, lastColumn - 1).getBackgrounds()[0];

    var row1TransposedValues = row1Values.map(function(cell) {
      return [cell];
    });
    var row1TransposedBackgrounds = row1Backgrounds.map(function(cell) {
      return [cell];
    });

    var row3TransposedValues = row3Values.map(function(cell) {
      return [cell];
    });
    var row3TransposedBackgrounds = row3Backgrounds.map(function(cell) {
      return [cell];
    });

    if (row1TransposedValues.length > 0) {
      var sheetNameArray = Array(row1TransposedValues.length).fill([sheet.getName()]); // シート名の配列を作成
      targetSheet.getRange(startRowB, 1, row1TransposedValues.length, 1).setValues(sheetNameArray); // A列にシート名を貼り付け
      targetSheet.getRange(startRowB, 2, row1TransposedValues.length, 1).setValues(row1TransposedValues); // B列に転置して貼り付け
      targetSheet.getRange(startRowB, 2, row1TransposedValues.length, 1).setBackgrounds(row1TransposedBackgrounds); // B列の背景色を転置して貼り付け
      startRowB += row1TransposedValues.length;
    }

    if (row3TransposedValues.length > 0) {
      var sheetNameArray = Array(row3TransposedValues.length).fill([sheet.getName()]); // シート名の配列を作成
      targetSheet.getRange(startRowC, 1, row3TransposedValues.length, 1).setValues(sheetNameArray); // A列にシート名を貼り付け
      targetSheet.getRange(startRowC, 3, row3TransposedValues.length, 1).setValues(row3TransposedValues); // C列に転置して貼り付け
      targetSheet.getRange(startRowC, 3, row3TransposedValues.length, 1).setBackgrounds(row3TransposedBackgrounds); // C列の背景色を転置して貼り付け
      startRowC += row3TransposedValues.length;
    }
  });

  rankKeywords(spreadsheetUrl, targetSheet.getName());
}

function rankKeywords(spreadsheetUrl, sheetName) {
  const keywordColumn = 1; // キーワードがある列（A列）
  const rankColumn = 4; // ランクを表示する列（D列）

  const sheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getSheetByName(sheetName);
  const data = sheet.getRange(2, keywordColumn, sheet.getLastRow() - 1).getValues(); // 2行目から最後まで取得

  let currentKeyword = '';
  let rank = 0;

  for (let i = 0; i < data.length; i++) {
    const keyword = data[i][0];
    if (keyword !== currentKeyword) {
      currentKeyword = keyword;
      rank = 1;
    } else {
      rank++;
    }
    sheet.getRange(i + 2, rankColumn).setValue(rank); // 2行目から設定
  }
}