GASでzendeskからスプレッドシートに情報を出力する

GASでzendeskからスプレッドシートに情報を出力する

GAS(Goolgle App Script)でzendeskから情報を取得して、スプレッドシートに記録するコードを書きました。

なんでやったの?

ある業務で redash を使って進捗管理をしていたのですが、zendesk上にのみ顧客対応ステータスの情報が登録されていました。

redashから検索keyをコピーしてzendeskで検索して…っていうのを毎日やっててイヤになったので、redashでzendeskの情報を出せるようにしてやりました。

完成したやつ

先にコードを載せておきます。

zendeskからチケットIDとカスタムフィールド3つを取得して、スプレに出力しています。

function zendeskGet() {

  //検索条件をURLエンコードしてHTTPリクエストするための情報に変換
  var query = '検索内容をここに入れる'; //検索内容
  var res1 = encodeURI(query); //検索クエリ
  var url = 'https://サブドメイン名.zendesk.com/api/v2/search.json?query=' + res1;

  //認証用情報
  const TOKEN = 'トークンを入れる';
  const EMAIL = 'メールアドレス';

  var data = {
      'email': EMAIL,
      'token': TOKEN
  };

  //dataをzendesk認証用に置き換え
  var options = {
      'method': 'get',
      'headers': {
          'Authorization': "Basic " + Utilities.base64Encode(Utilities.newBlob(data.email + '/token:' + data.token).getBytes()),
          'contentType': 'application/json',
          'Accept': 'application/json'
      }
  };

  //urlと認証情報をマージ
  var response = UrlFetchApp.fetch(url, options);

  //json形式で取得
  var json = JSON.parse(response.getContentText("UTF-8"));

  //jsonを配列に入れる
  var zendeskDataRow = [];
  zendeskDataRow.push(json);
  var zendeskData = zendeskDataRow[0].results

  //各データを格納する二次元配列の箱を用意
  var tickets = [];
  var eddReqIds = [];
  var eddStatuses = [];
  var kycKinds = [];

  //取得したいzendeskのカスタムフィールドのID
  var eddReq = 'フィールドIDを入れる';
  var eddStatus = 'フィールドIDを入れる';
  var kycKind = 'フィールドIDを入れる';

	//チケットIDと指定のカスタムフィールドの値を取得
  for(var j=0;j<zendeskData.length;j++){
    tickets.push([zendeskData[j].id]);
    for(var i=0;i<zendeskData[j].custom_fields.length;i++){
      var customFieldId = zendeskData[j].custom_fields[i].id
      if(customFieldId == eddStatus){
        eddStatuses.push([zendeskData[j].custom_fields[i].value]);
      }
      if(customFieldId == kycKind){
        kycKinds.push([zendeskData[j].custom_fields[i].value]);
      }
      if(customFieldId == eddReq){
        eddReqIds.push([zendeskData[j].custom_fields[i].value]);
      }
    }

  };

	//スプレに貼り付け
  var sheet = SpreadsheetApp.openById("シートIDを入れる").getSheetByName("シート名を入れる"); //書き込むスプレを指定
	var lastLow = sheet.getLastRow();
  var clearRange = sheet.getRange(2, 1, lastLow, 4) 
  var datalastRow = eddReqIds.length;
	
	clearRange.clear();

  sheet.getRange(2, 1, lastRow, 1).setValues(eddReqIds);
  sheet.getRange(2, 2, lastRow, 1).setValues(eddStatuses);
  sheet.getRange(2, 3, lastRow, 1).setValues(kycKinds);
  sheet.getRange(2, 4, lastRow, 1).setValues(tickets);


}

どうやったの?

redash でスプレッドシートの情報を取得できる環境が整っていたので、zendesk→スプレッドシート→redash を目指しました。遠回りになりますが、GASでzendeskからスプレに出力できるのは分かっていたので、今の知識の範囲でやれる方法を選択しました。

ステップはこんな感じです。

  1. zendeskで検索条件を決める
  2. URLエンコードする
  3. zendeskにアクセスしてjsonを取得する
  4. jsonを整形する
  5. スプレッドシートに貼り付ける

zendeskで検索条件を決める

まずzendesk Supportで検索条件を設定します。 zendesk上で検索がうまくできているか確認できるのはすごくいいですよね。検索条件はいろいろ設定できるのでzendeskのドキュメントを参考にしてください。

検索条件の参考:

こちらは例です。

image

この検索条件を + で繋ぎます。上記の例だと、aaa+updated>"2022-11-01"+updated<"2022-11-19"+status:solved ですね。 これを基にzendeskから情報を取得することになります。(次のステップに出てくる ver query = 部分に入れるもの)

ちなみに、ほしい情報が取得できているかはブラウザで以下のように入力すればjsonが確認できます。https://SUBDOMAIN.zendesk.com/api/v2/search.json?query=ここに検索条件を入れる

つまり上記の例だとこれですね。 https://SUBDOMAIN.zendesk.com/api/v2/search.json?query=aaa+updated>"2022-11-01"+updated<"2022-11-19"+status:solved

なお、 SUBDOMAIN 部分は契約しているサブドメイン名を入れてください。サブドメイン名はzendesk supportを開いたときのURLでわかります。 https://ここがサブドメイン.zendesk.com

URLエンコードする

検索条件URLに使える文字の組み合わせに変換します。

encodeURI関数(encodeURI(URI) )を使います。

//検索条件をURLエンコードしてHTTPリクエストするための情報に変換
var query = '検索条件をここに入れる'; //検索内容
var res1 = encodeURI(query); //検索クエリ
var url = 'https://サブドメイン名.zendesk.com/api/v2/search.json?query=' + res1;

ちなみにURLとURIの違いが気になる方はこちらをどうぞ。

出典:「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典

zendeskにアクセスしてjsonを取得する

認証情報を設定してアクセスします。

//認証用情報
const TOKEN = 'トークンを入れる';
const EMAIL = 'メールアドレス';

var data = {
    'email': EMAIL,
    'token': TOKEN
};

//dataをzendesk認証用に置き換え
var options = {
    'method': 'get',
    'headers': {
        'Authorization': "Basic " + Utilities.base64Encode(Utilities.newBlob(data.email + '/token:' + data.token).getBytes()),
        'contentType': 'application/json',
        'Accept': 'application/json'
    }
};

//urlと認証情報をマージ
var response = UrlFetchApp.fetch(url, options);

//json形式で取得
var json = JSON.parse(response.getContentText("UTF-8"));

ちなみに、zendeskのAPIトークンは、[zendesk管理センター]>[Zendesk API]>[APIトークンを追加]で取得できます。

zendesk developers

jsonを整形する

jsonを配列に入れちゃいます。

results のなかにチケットIDやカスタムフィールドの情報が入ってます。変数 zendeskData としておきます。

//jsonを配列に入れる
var zendeskDataRow = [];
zendeskDataRow.push(json);
var zendeskData = zendeskDataRow[0].results

チケットIDは id 、カスタムフィールドは custom_fields のなかの value に値が入ってます。

for文で対象チケットのチケットIDとカスタムフィールドの情報を配列にガーッと入れていきます。

スプレに出力するときは、for文で都度値を入力するよりも、配列に入れて setValues() したほうが負荷が少ないのでおすすめです。

//各データを格納する二次元配列の箱を用意
var tickets = [];
var eddReqIds = [];
var eddStatuses = [];
var kycKinds = [];

//取得したいzendeskのカスタムフィールドのID
var eddReq = 'フィールドIDを入れる';
var eddStatus = 'フィールドIDを入れる';
var kycKind = 'フィールドIDを入れる';

//チケットIDと指定のカスタムフィールドの値を取得
for(var j=0;j<zendeskData.length;j++){
  tickets.push([zendeskData[j].id]);
  for(var i=0;i<zendeskData[j].custom_fields.length;i++){
    var customFieldId = zendeskData[j].custom_fields[i].id
    if(customFieldId == eddStatus){
      eddStatuses.push([zendeskData[j].custom_fields[i].value]);
    }
    if(customFieldId == kycKind){
      kycKinds.push([zendeskData[j].custom_fields[i].value]);
    }
    if(customFieldId == eddReq){
      eddReqIds.push([zendeskData[j].custom_fields[i].value]);
    }
  }

};

スプレッドシートに貼り付ける

スプレにアクセスして指定のセルに貼り付けます。

スプレにアクセスするときは SpreadsheetApp.openById("シートIDを入れる").getSheetByName("シート名を入れる") これが楽だしわかりやすいからよく使ってるんですけど皆さんはどうですか?

clearRange.clear(); これでシートを一度クリアして更新したほうが良いです。当初これをしてなくて、前回の差分が残っている状態になってしまっていました。(情報を上からペーストするだけになるので、前回より情報が少ないと差分が上書きされずに残ってしまう)

//スプレに貼り付け
var sheet = SpreadsheetApp.openById("シートIDを入れる").getSheetByName("シート名を入れる"); //書き込むスプレを指定
var lastLow = sheet.getLastRow();
var clearRange = sheet.getRange(2, 1, lastLow, 4) 
var datalastRow = eddReqIds.length;

clearRange.clear();

sheet.getRange(2, 1, lastRow, 1).setValues(eddReqIds);
sheet.getRange(2, 2, lastRow, 1).setValues(eddStatuses);
sheet.getRange(2, 3, lastRow, 1).setValues(kycKinds);
sheet.getRange(2, 4, lastRow, 1).setValues(tickets);

あとはredashでスプレの情報を取得してクエリを書けば完成です。 トリガーで定期実行しておけば毎朝最新の情報になっています!

image

結果

めちゃくちゃ便利でした。

毎日進捗管理に30分くらい使ってたんですけど、3分で終わるようになりました。

検索条件から取りたい情報を決められるのはすごい便利と思います。コード書けなくても検索条件をスプレに入力してもらえれば、GASで読み取って出力する、みたいなことも実現できるし。 あれ調べたいなーとか、あれ調べといてーというときに、zendeskでの検索条件を決めればいいだけだから、GASが分からない人でも扱えて一次情報の分析も捗りますねこれは!

余談

使わなかったけど今回で学んだこと。

for of

今回は条件分岐のために回数情報が必要だったので使わなかったけどこれは便利。知らなかった。

【GAS】for文の派生for ofとfor in、forEachの処理と違いを解説 | AutoWorker〜Google Apps Script(GAS)とSikuliで始める業務改善入門

前回、Google Apps Script(GAS)でfor文を紹介しましたが、forループには派生したfor of、for in、forEachといった繰り返し処理もあります。 それぞれのループ処理の流れと、3つのfor文の派生形の違いについて解説します。 Google Apps Script(GAS)のスクリプトで、繰り返し処理の基本とも言えるのがfor文です。 繰り返し処理の中には、whileループもありますが、forループは予め何回の繰り返し処理を行うか条件を定めてます。 スプレッドシート上のデータや、配列のデータを処理することが多いGASスクリプトでは、while文よりもfor文を利用することが多いです。 そのため、様々な自動化処理を実行するGASスクリプトではfor文による繰り返し処理が必須と言えます。 前述したfor文に関する解説記事の中で、Google Apps Scriptでのforループの記述方法や使用例を紹介しました。 実はGASのfor文には、その派生からできた3種類の構文が用意されています。 3つともfor文とも近い処理が可能ですが、少し記述方法が異なっていて、利用シーンも異なります。 そこで、①for...of文、②for...in文、③forEachメソッドの3つの利用方法をサンプルコードを使って解説します。 1つ目のfor...of文は以下の形で利用するループ処理です。 for(変数 of 配列){ //配列の要素数だけ繰り返し、毎回変数に配列値を格納 //for ofループで実行する処理を記述 } 配列に含まれるデータを全て処理したい場合に、for of文を用います。 通常のfor文の場合は、「for(let i=0;i<Array.length;i++) 」といった書き方で配列の要素を記述していました。 for...of文を使うと、「for(let i of Array) 」とlengthプロパティで配列の要素数を取得せずに、配列の要素の数だけループ処理が可能です。 実際にGoogle Apps Script(GAS)でfor of文を使った繰り返し処理のサンプルコードを解説します。 function myFunction() { //for ofの処理で使う点数の配列を定義 const scores = [60,70,80,65,90]; //for...ofで配列からひとつずつ要素を取得

【GAS】for文の派生for ofとfor in、forEachの処理と違いを解説 | AutoWorker〜Google Apps Script(GAS)とSikuliで始める業務改善入門

変数のデータ型を確認する方法

おわり。