GASを使ってZendeskの情報を片っ端からスプレッドシートへ転記する
Zendesk初心者ですが、今回は Zendeskの各種設定と上手に付き合っていくために情報整理していて、GASを使ったりしていたので、その辺を書いています。
背景
そもそもZendeskはカスタマイズや簡単で、工夫次第でかなりシステムを構築できます。
一方で、作りこみすぎるとメンテナンスが大変で、他メンバーはもちろん、自分でも「なるほど分からん」状態になってしまう気配をひしひしと感じています（もちろんZendeskに限らずですが）。
せめてGoogleスプレッドシート上で設定やらの情報が整理できていれば、何がどうなっているのかは把握できそうだと思い、Zendesk API を使ってみることにしました。
やりたいこと
Zendesk で設定したカスタム値をスプレッドシート上で管理したい
Googleスプレッドシートの用意
前置きはこれくらいで、さっそく準備ですが、まずスプレッドシートから設定値を取得することにします。
configシート
macrosシート
ticketsシート
articlesシート
シートへのアクセス権は特定メンバーに制限しましょう。
GASのコード
最初にシートから設定値などをconfigシートから取得
/*
SHEET_ID : スプレッドシート自体のID
TOKEN : Zendesk APIの Access Token
EMAIL : 自分のメアド
BASE_URL : ZendeskのサブドメインまでのURL
USER_ID : Zendeskの自分のUser ID
BRAND_ID : ZendeskのブランドID
*/
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const SHEET_ID = spreadsheet.getId();
const ss = SpreadsheetApp.openById(SHEET_ID);
// Zendesk設定値
const configSheet = ss.getSheetByName("config");
const sp = PropertiesService.getScriptProperties();
// Zendesk設定値
const ZENDESK_TOKEN = sp.getProperty('TOKEN');
const EMAIL = configValues[1];
const BASE_URL = configValues[2];
const USER_ID = configValues[3].toString();
const BRAND_ID = configValues[4].toString();各種オプションを取得する関数を定義
function getOptions() {
const options = {
"method": "get",
"contentType": "application/json",
"headers": {
"Authorization": "Basic " + Utilities.base64Encode(EMAIL + "/token:" + TOKEN)
}
}
return options;
}API経由でマクロ情報を取得するための関数を定義
function fetchMacroValues() {
const options = getOptions();
const url = BASE_URL + "/api/v2/macros.json";
const response = UrlFetchApp.fetch(url, options);
console.log("/api/v2/macros.json ："+response.getResponseCode())
const values = JSON.parse(response.getContentText("UTF-8"));
// 100件以上(next_page)の取得を可能にするためにvaluesを配列に格納する
let valuesArray = [];
valuesArray.push(JSON.parse(response.getContentText("UTF-8")));
let pageCounter = 2
while(values.next_page !== null){
const url = BASE_URL + "/api/v2/macros.json?page="+pageCounter;
const response = UrlFetchApp.fetch(url, options);
console.log("/api/v2/macros.json?page="+pageCounter + " ："+response.getResponseCode())
valuesArray.push(JSON.parse(response.getContentText("UTF-8")));
pageCounter += 1;
const next_page_values = JSON.parse(response.getContentText("UTF-8"));
if (next_page_values.next_page === null) {
break;
}
}
return valuesArray;
}取得した情報をスプレッドシートへ転記する関数を定義
レスポンスの中身はこちらのリファレンスを参照。
マクロ本文がHTMLで返ってくるのでタグを除去しています。
function setMacroValues(valuesArray) {
const sh = ss.getSheetByName("macros");
clearSheetContents(sh);
let rowCounter = 2
for (const value of valuesArray) {
value.macros.forEach(val => {
sh.getRange(rowCounter, 1).setValue(today);
sh.getRange(rowCounter, 2).setValue(val.id);
sh.getRange(rowCounter, 3).setValue(val.title);
sh.getRange(rowCounter, 4).setValue(val.description);
// <br>を改行に変換後にHTMLタグを除去
sh.getRange(rowCounter, 5).setValue(val.actions[0].value
.replace(/<br>/g, "\n")
.replace(/<("[^"]*"|'[^']*'|[^'">])*>/g,''));
// agentがアクセス可能なurlに変換
sh.getRange(rowCounter, 6).setValue(val.url
.replace(".json", "")
.replace("/api/v2/", "/admin/workspaces/agent-workspace/"));
sh.getRange(rowCounter, 7).setValue(val.active);
sh.getRange(rowCounter, 8).setValue(val.restriction);
rowCounter += 1;
});
}
}※あらかじめ転記する「macros」シートを作成しています
シートの初期化の関数を定義
転記の前にシートを初期化します。待機はただのおまじないです。
function clearSheetContents(sh) {
const lastRow = sh.getLastRow();
const lastColum = sh.getLastColumn();
sh.getRange(2,1,lastRow,lastColum).clearContent();
Utilities.sleep(3000);
}実行確認のためのポップアップの関数を定義
関数のメニュー化や実行ボタン化をする際に間違って押しちゃうことがあっても安心のおまじないです。
function isExecutable() {
return Browser.msgBox("実行しても良いですか？", Browser.Buttons.OK_CANCEL);
}GASのコード全体
// Googleスプレッドシートの設定値
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const SHEET_ID = spreadsheet.getId();
const ss = SpreadsheetApp.openById(SHEET_ID);
// Zendesk設定値
const configSheet = ss.getSheetByName("config");
const configValues = configSheet.getRange("B2:B14").getValues();
const ZENDESK_TOKEN = configValues[0];
const EMAIL = configValues[1];
const BASE_URL = configValues[2];
const USER_ID = configValues[3].toString();
const BRAND_ID = configValues[4].toString();
// マクロ情報をシートへ連携
function getMacroValuesToSheet() {
const result = isExecutable();
if (result === "ok") {
const macroValues = fetchMacroValues();
setMacroValues(macroValues);
} else if (result === "cancel") {
Browser.msgBox("キャンセルしました");
}
}
// API経由でMacro情報を取得
function fetchMacroValues() {
const options = getOptions();
const url = BASE_URL + "/api/v2/macros.json";
const response = UrlFetchApp.fetch(url, options);
console.log("GET /api/v2/macros.json ："+response.getResponseCode())
const values = JSON.parse(response.getContentText("UTF-8"));
return values
}
// シートへ転記
function setMacroValues(values) {
const sh = ss.getSheetByName("macros");
clearSheetContents(sh);
let rowCounter = 2
values.macros.forEach(value => {
sh.getRange(rowCounter, 1).setValue(TODAY);
sh.getRange(rowCounter, 2).setValue(value.id);
sh.getRange(rowCounter, 3).setValue(value.title);
sh.getRange(rowCounter, 4).setValue(value.description);
// <br>を改行に変換後にHTMLタグを除去
sh.getRange(rowCounter, 5).setValue(value.actions[0].value
.replace(/<br>/g, "\n")
.replace(/<("[^"]*"|'[^']*'|[^'">])*>/g,''));
// agentがアクセス可能なurlに変換
sh.getRange(rowCounter, 6).setValue(value.url
.replace(".json", "")
.replace("/api/v2/", "/admin/workspaces/agent-workspace/"));
sh.getRange(rowCounter, 7).setValue(value.active);
sh.getRange(rowCounter, 8).setValue(value.restriction);
rowCounter += 1;
});
}
// Optionを取得
function getOptions() {
const options = {
method: "get",
contentType: "application/json",
headers: {
Authorization: "Basic " + Utilities.base64Encode(EMAIL + "/token:" + TOKEN),
},
};
return options;
}
// シートを初期化
function clearSheetContents(sh) {
const lastRow = sh.getLastRow();
const lastColum = sh.getLastColumn();
sh.getRange(2,1,lastRow,lastColum).clearContent();
Utilities.sleep(3000);
}
// 実行確認のポプアップ
function isExecutable() {
return Browser.msgBox("実行しても良いですか？", Browser.Buttons.OK_CANCEL);
}・・・こんな感じで、たくさんあるマクロの情報がスプレッドシートへ転記されます。
補足
マクロが20～30個程度なら、全く問題なさそうですが、100個近くになってくると、割と改修が大変になってくるはずですが、こうして整理しておくと工数削減ができそうです。
Zendesk Ticket 情報を取得
もちろん、チケットにも使えます。こちらの方が用途があるかもしれません。エンタープライズプランなど、Advanced Search を利用する場合は、そちらで良しなにやっている方が多いかもしれませんが。
// 取得したいフィールドがあれば
const FIELD_01 = "<field_id_01>"
const FIELD_02 = "<field_id_02>"
const FIELD_03 = "<field_id_03>"
// 取得したいフィールドがあれば
const FORM_IDS = {
"<form_id_01>": "<form_title_01>",
"<form_id_02>": "<form_title_02>",
"<form_id_03>": "<form_title_03>",
}
// 作成済みチケット情報を取得して転記
function getTicketValuesToSheet(){
const result = isExecutable()
if (result==="ok"){
const ticketValues = fetchTicketValues();
setTicketValues(ticketValues);
} else if (result==="cancel"){
Browser.msgBox("キャンセルしました")
}
}
// API経由でTicket情報を取得
function fetchTicketValues() {
const options = getOptions();
const url = BASE_URL + "/api/v2/tickets.json";
const response = UrlFetchApp.fetch(url, options);
console.log("GET /api/v2/tickets.json ："+response.getResponseCode());
let valuesArray = [];
const values = JSON.parse(response.getContentText("UTF-8"));
valuesArray.push(JSON.parse(response.getContentText("UTF-8")));
let pageCounter = 2
while(values.next_page !== null){
const url = BASE_URL + "/api/v2/tickets.json?page="+pageCounter;
const response = UrlFetchApp.fetch(url, options);
console.log("/api/v2/tickets.json="+pageCounter + " ："+response.getResponseCode())
valuesArray.push(JSON.parse(response.getContentText("UTF-8")));
pageCounter += 1;
const next_page_values = JSON.parse(response.getContentText("UTF-8"));
if (next_page_values.next_page === null || pageCounter > 100) {
break;
}
}
return valuesArray;
}
// 取得した情報をスプレッドシートへ転記する
function setTicketValues(valuesArray) {
const sh = ss.getSheetByName("tickets");
clearSheetContents(sh);
const _START_DATE = configSheet.getRange(2, 5).getValue();
const _END_DATE = configSheet.getRange(2, 6).getValue();
const START_DATE = Utilities.formatDate(new Date(_START_DATE), "Asia/Tokyo", "yyyy-MM-dd")
const END_DATE = Utilities.formatDate(new Date(_END_DATE), "Asia/Tokyo", "yyyy-MM-dd")
let rowCounter = 2
for (const value of valuesArray) {
value.tickets.forEach(val => {
const tmpDate = Utilities.formatDate(new Date(val.created_at), "Asia/Tokyo", "yyyy-MM-dd")
if(tmpDate > START_DATE && tmpDate <= END_DATE){
sh.getRange(rowCounter, 1).setValue(val.subject);
// <br>を改行に変換後にHTMLタグを除去
sh.getRange(rowCounter, 2).setValue(val.description
.replace(/<br>/g, "\n")
.replace(/<("[^"]*"|'[^']*'|[^'">])*>/g,''));
// agentがアクセス可能なurlに変換
sh.getRange(rowCounter, 3).setValue(val.url
.replace(".json", "")
.replace("/api/v2/", "/agent/"));
sh.getRange(rowCounter, 4).setValue(val.id)
sh.getRange(rowCounter, 5).setValue(Utilities.formatDate(new Date(val.created_at), "Asia/Tokyo", "yyyy-MM-dd"));
for (let i = 0; i < val.custom_fields.length; i++) {
if (val.custom_fields[i].id == FIELD_01){
sh.getRange(rowCounter, 6).setValue(val.custom_fields[i].value);
} else if (val.custom_fields[i].id == FIELD_02){
sh.getRange(rowCounter, 7).setValue(val.custom_fields[i].value);
} else if (val.custom_fields[i].id == FIELD_03){
sh.getRange(rowCounter, 8).setValue(val.custom_fields[i].value);
}
sh.getRange(rowCounter, 9).setValue(FORM_IDS[val.ticket_form_id]);
sh.getRange(rowCounter, 10).setValue(val.tags[0]);
sh.getRange(rowCounter, 11).setValue(val.tags[1]);
sh.getRange(rowCounter, 12).setValue(val.tags[2]);
sh.getRange(rowCounter, 13).setValue(val.via.source.from.address);
sh.getRange(rowCounter, 14).setValue(val.requester_id);
rowCounter += 1;
}
});
}
}取得する件数が多い場合、6分間の実行時間を超過してしまうかもしれないので、期間を絞って取得してください。
・・・とGASを利用することでGoogleスプレッドシートへ情報を転記できるという内容でした。
チケットについても、Googleスプレッドシートにとりあえず転記しておけば、関数で grep したり抽出や集計がササっとできる気がしています（Exploreを理解していないだけの可能性が高い）
追記
Zendesk GuideのArticle情報を取得（ヘルプページ）
ヘルプページの記事の取得も可能です。Zendesk Guideには直接CSVエクスポートする機能が見当たりませんでした（あったらすみません）。一括で取得する場合は以下のようにします。
function getArticlesToSheet(){
const result = isExecutable()
if (result==="ok"){
const articleValues = fetchArticles()
setArticleValues(articleValues)
} else if (result==="cancel"){
Browser.msgBox("キャンセルしました")
}
}
function fetchArticles() {
const options = getOptions();
const url = BASE_URL + "/api/v2/help_center/ja/articles";
const response = UrlFetchApp.fetch(url, options);
Logger.log("/api/v2/help_center/ja/articles："+response.getResponseCode())
let valuesArray = [];
const values = JSON.parse(response.getContentText("UTF-8"));
valuesArray.push(JSON.parse(response.getContentText("UTF-8")));
let pageCounter = 2
while(values.next_page !== null){
const url = BASE_URL + "/api/v2/help_center/ja/articles?page="+pageCounter;
const response = UrlFetchApp.fetch(url, options);
Logger.log("/api/v2/help_center/ja/articles="+pageCounter + " ："+response.getResponseCode())
valuesArray.push(JSON.parse(response.getContentText("UTF-8")));
pageCounter += 1;
const next_page_values = JSON.parse(response.getContentText("UTF-8"));
if (next_page_values.next_page === null || pageCounter > 20) {
break;
}
}
return valuesArray;
}
function setArticleValues(valuesArray) {
const sh = ss.getSheetByName("airticles");
clearSheetContents(sh);
let rowCounter = 2
for (const value of valuesArray) {
value.articles.forEach(val => {
if(val.body !== null){
sh.getRange(rowCounter, 1).setValue(val.draft);
sh.getRange(rowCounter, 2).setValue(val.id);
sh.getRange(rowCounter, 3).setValue(val.title);
sh.getRange(rowCounter, 4).setValue(val.body.replace(/<("[^"]*"|'[^']*'|[^'">])*>/g,''));
// agentがアクセス可能なurlに変換
sh.getRange(rowCounter, 5).setValue(val.url
.replace("/ja/articles", "")
.replace("/api/v2/help_center/", "/knowledge/articles/")
.replace(".json", `/ja?brand_id=${BRAND_ID}`));
rowCounter += 1;
}
});
}
}Articleは20件(30件?)までしか1度に取得できないため、以下のようにPage数をカウントさせて実行します。
pageCounter += 1;
const next_page_values = JSON.parse(response.getContentText("UTF-8"));
if (next_page_values.next_page === null || pageCounter > 20) {
break;
}また取得するURLをGuide編集ページにしたいので以下のようにZendeskエージェントがアクセス可能なURLに置換します。
// agentがアクセス可能なurlに変換
sh.getRange(rowCounter, 5).setValue(val.url
.replace("/ja/articles", "")
.replace("/api/v2/help_center/", "/knowledge/articles/")
.replace(".json", `/ja?brand_id=${BRAND_ID}`));次回
マクロの話に戻りますが、こうなってくると更新（update）もGASでやりたくなりますね。長くなったので次回でGASを使ってマクロを更新してみます