gas_sf_create_metadata.js

/**
 * カスタムオブジェクトGAS__cを作成する。
 */
function createObject() {
  //package.xml作成
  var package = Utilities.newBlob("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\
<Package xmlns=\"http://soap.sforce.com/2006/04/metadata\">\
<types><name>CustomObject</name><members>GAS__c</members>\
</types><version>30.0</version>\
</Package>").setName("hogehoge/package.xml");
  
  //オブジェクトXML作成 
  var objInfo = Utilities.newBlob("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\
<CustomObject xmlns=\"http://soap.sforce.com/2006/04/metadata\">\
<actionOverrides>\
<actionName>Accept</actionName>\
<type>Default</type>\
</actionOverrides>\
<actionOverrides>\
<actionName>Clone</actionName>\
<type>Default</type>\
</actionOverrides>\
<actionOverrides>\
<actionName>Delete</actionName>\
<type>Default</type>\
</actionOverrides>\
<actionOverrides>\
<actionName>Edit</actionName>\
<type>Default</type>\
</actionOverrides>\
<actionOverrides>\
<actionName>List</actionName>\
<type>Default</type>\
</actionOverrides>\
<actionOverrides>\
<actionName>New</actionName>\
<type>Default</type>\
</actionOverrides>\
<actionOverrides>\
<actionName>Tab</actionName>\
<type>Default</type>\
</actionOverrides>\
<actionOverrides>\
<actionName>View</actionName>\
<type>Default</type>\
</actionOverrides>\
<deploymentStatus>Deployed</deploymentStatus>\
<description>このオブジェクトはGASから作ってます</description>\
<enableActivities>false</enableActivities>\
<enableFeeds>false</enableFeeds>\
<enableHistory>false</enableHistory>\
<enableReports>false</enableReports>\
<label>GoogleAppsScriptから作ったオブジェクト</label>\
<nameField>\
<label>The Name</label>\
<type>Text</type>\
</nameField>\
<searchLayouts/>\
<sharingModel>ReadWrite</sharingModel>\
</CustomObject>").setName("hogehoge/objects/GAS__c.object");
 
  var zip = Utilities.zip([package, objInfo]);
  var driveZip = DriveApp.createFile(zip); // データをGドライブに格納
  var binary = Utilities.base64Encode(zip.getBytes());
   
  var metaBody = "<?xml version=\"1.0\" encoding=\"utf-8\"?>\
<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" \
xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" \
xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">\
<soap:Header><SessionHeader xmlns=\"http://soap.sforce.com/2006/04/metadata\">\
<sessionId>{{sessionId}}</sessionId></SessionHeader></soap:Header><soap:Body>\
<deploy xmlns=\"http://soap.sforce.com/2006/04/metadata\">\
<ZipFile>{{zipfile}}</ZipFile>\
<DeployOptions>\
<allowMissingFiles>false</allowMissingFiles>\
<autoUpdatePackage>false</autoUpdatePackage>\
<checkOnly>false</checkOnly>\
<ignoreWarnings>false</ignoreWarnings>\
<performRetrieve>false</performRetrieve>\
<purgeOnDelete>false</purgeOnDelete>\
<rollbackOnError>false</rollbackOnError>\
<runAllTests>false</runAllTests>\
<singlePackage>false</singlePackage>\
</DeployOptions>\
</deploy>\
</soap:Body></soap:Envelope>";
   
  //deployコール
  checkAuthorization();
  var prop = PropertiesService.getUserProperties();
  var sessionInfo = JSON.parse(prop.getProperty("session_info"));
  var orgId = sessionInfo.id.match(/https:\/\/.+\.com\/id\/([a-zA-Z\d]+)\/([a-zA-Z\d]+)/)[1];
  var result = UrlFetchApp.fetch(sessionInfo.instance_url + "/services/Soap/m/30.0/" + orgId, {
    "method" : "POST",
    "payload" : metaBody.replace("{{sessionId}}", sessionInfo.access_token).replace("{{zipfile}}", binary),
    "muteHttpExceptions": true,
    "headers" : {
      "SOAPAction" : "\"\""
    },
    "contentType" : "text/xml"
  });
   
  //レスポンスのXMLのパース
  var doc = XmlService.parse(result.getContentText());
  var contents = doc.getDescendants();
  var checkId = '';
  for (var i = 0; i < contents.length; i++ ) {
    if (contents[i].getType() == XmlService.ContentTypes.ELEMENT) {
      if (contents[i].asElement().getName() == "id") {
        var checkId = contents[i].asElement().getText();
        break;
      }
    }
  }
 
  //ステータスチェック（ポーリング）
  while(true) {
    Utilities.sleep(1000);
    var status = checkDeployStatus(checkId);
    if (status != "InProgress" && status != "Pending") {
      break;
    };
  }
}
 
/**
 * ポーリング用メソッド
 */
function checkDeployStatus(checkId) {
   
  var metaBody = "<?xml version=\"1.0\" encoding=\"utf-8\"?>\
<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" \
xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" \
xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">\
<soap:Header><SessionHeader xmlns=\"http://soap.sforce.com/2006/04/metadata\">\
<sessionId>{{sessionId}}</sessionId></SessionHeader></soap:Header><soap:Body>\
<checkDeployStatus xmlns=\"http://soap.sforce.com/2006/04/metadata\">\
<ID>{{id}}</ID>\
<includeDetails>true</includeDetails>\
</checkDeployStatus>\
</soap:Body></soap:Envelope>";
   
  var prop = PropertiesService.getUserProperties();
  var sessionInfo = JSON.parse(prop.getProperty("session_info"));
  var orgId = sessionInfo.id.match(/https:\/\/.+\.com\/id\/([a-zA-Z\d]+)\/([a-zA-Z\d]+)/)[1];
  var result = UrlFetchApp.fetch(
    sessionInfo.instance_url + "/services/Soap/m/30.0/" + orgId, {
    "method" : "POST",
    "payload" : metaBody.replace("{{sessionId}}", sessionInfo.access_token).replace("{{id}}", checkId),
    "muteHttpExceptions": true,
    "headers" : {
      "SOAPAction" : "\"\""
    },
    "contentType" : "text/xml"
  });
 
  var doc = XmlService.parse(result.getContentText());
  var contents = doc.getDescendants();
  var checkId = '';
  for (var i = 0; i < contents.length; i++ ) {
    if (contents[i].getType() == XmlService.ContentTypes.ELEMENT) {
      if (contents[i].asElement().getName() == "status") {
        return contents[i].asElement().getText();
      }
    }
  }
  return '';
}

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());
    }
  }
}

gas_sf_oauth.js

var AUTHORIZATION_URL = "https://login.salesforce.com/services/oauth2/authorize?response_type=code&client_id={client_id}&redirect_uri={redirect_uri}&state={state}";
var ACCESS_TOKEN_URL = "https://login.salesforce.com/services/oauth2/token";
var CLIENT_ID = "input your client_id";
var CLIENT_SECRET = "input your client_secret";
var REDIRECT_URI = "https://script.google.com/macros/s/********************/usercallback";
 
/**
 * メニューの設定
 */
function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Salesforce Utility", [
    {name:"OpenDialog", functionName: "openDialog"}
  ]);
}
 
/**
 * ダイアログを開く
 */
function openDialog() {
  var prop = PropertiesService.getUserProperties();
  var template = HtmlService.createTemplateFromFile("dialog");
  var authorization_url = AUTHORIZATION_URL;
  authorization_url = authorization_url.replace("{client_id}", CLIENT_ID);
  authorization_url = authorization_url.replace("{redirect_uri}", encodeURIComponent(REDIRECT_URI));
  authorization_url = authorization_url.replace("{state}", ScriptApp.newStateToken().withMethod("callback").withTimeout(120).createToken());
   
  template.authorization_url = authorization_url
  SpreadsheetApp.getActive().show(template.evaluate());
}
 
/**
 * OAuthでコールバックされたcode値を元にaccess_tokenを取得・保持する。
 */
function callback(e) {
  var code = e.parameter.code;
  var res = UrlFetchApp.fetch(ACCESS_TOKEN_URL, {
    "method" : "POST",
    "payload" : {
      "grant_type" : "authorization_code",
      "code" : code,
      "redirect_uri" : REDIRECT_URI,
      "client_id" : CLIENT_ID,
      "client_secret" : CLIENT_SECRET
    }
  });
 
  var prop = PropertiesService.getUserProperties();
  prop.setProperty("session_info", res.getContentText());
 
  return HtmlService.createHtmlOutput("<div>windowを閉じて!!</div>");
}

gas_sf_update_record.js

/**
 * 更新クエリ
 */
function updateAccount() {
  checkAuthorization();
  var prop = PropertiesService.getUserProperties();
  var sessionInfo = JSON.parse(prop.getProperty("session_info"))
  //選択したセルがSalesforceのAccountIDであることを想定
  var accountId = SpreadsheetApp.getActiveRange().getValue();
  var res = UrlFetchApp.fetch(
    sessionInfo.instance_url + "/services/data/v30.0/sobjects/Account/" + accountId +"?_HttpMethod=PATCH", 
    {
      "method" : "POST",
      "headers" : {
        "Authorization": "Bearer " + sessionInfo.access_token
      },
      "payload": JSON.stringify({
      "Name": "GoogleAppsScriptによって更新！"
      }),
      "contentType": "application/json; charset=utf-8"
    }
  );
  return res.getContentText();  
}