Google Search ConsoleをGoogle Apps Scriptから呼びだす

はじめに

SearchConsoleのデータから日付×URLという表が作りたかったので、Google SpreadSheet上でAppsScriptで作ればサーバーもいらないし、いいじゃないかと思って作ってみました。

注意事項

SpreadSheetを使う上で制限があります。

• 200万セルが最大
  ドライブ ヘルプ
  あまり長期間のデータを抜こうとすると、書き込めなくなります。

• Spreadsheets作成 1日250回

• URL Fetch(httpアクセス)　1日2万回

• URL Fetch(httpアクセス)　1日50MB

Quotas for Google Services
開発中にがんがん試していたら50MBにひっかかりました。

アカウント準備

SearchConsoleにアクセスするために認証が必要になるので、アカウントを作成します。またOAuth認証用ののライブラリも使用します。
apps-script-oauth2

Google Developers ConsoleからOAuthにアクセスするクライアントIDを発行してください。

• プロジェクトを作成
• APIと認証 > 認証情報 > 認証情報を追加 > OAuth 2.0 クライアント ID
• • ウェブアプリケーションを選択
• • 認証済みのリダイレクトURIに以下を設定
• https://script.google.com/macros/d/{PROJECT_KEY}/usercallback
  {PROJECT_KEY}にはスプレッドシートのプロジェクトキーを入れてください。
• プロジェクトキーはスクリプトエディターのメニューから確認できます。
  ファイル > プロジェクトのプロパティ > 情報 > プロジェクトキー

認証

var CLIENT_ID = 'YOUR_ID';
var CLIENT_SECRET = 'YOUR_SECRET';

// 認証のエンドポイントとなるダイアログを表示します。
function alertAuth() {
  var service = getService();
  var authorizationUrl = service.getAuthorizationUrl();
  var template = HtmlService.createTemplate(
    '<a href="<?= authorizationUrl ?>" target="_blank">認証</a>. ' +
    '認証が完了したら再度操作を行ってください。');
  template.authorizationUrl = authorizationUrl;
  var page = template.evaluate();
  
  SpreadsheetApp.getUi().showModalDialog(page, "認証が必要です");
  Logger.log("認証が切れています");
}

// SearchConsole APIのサービスを取得
function getService() {
  // Create a new service with the given name. The name will be used when
  // persisting the authorized token, so ensure it is unique within the
  // scope of the property store.
  return OAuth2.createService('webmasters')

      // Set the endpoint URLs, which are the same for all Google services.
      .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
      .setTokenUrl('https://accounts.google.com/o/oauth2/token')

      // Set the client ID and secret, from the Google Developers Console.
      .setClientId(CLIENT_ID)
      .setClientSecret(CLIENT_SECRET)

      // Set the name of the callback function in the script referenced
      // above that should be invoked to complete the OAuth flow.
      .setCallbackFunction('authCallback')

      // Set the property store where authorized tokens should be persisted.
      .setPropertyStore(PropertiesService.getUserProperties())

      // Set the scopes to request (space-separated for Google services).
      .setScope('https://www.googleapis.com/auth/webmasters.readonly')
}

//認証コールバック
function authCallback(request) {
  var service = getService();
  var isAuthorized = service.handleCallback(request);
  if (isAuthorized) {
    return HtmlService.createHtmlOutput('認証に成功しました。タブを閉じてください。');
  } else {
    return HtmlService.createHtmlOutput('認証に失敗しました。タブを閉じてください。');
  }
}

alert()を呼び出したらポップアップが表示されて、認証のリンクを押すとOauthの画面に遷移する仕組みになっています。
OauthへのURLへコード上でリダイレクトさせることができないようなので、リンクで飛ばすようにしています。

SearchConsoleへのリクエスト

var SITE_URL = 'https%3A%2F%2Fmatome.miil.me'

/* SearchConsoleからデータを取得
* @param date Date 日付
* @param unscope_url Array 除外するURLのリスト
* @return Hash レスポンス
*/
function importData(date, unscope_url){
  // 条件指定
  // 読み込み済みのURLを除外
  for(var i=0; i<unscope_url.length; i++){
    filters.push({
      dimension: "page",
      operator: "notEquals",
      expression: unscope_url[i]
    })
  }
  
  var params = {
    rowLimit: PER_RECORD,
    startDate: date.toISOString().substring(0, 10),
    endDate: date.toISOString().substring(0, 10),
    dimensions: ["page"],
    dimensionFilterGroups:[
      {
        filters: filters
      },
    ],
  }

  return query(params);
}

/* SearchConsoleへクエリ問い合わせ
*/
function query(params) {
  var service = getService();
  var response = UrlFetchApp.fetch('https://www.googleapis.com/webmasters/v3/sites/'+ SITE_URL +'/searchAnalytics/query', {
    method : "post",
    contentType: "application/json",   
    headers: {
      Authorization: 'Bearer ' + service.getAccessToken(),
    },
    payload:JSON.stringify(params),
  });
  return response;
}

importDataを呼び出すとデータがとれるようになっています。
除外のURLリストを渡すのは、SearchConsoleのAPIが5,000件までしか１度にとれないのと、ページネーションが行えないので仕方なく一度取得したURLは次は取らないようにフィルターとして渡すためです。

シートへの書き込み

//  日付の期間
var DATE_RANGE = 7;

/*
* 全シートを更新
* 昨日から数日分のシートを再作成する
*/
function updateSheets(){
  var service = getService();  
  if(!service.hasAccess()){
    alertAuth();
    return;
  }
  var date = new Date();
  date.setDate(date.getDate() - 2);
  create_records_sheet(date,DATE_RANGE);
}

/* 指定期間のレコードのシートを作成する
* ＠@param Date 取得対象の日付
* @param Int 取得過去日数
*/
function create_records_sheet(date, dateRange){
  sheet = getSheet("レコード");
  sheet.clear();
  set_record_format(sheet);
  
  date.setDate(date.getDate() - dateRange);
  var rowIndex = 2;
  for(var i=0; i < dateRange; i++){
    rowIndex = add_records(date, sheet, rowIndex);
    date.setDate(date.getDate() + 1);
  }
  sheet.autoResizeColumn(2);
}

// レコード記載用のシートのフォーマットを設定する
function set_record_format(sheet){
  ranges = sheet.getRange(1,1,1,6)
  ranges.setValues([["日付","URL","クリック数","表示回数","CTR","掲載順位"]])
}

/* 指定した日付のデータを指定のシートに追記する
* @param date データ取得対象日
* @param sheet 追加先のシート
* @param row 挿入開始行番号
*/
function add_records(date,sheet,rowIndex){
  var unscope_url = [];
  //一度に読み込める件数に制限がある(ページング制御もない)ので、ループして全件分を行う
  for(var count=0; count < 3; count++){ //無限ループでもいいけど、念の為100回くらいで止めておく
    // データを取得
    var data = JSON.parse(importData(date,unscope_url));
    
    // SpreadSheetに突っ込むために整形
    var records = modify_records(data,date);
    if(records.length == 0){
      break; //読込終了してるのでぬける
    }
    
    //取得したURLは読み込み済みのリストへ追加
    for(var i = 0; i < records.length; i++){
      unscope_url.push(records[i][1]);
    }
    
    // SpreadSheetへ書き込み
    writeSheet(sheet,records, rowIndex);
    rowIndex += records.length;
  }
  return rowIndex;
}

//  SearchConsoleの結果をSpreadSheetに突っ込むために整形
function modify_records(data,date){
  var records = [];
  var date_str = date.toISOString().substring(0, 10);
  if(data["rows"] != undefined){
    for (var i = 0; i < data["rows"].length; i++){
      record = data["rows"][i]
      records.push([date_str,record["keys"][0],record["clicks"],record["impressions"],record['ctr'],record['position']]);
    }
  }
  return records;  
}

/*
* データをシートに出力する
* @param sheet, 書き込む先のシート
* @param records 書き込むレコード 2次元配列をわたす
* @param row 書き込み開始する行番号
*/
function writeSheet(sheet,records,row){  
  ranges = sheet.getRange(row,1,records.length,6)
  ranges.setValues(records)
}

// シートを取得
// 同じシート名があれば既存のシートを返す
// 同じシート名がなければ新しく作成する
function getSheet(sheetName){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName(sheetName);
  if (sheet == null) {
    sheet = spreadsheet.insertSheet(sheetName);
  }
  return sheet;
}

updateSheetを呼び出せば数日分のデータが日ごとに"レコード"のシートに書き込まれます。
あとはそのシートからピボットテーブルを作れば日付×URLの表がつくれます。
最後に使いやすいように更新のコマンドをメニューに追加しておきます。

//シート開始時の処理
function onOpen() {
  // 更新用のメニューを追加
  SpreadsheetApp.getUi()
      .createMenu('SearchConsole')
      .addItem('最新の情報を取得', 'updateSheets')
      .addToUi();
}

終わりに

作ってみて、SearchConsoleのデータは表示回数が0ならばデータが入ってこないです。
それとクエリも指定しないと何で表示された掲載順位かもわからない、というのが辛いところ