【備忘録：HubSpotユーザー向け】HubSpot APIから企業名を取得。Googleスプレッドシート（GAS）から呼び出してみる。

CRMツールでHubSpotを使っているのですが、APIの"やってみた"系日本語記事が見当たらなかったので、スプレッドシートに抽出する手順とコードを備忘録的に残しておこうと思います。

(HubSpotユーザーでエンジニアじゃないけどAPI触りたいって人の参考になれば嬉しいです！一ユーザーとしてはやってみた系の記事が増えて勉強させて貰いたいと思っているので、皆さんのやり方も教えてください。）

HubSpot API

まずは元URLをご紹介します。

▼OAuth 2.0 Overview
https://developers.hubspot.com/docs/methods/oauth2/oauth2-overview

▼全体的に参考にした記事

• Create an automated Hubspot custom dashboard with Google Spreadsheet and Data Studio
  https://medium.com/how-to-lean-startup/create-a-hubspot-custom-dashboard-with-google-spreadsheet-and-data-studio-27f9c08ade8d

• How to retrieve your contacts from Hubspot in Google Spreadsheet using App Script
  https://medium.com/@alexisbedoret/how-to-retrieve-your-contacts-from-hubspot-in-google-spreadsheet-using-app-script-f8cb4a827826

処理・手順

まず今回の処理はこんな感じ。

• GoogleスプレッドシートからGASでHubSpot APIを呼び出す
• HubSpotにある「問い合わせ」「商談済み」等、4種類のステータスの企業リストを取得する
• この時、”フィルター付きコンタクト”や”取引”から企業名を抜き出すのはAPIの内容的に厳しいので、先にHubSpot内リスト機能で同じ動的リストを作っておきそれを”リスト取得のAPI”を使って企業名を取ってくる
• 使いやすいように別のメンバーがボタンひとつで最新リストを提供できるようにし、もともとは企業名リストが欲しいので、表記揺れを補正した上で重複削除にて企業リストとする処理をしておく
• 最終的にはこの「リスト更新」ボタンを押して、A〜D列にそれぞれ4つのリストの企業名が重複削除・補正された上で入るようにする（もちろんスプレッドシートなので決まった時間に自動更新でもOK！）

大まかな手順はこんな感じ。短い！

• Googleスプレッドシートでリクエストを投げるように設定！
• HubSpotに認証させる！
• APIから値ゲット！

具体的に

まずHubSpot APIについてスーパーざっくり書くと、コンタクトや取引などから決まった値を取得（逆にHubSpotに登録も）することができます。

認証は2パターンあって（間違ってたらごめんなさい）それぞれの手順はざっくりこんな感じ。今回は2で行くので、2の手順から追っていけばOK。

1, APIキー
URLの後ろにパラメータとしてAPIキーをくっつけて投げればOK。APIキーもHubSpot画面から簡単に取得できてめちゃ楽。まあドキュメントにある通りセキュリティ的にも定期実行とかにはあまりオススメしない。

2, Outh認証してアクセストークン発行
まず開発者ページで適当にアプリを作成。するとこういう画面が出るので、（この画面の名称で）”顧客ID”と”顧客の秘密”(笑)をメモ。

次にリクエストを投げるスプレとアプリを作成していきます。
まずはスプレのスクリプトエディタからGASでHubSpotにリクエストを投げるアプリを作成します。

次に、先に2つほどライブラリを入れておきます。[リソース]→[ライブラリ]に行き、それぞれ下の文字列を検索窓に入れてそれぞれ最新ver.を入れておきます。

• Outh用のライブラリ1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF
• 転置用ライブラリ（今回転置したかったので。）
  M3i7wmUA_5n0NSEaa6NnNqOBao7QLBR4j

そしてこのコードを書きます。（雑ですみません。）

自分の変数として入れないといけないのは以下。list_numは増やしても減らしてもOK！

・CLIENT_ID = '自分のクライアントID'
・CLIENT_SECRET = '自分のクライアントシークレット'
・list_dict = {"名前（例えば問い合わせリストとか）":'リストID',"名前":'リストID',"名前":'リストID',"名前":'リストID'}

// 【コンタクトのリストから企業名一覧を取得する】

// 各値のセット
var CLIENT_ID = '自分のクライアントID'; 
var CLIENT_SECRET = '自分のクライアントシークレット'; 
var SCOPE = 'contacts';
var AUTH_URL = 'https://app.hubspot.com/oauth/authorize';
var TOKEN_URL = 'https://api.hubapi.com/oauth/v1/token';

// ライブラリから。各種値をゲット・セット（実は中身あまり分かってない）
function getService() {
 return OAuth2.createService('hubspot')
 .setTokenUrl(TOKEN_URL)
 .setAuthorizationBaseUrl(AUTH_URL)
 .setClientId(CLIENT_ID)
 .setClientSecret(CLIENT_SECRET)
 .setCallbackFunction('authCallback')
 .setPropertyStore(PropertiesService.getUserProperties())
 .setScope(SCOPE);
}

//　以下2つの関数で初回認証を行う。参考：https://medium.com/how-to-lean-startup/create-a-hubspot-custom-dashboard-with-google-spreadsheet-and-data-studio-27f9c08ade8d
function authCallback(request) {
  var service = getService();
  var authorized = service.handleCallback(request);
  if (authorized) {
     return HtmlService.createHtmlOutput('Success!');
  } else {
     return HtmlService.createHtmlOutput('Denied.');
  }
}
function authenticate() {
  var service = getService();
  if (service.hasAccess()) {
  } else {
     var authorizationUrl = service.getAuthorizationUrl();
     Logger.log('Open the following URL and re-run the script: %s',authorizationUrl);
  }
}

// スプレからこの関数を実行する。他関数の実行を行う。
function exeFunc(){
 // スプレ上のヘッダーになる名前と、取得したいリストID　（HuBspotのリストURLの最後の数字列）
 var list_dict = {"名前（例えば問い合わせリストとか）":'リストID',"名前":'リストID',"名前":'リストID',"名前":'リストID'}
 var sheet_name = "参照シート(HubSpot各ステージ)";
 var colnum = 0;
 deleteResults(sheet_name); //更新時にシートの内容を消しておく。
 for (key in list_dict){
   each_list_data_result = getContactList(list_dict[key], key);
   colnum ++;
   writeResults(sheet_name, [each_list_data_result],colnum);  //setValues関数使う用に[]を重ねる。
 };
};

// 諸々の処理を行う関数。
function getContactList(list_num, lavel_name) {
 var service = getService();
 var options = {headers: {'Authorization': 'Bearer ' +   service.getAccessToken()}};
 var numResults = 0;  
 var each_list_data = new Array();
 each_list_data.push(lavel_name);
 
 // 1リクエストでのデータ数に制限があるのでページがなくなるまで行う。
 var go = true;
 var hasMore = false;
 var offset = 0;  
 while (go)
 {
   var url_query = 'https://api.hubapi.com/contacts/v1/lists/'+ list_num +'/contacts/all';
   if (hasMore)
   {
     //各パラメータセット（他のオプションはドキュメント参照）
     url_query += "?count=100"+　"&vidOffset=" + offset;
   }
   var response = UrlFetchApp.fetch(url_query, options).getContentText();
   response = JSON.parse(response);
   hasMore = response['has-more'];
   offset = response['vid-offset'];
   
   if (!hasMore)
   {
     go = false;
   }
   // hasOwnPropertyで探してなければ”NA”を返す。後で重複を消すので表記ゆれを綺麗にしておく。
   response.contacts.forEach(function(item) {
     var company = (item.properties.hasOwnProperty('company')) ? item.properties.company.value.replace(/株式会社|（株）| | |Inc.|NPO法人|有限会社|（株）|(有)|㈱|()|,/g, ""): "NA";
     each_list_data.push(company); 
     //データ数を追加
     numResults++;
   });
 }  
 var dd_each_list_data = each_list_data.filter(function (x, i, self) {
   return self.indexOf(x) === i;
 });
 return dd_each_list_data ;
 
}
// スプレへの書き込み関数を定義
function writeResults(sheetName, results, colnum)
{
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName(sheetName);
 var setRange = sheet.getRange(1, colnum, results[0].length, results.length);
 var _ = Underscore.load();
 var transData = _.zip.apply(_, results);
 setRange.setValues(transData);
}

// シートのデータリセット関数を定義
function deleteResults(sheetName)
{
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName(sheetName);
 sheet.clear();
}

ここから実際に実行していきます。

まずは認証。「authenticate」を選択し左横の再生ボタン的なやつで実行。

ログに認証URLを残すスクリプトなので、実行した後”Command+Enter”でログを表示します。そうするとこういうポップアップが表示されます。

このURL（メッセージ以降のhttps:~）をコピペしてアクセス。飛ぶ先でHubSpotから諸々出てくるのでOKを押す。その後別関数で表示させる”Succsess!”のページに飛ばされればOK！（ココらへんは冒頭の記事のまんまです。）

!

そのまま「exeFunc」を実行しても良いが他メンバーも使うので実行トリガーを表に持ってきましょう。表に戻って関数実行のボタンを作ります。[挿入]→[図形絵画]から適当に図形を作ってその上で右クリック「スクリプトの割当」で「exeFunc」を入力。

picture_pc_7e5913b7909040a2f07aee12d3d8cc87.png

もちろん定期実行もOK。分かりやすい記事がたくさんあるのでご参考に。
https://tonari-it.com/gas-trigger-set/

押す。（ちょっと時間かかる）

完了。

ここから=IF(COUNTIF('このリスト'!A:A,$A:$A)>0,1,0)的な関数やVLOOKUPなんかを使って大元の全体リストと付け合わせればOK！