Google Apps ScriptでGoogle Search Consoleの情報を取得する

var CLIENT_ID = '{YOUR_CLIENT_ID}';
var CLIENT_SECRET = '{YOUR_CLIENT_SECRET}';
var SITE_URL = 'https%3A%2F%2Fmeganii.com';
var PER_RECORD = 500;

var SHEET_ID = '{YOUR_SHEET_ID}';


function openSheet() {
  var spreadsheet = SpreadsheetApp.openById(SHEET_ID);
  var sheet = spreadsheet.getSheetByName("Sheet");  //この場合、あらかじめ「Sheet」シートを作成しておく
  sheet.clear();

  var response = query();
  var json = JSON.parse(response.getContentText());
  var rows = json["rows"];

  // Add Header
  sheet.getRange(1, 1).setValue("keys");
  sheet.getRange(1, 2).setValue("clicks");
  sheet.getRange(1, 3).setValue("ctr");
  sheet.getRange(1, 4).setValue("impressions");
  sheet.getRange(1, 5).setValue("position");

  for (var i = 0; i < rows.length; i++) {
    sheet.getRange(i+2, 1).setValue(rows[i]["keys"]);
    sheet.getRange(i+2, 2).setValue(rows[i]["clicks"]);
    sheet.getRange(i+2, 3).setValue(rows[i]["ctr"]);
    sheet.getRange(i+2, 4).setValue(rows[i]["impressions"]);
    sheet.getRange(i+2, 5).setValue(rows[i]["position"]);
    Logger.log(rows);
  }
}

function query() {
  var date = new Date('2016-12-01');
  var params = {
    rowLimit: PER_RECORD,
    startDate: '2016-10-01',
    endDate:   '2016-12-31',
    dimensions: ["query"],
  }
  Logger.log(params);

  var service = getService();
  if(!service.hasAccess()){
    alertAuth();
    return;
  }

  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),
  });
  Logger.log(response);
  return response;
}

function alertAuth() {
  var service = getService();
  var authorizationUrl = service.getAuthorizationUrl();
  Logger.log(authorizationUrl);
}

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('認証に失敗しました。タブを閉じてください。');
  }
}

/**
 * Reset the authorization state, so that it can be re-tested.
 */
function reset() {
  var service = getService();
  service.reset();
}