matsumoto.asahi

2017.08.23

GASでSoQLを叩く

初めまして、新米エンジニアのAsahiです。
今回初めてエンジニアブログを書かせていただきます。
読みづらい点も多々あるかと思いますが、最後まで読んでもらえたら嬉しいです。

はじめに

今回はSalesforceからデータをGoogle Apps Script（以下GAS）で取ってこようと思います。
Salesforceとは営業支援ツールのことで、商談や契約の管理などができるサービスです。
使っている人は感じたことがあるかと思いますが、現状の把握や報告をする際にいつでもカスタマイズした最新のデータを表示したいという要望がありました。
Google スプレッドシートに最新のデータがまとまっていると、関数などを使って綺麗に現状を把握できるのではないかと思います。
それを実現するために、SalesforceのAPIをGASから叩いてデータが取ってこれるところまでやってみようと思います。

APIを叩くときに、SoQLというSalesforce上の独自SQLを使ってデータを取ってきました。
SoQLを使うと、条件に合った情報を簡単に取得できます。
今回SoQLの使い方を詳しくは説明しないので、知らない方はこちらから調べてみてください。

また、Salesforceの管理者権限がないとAPIを叩ける準備が整いません。
管理者でない人は、管理者と相談して実装してください。

ではまず、APIを使うための準備から始めたいと思います。

1.Salesforceにアプリケーションを用意

まずはSalesforce側でAPIを叩くためのアプリケーションを用意します。
「設定」から「ビルド > 作成 > アプリケーション」、その中の「接続アプリケーション」を見に行きます。
管理者であれば、「新規」ボタンがあるのでそれをクリックしてアプリケーションの作成を始めます。

1-1.接続アプリケーションの作成

接続アプリケーション名とAPI参照名と取引先責任者メールは任意のものを入れましょう。
「API(OAuth 設定の有効化)」内の「OAuth 設定の有効化」をチェックして、
「コールバック URL」に適当なものを入れ、
（今回はコールバックを使わないフローで認証をするので、適当なURLで構いません。）
「選択した OAuth 範囲」から「データへのアクセスと管理（api）」を追加しましょう。
そして「保存」を押して作成します。

※コールバックを行う認証の場合は、GASの公開をしてURLを取得、末尾のexecをusercallbackに変えることでGAS内のcallbackメソッドにコールバックさせることができます

1-2.ポリシーの編集

アプリケーションを作成しただけでは、まだ準備が足りていません。
ここからGASからAPIが叩けるよう、ポリシーを編集します。
「ビルド > 作成 > アプリケーション」の中の「接続アプリケーション」から今回作ったものを見つけます。
見つけたら「Manage」を押し、「ポリシーを編集」ボタンから下記の編集を行います。

「許可されているユーザ」を「すべてのユーザは自己承認可能」
「IP 制限の緩和」を「IP制限の緩和」

2.GASからアクセストークンを取得

GASの作成に入る前に、先ほど作成したアプリケーションの”コンシューマ鍵”(client_id)と”コンシューマの秘密”(client_secret)をメモっておきます。
「ビルド > 作成 > アプリケーション」の中の「接続アプリケーション」の中から、今回のアプリケーションの名前をクリックします。
すると、アプリケーションに関する情報が出てくるので、そこから”コンシューマ鍵”と”コンシューマの秘密”をローカルのどこかに保存しておきましょう。

2-1.GASにアクセストークン取得用メソッドを作成

早速、先ほどメモった鍵と秘密を利用して、アクセストークンを取得します。
まずは、Google Drive上にGASを作成します。
今回は、ユーザ名とパスワードを使ってアクセストークンを取得するので、Google スプレッドシートに紐付いたGASは使わないようにしましょう。
Google スプレッドシートの編集権限があればスクリプトが読めてしまうので、パスワードがバレてしまいます。

“コンシューマ鍵”・”コンシューマの秘密”・ユーザ名・パスワードを使って、
以下のメソッドの中身を書き換えてGASで実行してみてください。

function authorization() {
    var res = UrlFetchApp.fetch(
      "https://login.salesforce.com/services/oauth2/token", 
      {
        "method" : "POST",
        "payload" : {
          "grant_type": "password",
          "client_id": "***CLIENT_ID***", //コンシューマ鍵
          "client_secret": "***CLIENT_SECRET***", //コンシューマの秘密
          "username": "***username***", //ユーザ名
          "password": "***password***" //パスワード
      },
      "muteHttpExceptions": true
    });
    if (res.getResponseCode() == 200) {
      var prop = PropertiesService.getScriptProperties();
      prop.setProperty("session_info", res.getContentText());
    }
}

function authorization() {

var res = UrlFetchApp.fetch(

"https://login.salesforce.com/services/oauth2/token",

{

"method" : "POST",

"payload" : {

"grant_type": "password",

"client_id": "***CLIENT_ID***", //コンシューマ鍵

"client_secret": "***CLIENT_SECRET***", //コンシューマの秘密

"username": "***username***", //ユーザ名

"password": "***password***" //パスワード

"muteHttpExceptions": true

});

if (res.getResponseCode() == 200) {

var prop = PropertiesService.getScriptProperties();

prop.setProperty("session_info", res.getContentText());

}

無事実行が完了すると、スクリプトのプロパティに情報が入ってきます。
「ファイル」タブから「プロジェクトのプロパティ」を開いて、「スクリプトのプロパティ」タブから確認できます。

これで、アクセストークンが取得できました。
次は、アクセストークンを使ってSoQLを叩いてみます。

3.GASからSoQLを発行

3-1.アクセストークンの期限切れチェック

SoQLを発行するメソッドを作る前に、アクセストークンの期限が切れていた時のために、
トークンのリフレッシュをする処理を入れます。

function checkAuthorization() {
  var prop = PropertiesService.getScriptProperties();
  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("https://login.salesforce.com/services/oauth2/token", {
      "method" : "POST",
      "payload" : {
     　 "grant_type": "refresh_token",
        "client_id": "***CLIENT_ID***", //コンシューマ鍵
        "client_secret": "***CLIENT_SECRET***", //コンシューマの秘密
        "username": "***username***", //ユーザ名
        "password": "***password***" //パスワード
      },
      "muteHttpExceptions": true
    });
    
    if (res.getResponseCode() == 200) {
      var newSessionInfo = JSON.parse(res.getContentText());
      newSessionInfo.refresh_token = sessionInfo.refresh_token;
      prop.setProperty("session_info", res.getContentText());
    }
  }
}

function checkAuthorization() {

var prop = PropertiesService.getScriptProperties();

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("https://login.salesforce.com/services/oauth2/token", {

"method" : "POST",

"payload" : {

　 "grant_type": "refresh_token",

"client_id": "***CLIENT_ID***", //コンシューマ鍵

"client_secret": "***CLIENT_SECRET***", //コンシューマの秘密

"username": "***username***", //ユーザ名

"password": "***password***" //パスワード

"muteHttpExceptions": true

});

if (res.getResponseCode() == 200) {

var newSessionInfo = JSON.parse(res.getContentText());

newSessionInfo.refresh_token = sessionInfo.refresh_token;

prop.setProperty("session_info", res.getContentText());

}

このメソッドを、これから作っていくSoQL発行処理の前に入れるようにします。

3-2.SoQL発行

長くなりましたが、これでやっとSoQLを安定して叩くことができる状態ができました。
それでは早速、SELECT文を実行をしてみましょう。

まずはクエリの発行処理部分

function query(q) {
  checkAuthorization();
  
  q = encodeURIComponent(q).replace(/%20/g, '+');
  var prop = PropertiesService.getScriptProperties();
  var sessionInfo = JSON.parse(prop.getProperty("session_info"));
  var res = UrlFetchApp.fetch(sessionInfo.instance_url + "/services/data/v35.0/query/?q=" + q, {
    "method" : "GET",
    "headers" : {
      "Authorization": "Bearer " + sessionInfo.access_token
    }
  });
  
  var queryResult = JSON.parse(res.getContentText());
  return queryResult.records;
}

function query(q) {

checkAuthorization();

q = encodeURIComponent(q).replace(/%20/g, '+');

var prop = PropertiesService.getScriptProperties();

var sessionInfo = JSON.parse(prop.getProperty("session_info"));

var res = UrlFetchApp.fetch(sessionInfo.instance_url + "/services/data/v35.0/query/?q=" + q, {

"method" : "GET",

"headers" : {

"Authorization": "Bearer " + sessionInfo.access_token

}

});

var queryResult = JSON.parse(res.getContentText());

return queryResult.records;

}

自分で書き直す際に注意が必要なのは、クエリのエンコード処理です。

APIでクエリを発行する場合、クエリのスペースは「+」に変換する必要があります。
また、クエリをそのままパラメータに持ってくると、GASの方でエラーが発生します。
なので、エンコードする必要があるのですが、「+」に変換した後にエンコードするとクエリの実行が失敗します。
まず、クエリをエンコードし、それからエンコードされたスペース「%20」を「+」に変換しましょう。

この関数を使ってSoQLを発行します。
「最終更新日が昨日以降」の「商談」を取ってきます。

function getOpportunity() {
  var records = query(
    "SELECT Id, Amount, Name, CloseDate, LastModifiedDate"
    + " FROM Opportunity"
    + " WHERE LastModifiedDate >= YESTERDAY"
  );
  var data = [];
  for(var i = 0; i < records.length; i++) {
    Logger.log({
      id: records[i].Id, //商談ID
      amount: records[i].Amount, //金額
      name: records[i].Name, //商談名
      closeDate: records[i].CloseDate //完了予定日
    });
  }
}

function getOpportunity() {

var records = query(

"SELECT Id, Amount, Name, CloseDate, LastModifiedDate"

+ " FROM Opportunity"

+ " WHERE LastModifiedDate >= YESTERDAY"

);

var data = [];

for(var i = 0; i < records.length; i++) {

Logger.log({

id: records[i].Id, //商談ID

amount: records[i].Amount, //金額

name: records[i].Name, //商談名

closeDate: records[i].CloseDate //完了予定日

});

}

これを実行し、ログを確認してみてください。
ラグは、「表示」タブの中の「ログ」で確認できます。

SoQLでは、条件文で使う項目もSELECTに含めないといけません。
「LastModifiedDate」をSELECTに含めているのはそのためです。

まとめ

以上、ここまででGASからSoQLが叩けるようになりました。
自分が取ってきたい情報に合わせて、SoQLを書いて実行してみてください。

また、リレーションを使うときは注意してください。
例えば、「SELECT o.id, u.name FROM Opportunity o, Opportunity.LastModifiedBy u」というSoQLを実行すると以下のようなレスポンスが返ってきます。

{
  "totalSize": 100,
  "done":true,
  "records":[
    {
      "attributes":{
        "type":"Opportunity",
        "url":"/services/data/v35.0/sobjects/Opportunity/OPPORTUNITY_ID"
      },
      "Id":"OPPORTUNITY_ID",
      "LastModifiedBy":{
        "attributes":{
          "type":"User",
          "url":"/services/data/v35.0/sobjects/User/USER_ID"},
        "Name":"テスト太郎"
      }
    },
  ...略...
  ]
}

{

"totalSize": 100,

"done":true,

"records":[

{

"attributes":{

"type":"Opportunity",

"url":"/services/data/v35.0/sobjects/Opportunity/OPPORTUNITY_ID"

"Id":"OPPORTUNITY_ID",

"LastModifiedBy":{

"attributes":{

"type":"User",

"url":"/services/data/v35.0/sobjects/User/USER_ID"},

"Name":"テスト太郎"

}

...略...

]

}

o.id は record.Id で取ってこれますが、u.name は record.LastModifiedBy.Name で取ってこないといけません。
大半の方は気づくかもしれませんが、軽く注意してください。

今回はGASの実行が一回につき5分が限度なのと、
SoQLでの対象レコードの限度が2000個という点に対応していません。
実行結果が多くなりがちな場合は、その辺の対応をしつつ実装してみてください。

以上、最後まで読んでいただきありがとうございました。

matsumoto.asahi

エンジニア３年目　プランニング部に所属しています。海外向けプロダクトでの開発を１年、マーケティングツールのデータ整備やそれを用いた業務改善ツールの開発を１年やってきています。ラーメンのことを考えすぎて、脳がラーメンになりました。

RELATION ENTRY

matsumoto.asahiの記事一覧へ

▶︎

GASでSoQLを叩く

はじめに

1.Salesforceにアプリケーションを用意

1-1.接続アプリケーションの作成

1-2.ポリシーの編集

2.GASからアクセストークンを取得

2-1.GASにアクセストークン取得用メソッドを作成

3.GASからSoQLを発行

3-1.アクセストークンの期限切れチェック

3-2.SoQL発行

まとめ

RELATION ENTRY

HOT ENTRY

RECENT