サーチコンソールの詳細データをGoogleスプレッドシートに自動反映させてTableauにインポートする方法 :: 「清水 誠」公式サイト紹介されているSearch Analytics for Sheets - Google スプレッドシート アドオンいうGoogle Search Console のデータをGoogle Spread Sheet にバックアップするツールを使ってみました。

ツールデータを Export した後、Google データスタジオ で、グラフ表示をしようとしたのですが、データスタジオは tableau のようにスプレッドシートの複数のシートを正規表現でデータソースには設定できなそうです。

このため、スクリプトを作って、マージする処理を作成し、マージしたシートをデータソースとして設定しました。
作成したスクリプトを以下に記載します。


Google 複数シートのデータをマージするスクリプト

以下、シート名の末尾が4桁の数値で終わるシートのデータを、Merge という名称のシートにまとめるスクリプトです。

MergeMonthlyData.gs

function execute() {

 // Merge という名前のシートオブジェクトを取得する。
 // シート自体がなければ、新規作成する。
 var doc = SpreadsheetApp.getActiveSpreadsheet();
 var outputSheetName = "Merge";
 var dataSheet = doc.getSheetByName(outputSheetName);
 if(dataSheet == null) {
     doc.insertSheet(outputSheetName, 0);
     dataSheet = doc.getSheetByName(outputSheetName);
 }
 // 前回入力値のクリア 
 dataSheet.clear();

 // 末尾が4桁の数値で終わることを示す正規表現。
 var regexp = /\d{4}$/;
 var addHeader = true;
 var sheets = doc.getSheets();
 for (i in sheets){
   // シート名が正規表現にマッチする場合
   if (sheets[i].getSheetName().match(regexp)){ 
     var values = sheets[i].getDataRange().getValues();
     if(addHeader) {
         addHeader = false;
     } else {
         // シートに含まれるヘッダ部のdata除去する
         values.shift();
     }
     //最終行の位置を取得
    var endrow = Number(dataSheet.getLastRow()) + 1;
     //カラムの数を取得する
    var lastColumn = values[0].length;
     //行の数を取得する
   var lastRow = values.length;
     //データを転記する
    dataSheet.getRange(endrow,1,lastRow,lastColumn).setValues(values);      
   }
 }
}

説明

  • シート名の正規表現について
    Search Analytics for Sheetsスケジュール実行で作成するバックアップデータのシート名は、[Apr 2017] 等、
    末尾が4桁の数値で終わるため、正規表現の後方一致で、4桁の数値で終わる場合、マージ対象としました。

  • ヘッダ部について
    バックアップシートには、全てヘッダが付与されます。
    最初に取り込み対象になったデータのヘッダ部を Merge シートに転記し、
    次シートからは、ヘッダ部は読み飛ばすようにしました。

  • データの転記方法について
    dataSheet#appendRow()ありますが、件数が多いせいか動作が遅かったので、
    参考記事の内容を拝借して、データを転記するようにしました。


参考

Search Analytics for Sheetsスケジュール実行後に、月イチで、スクリプトが実行されるように、設定しています。
データスタジオ側は [Merge] シートを見るようにしておけば、集計後に、自動で反映されるようになります。

シートのデータ量に上限があるので、1年おきに新しいスプレッドシートを作るなどしないといけないかもしれないですが、毎月マージする地獄からは解放されました。
以上です。

コメント