gas_sf_export_record.js

/**
 * クエリ発行してスプレッドシートに書き込み
 */
function query(q) {
  checkAuthorization();
   
  //クエリ発行
  var prop = PropertiesService.getUserProperties();
  var sessionInfo = JSON.parse(prop.getProperty("session_info"));
  var res = UrlFetchApp.fetch(sessionInfo.instance_url + "/services/data/v30.0/query/?q=" + q, {
    "method" : "GET",
    "headers" : {
      "Authorization": "Bearer " + sessionInfo.access_token
    }
  });
   
  //レスポンスをパースして変数に格納
  var queryResult = JSON.parse(res.getContentText());
  var fieldsArray = getFieldsFromQueryResult(queryResult);
  var records = [fieldsArray];
   
  queryResult.records.forEach(function(record){
    var pushRecord = [];
    fieldsArray.forEach(function(field) {
      pushRecord.push(record[field]);
    });
    records.push(pushRecord);
  });
 
  //シートに書きこみ  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = SpreadsheetApp.getActiveRange();
  sheet.getRange(range.getRow(), range.getColumn(), records.length, fieldsArray.length).setValues(records);
     
}
 
/**
 * QueryResultから項目リストを取得
 */
function getFieldsFromQueryResult(qr) {
  var fields = Object.keys(qr.records[0]);
  fields.forEach(function(field, i){
    if (field == "attributes") {
      return fields.splice(i,1);
    }
  });
  return fields;
}
 
/**
 * 認証状態を確認し、access_tokenの有効期限が切れていたら
 * refresh_tokenでaccess_tokenを再取得する。
 */
function checkAuthorization() {
  var prop = PropertiesService.getUserProperties();
  var sessionInfo = JSON.parse(prop.getProperty("session_info"));
  var res = UrlFetchApp.fetch(sessionInfo.instance_url + "/services/data/v30.0", {
    "method" : "GET",
    "headers" : {
      "Authorization": "Bearer " + sessionInfo.access_token
    },
    "muteHttpExceptions": true
  });
   
  if (res.getResponseCode() === 401) {
    var res = UrlFetchApp.fetch(
      ACCESS_TOKEN_URL, 
      {
        "method" : "POST",
        "payload" : {
        "grant_type": "refresh_token",
        "client_id": CLIENT_ID,
        "client_secret": CLIENT_SECRET,
        "refresh_token": sessionInfo.refresh_token
      },
      "muteHttpExceptions": true
    });
     
    if (res.getResponseCode() == 200) {
      var newSessionInfo = JSON.parse(res.getContentText());
      newSessionInfo.refresh_token = sessionInfo.refresh_token;
      prop.setProperty("session_info", res.getContentText());
    }
  }
}