Zendesk Exploreの圧縮データを解凍してスプレッドシートに同期する

この記事は、Pepabo CS Advent Calendar 2023 の23日目記事として書きました。PepaboのCSについて興味ある方は他の投稿も是非読んでみてください！

ZendeskのデータでExploreでしか取れないデータをスプレッドシートで分析出来るようにしたい！

そんなニーズから、この取り組みを着手しました。

Zendeskには豊富なAPIが用意されており、Zendesk内の多くのデータはAPI経由で自在にデータを取得することが可能です。

しかしながら、AnswerBotに関するデータについては、今のところExploreでしか取得することができません。

もし、Zendesk Explore内で全てのレポーティングが出来ている場合は、この記事に書かれているようなニーズは発生しません。

しかし、生データを取得し、スプレッドシートなどで別途管理を希望する方向けに、Exploreで取得できるデータをスプレッドシートに展開する方法について解説します。

想定読者

• Zendeskユーザー
• Zendesk Exploreのデータをスプレッドシートで管理出来るようにしたい方

Answer Botで取得出来るExploreのデータについて

Exploreでは、ZendeskのAnswer Bot関係の分析に必要なほぼ全てのデータの取得が可能です。

以下の様なデータを日次で取得し、スプレッドシートでも閲覧出来るようにすることを目指します。

実装のレシピ

• Exploreのレポート作成
• Exploreのダッシュボード作成
• スケジュール設定
• メールで添付されたファイルを解凍し、スプレッドシートに貼り付け

Exploreのレポート作成

取得を希望するデータを、表形式のレポートで作成していきます。

実際に取得したい項目をメトリックや行に追加し、「表」の表示タイプで可視化を行い保存します。

Exploreのダッシュボード作成

作成したレポートを使って、ダッシュボードを一つ作成します。ダッシュボードは本来複数のレポートを一つの画面に表示させられる機能になりますが、今回はあくまでCSVファイルとしてデータを取得するために利用するので、1レポートにつき1つのダッシュボードを作成するようにします。

スケジュール設定

ダッシュボードを作成したら、ダッシュボードのメニューにある「スケジュール」に進み、自身の登録アカウントのメールアドレス宛にcsvファイルを送信するスケジュール設定を行います。

スケジュール設定を行うことで、自身のメールアドレス宛に、zipファイル付きのメールが届くことを確認します。（csvファイルは、このzipファイルの中に保存されています）

メールで添付されたファイルを解凍し、スプレッドシートに貼り付け

最後に、メールに添付されたzipファイルを解凍し、中身のcsvファイルをGoogleDriveにロードした後、スプレッドシートに展開するGASを実行します。

GASの内容

function searchZipMail(){
    // Gmailから特定の件名を持つメールを検索して添付ファイルを取得
    var title = "配信スケジュール: AnswerBot KPI ALL"; // 件名設定
    var searchGmail = GmailApp.search(title, 0, 10); // searchメソッドでメール検索
    var threadsGmail = GmailApp.getMessagesForThreads(searchGmail); // メールスレッドのメッセージを取得
    var zipAttachments = threadsGmail[0][0].getAttachments(); // 最新のメールの添付ファイルを取得
    Logger.log(threadsGmail[0][0].getSubject());  // 取得したメールの件名をログに表示

    // 取得したZIP添付ファイルをGoogle Driveに保存して解凍
    var attachmentsNum = zipAttachments.length; // 添付ファイルの数を取得
    for (var k = 0 ; k < attachmentsNum; k++) {
      var unzipFile = Utilities.unzip(zipAttachments[k]); // ZIPファイルを解凍
      var unzipFileNum = unzipFile.length; // 解凍後のファイル数を取得
    }
    var folder = DriveApp.getFolderById("YOUR_FOLDER_ID_HERE"); // 保存するフォルダーを指定（YOUR_FOLDER_ID_HEREにフォルダIDを設定）
    for (var q = 0 ; q < unzipFileNum; q++) {
      folder.createFile(unzipFile[q]); // 解凍したファイルを指定フォルダに保存
    }

    // 解凍したCSVファイルをGoogleスプレッドシートにインポート
    var files = folder.getFiles(); // フォルダ内のファイルを取得
    var file = files.next(); // 最初のファイルを取得
    var fileId = file.getId(); // ファイルIDを取得
    var blob = DriveApp.getFileById(fileId).getBlob(); // ファイルのBlobを取得
    var csv = blob.getDataAsString(); // Blobから文字列としてCSVデータを読み込み
    var values = Utilities.parseCsv(csv, ';'); // CSVデータを2次元配列に変換
    var svalues = values.splice(1, values.length); // ヘッダー行を除外

    var ss = SpreadsheetApp.getActiveSpreadsheet(); // 現在のスプレッドシートを取得
    var sheet = ss.getSheetByName('answer_bot'); // 特定のシートを取得
    sheet.getRange(3, 1, svalues.length, svalues[0].length).setValues(svalues); // スプレッドシートにデータを書き込み

    // 処理したファイルを「使用済み」フォルダに移動
    var moveDir = DriveApp.getFolderById('YOUR_ARCHIVE_FOLDER_ID_HERE'); // 移動先のフォルダを指定（YOUR_ARCHIVE_FOLDER_ID_HEREにフォルダIDを設定）
    file.moveTo(moveDir); // ファイルを移動
}

GASの説明

このGoogle Apps Script (GAS) のコードは、Gmailから特定の添付ファイルを取得し、Googleドライブに保存してCSVデータをGoogleスプレッドシートにインポートするためのものです。以下、コードの概要を説明します。

1. Gmailから添付ファイルを取得:
• GmailApp.searchメソッドを使い、特定の件名（ここでは "配信スケジュール: AnswerBot KPI ALL"）を持つメールを検索します。
• getMessagesForThreadsメソッドを用いて、検索したメールスレッド内のメッセージを取得します。
• 最新のメッセージからZIP添付ファイルを取得します。
2. 添付ファイルをDriveに保存して解凍:
• 取得したZIP添付ファイルをループ処理で処理します。
• Utilities.unzipメソッドでZIPファイルを解凍し、解凍したファイルをGoogleドライブの指定フォルダに保存します。
3. CSVファイルをGoogleスプレッドシートにインポート:
• 解凍したCSVファイルをGoogleドライブから取得し、その内容を文字列として読み込みます。
• Utilities.parseCsvメソッドを使って、CSVデータを2次元配列に変換します。
• この配列をGoogleスプレッドシートの特定のシートに書き込みます。
4. 処理済みファイルの管理:
• 処理が完了したファイルを、「使用済み」フォルダに移動させます。

上記の設計で、スプレッドシートにCSVファイルの内容が展開されることをお確かめください。

お疲れ様でした！！

share this page