Stanby Tech Blog

求人検索エンジン「スタンバイ」を運営するスタンバイの開発組織やエンジニアリングについて発信するブログです。

トップ > Other > kickflowとスプレッドシートの連携をZapierからGASに変更したところ管理が楽になった話

kickflowとスプレッドシートの連携をZapierからGASに変更したところ管理が楽になった話

Other

はじめに

こんにちは、コーポレートITグループの西本です。

コーポレートITグループでは、社員のPCの管理や、各種ライセンス管理をはじめ、社内で利用するサービスの導入やサービス間の連携など幅広く業務をおこなっています。

その中で今回は、タイトルにもあるように、kickflowというワークフローシステムとGoogleスプレッドシートを GoogleAppsScript で連携させた話をご紹介いたします。

背景

スタンバイでは、購入稟議や支払い申請を管理するためのワークフローシステムとしてkickflowというサービスを利用しています。

https://kickflow.com/

kickflowで作成された各種リクエストはチケットという単位で管理され、ベルトコンベアの上を流れる出来立てのパンのように、各部署のチェックをもらいながら完了へと進んでいき、すべてのチェックの完了後、リクエストに応じて各部署で物品の購入や、支払い処理などに進んでいきます。

それぞれのチケットは、kickflowのサイト上で1つずつ確認ができますが、業務を効率的に進める上で、完了したチケットを一覧にしてGoogleスプレッドシートへの書き出しをしてほしいという要望があり、以前までZapierというノーコードで複数のサービスを連携させることのできるサービスを利用し、kickflowで完了したチケットのデータをスプレッドシートに書き込んでいました。

Zapier | Automation that moves you forward

何が課題だったのか

Zapierはプログラミングに関しての知識がなくても各種サービスを連携させることができる便利なサービスなのですが、この連携についてはいくつかの問題点がありました。

  1. 書き込み時にエラーが起こった場合、対象のチケットデータの再取得に手間がかかる。
    Zapierは基本的に指定したイベントをトリガーとして処理が開始します。kickflowでチケットが承認されると、そのタイミングでwebhookを送信するよう設定し、それをトリガーとしてZapierでの処理を動作させていました。しかし、webhookを受け取って処理を実行する際にエラーが発生した場合、対象のチケットの再取得に手間がかかるという問題がありました。このため、特定のチケットに対して任意のタイミングでデータ取得やデータ加工が行えるようにする必要がありました。

  2. データの加工の問題
    Zapierは基本的にノーコードプラットフォームであり、さまざまなサービスの連携を強力にサポートします。しかし、kickflowで生成されるJSON形式のデータをスプレッドシートへの書き込み用に加工する際には、多くのステップを踏む必要がありました。また、スプレッドシートのフォーマットが変更されると、そのメンテナンスに多くの時間がかかっていました。このため、変化するニーズに柔軟に対応できる連携方法に変更する必要がありました。

  3. 使用制限の超過（昔話です。現在はアップデートにより解決しています）
    現在のkickflowではwebhookを送信するイベントを任意で選べるようになりましたが、以前は全てのイベント（全てのワークフローで作成された全てのチケットのステータス変更が対象）でwebhookが送信されおり、結果として大量の処理がZapier上で行われることになりました。これにより、契約していた月間使用可能な処理回数を超えてしまい、予期せぬタイミングで処理が停止する問題が発生しました。

このような課題を解決するためにZapierでの連携を見直す必要がありました。

GoogleAppsScriptで連携をさせるメリット

このような課題を解決するためにコードで細かく処理を行えるGoogleAppsScript（以降GASと表記）で連携させることにしました。

GASを使用するメリットとしては以下のようなものがあります。

  • GoogleWorkSpaceを契約している場合無償で利用できる ←超重要
  • 独立した実行環境を必要としないため、非専門家にとって業務で使用する敷居が低い
  • GASはJavaScriptがベースになっているため、私にとってコードの記述が難しくない（過去に少しだけ触ったことがありました）

このようにGASには多くのメリットがありますが、実装を進める中で同時に解消しなければいけない課題もいくつか見えてきました。

GASで連携を進める中で見えてきた課題

そんなこんなで、GASを用いてkickflowとスプレッドシートの連携を進めていったのですが、いくつか問題がありました。

  1. データをどのように取得するか
    kickflowのwebhookはタイムアウトが10秒に設定されており、その時間内にデータの加工、添付ファイルのダウンロード、スプレッドシートへの書き込みを実行するには時間がかかりすぎてしまいエラーになってしまいます。そのため、どのようにkickflowからスプレッドシートに書き込む対象のチケットデータを取得するかが問題になりました。

  2. ワークフローごとに異なる配列のインデックスをどのように指定するか
    これはZapierでの連携時からの課題と重なりますが、kickflowで作成したワークフローには、カスタムフィールドの設定が可能で、その入力データはオブジェクトに配列として保存されます。 Zapierで連携していた時は、"ticketData.inputs[10]" のようにワークフローごとに配列のインデックスを指定しデータを取得していましたが、これではカスタムフィールドの項目変更があった場合、どこのインデックスにどのデータが保存されているかの確認から行う必要がありメンテナンスコストがかかるためチケットデータから任意のデータの取得方法を改善する必要がありました。

次の章から具体的にどのような方法、仕組みで問題を解消しつつ連携を進めていったか説明します。

課題解決とGASでの連携方法

それではここから先述したZapier、GASの課題についてどのように対応し、連携をおこなったかを解説します。

まずこちらの課題について、添付のブログ内の'独自キャッシュシステムを作成'の方法を参考にしました。

課題1（Zapier）書き込み時にエラーが起こった場合、対象のチケットデータの再取得に手間がかかる。

課題1（GAS）データをどのように取得するか

GoogleAppsScript(GAS)でのcacheあれこれ - 株式会社BEFOOL ブログ

実装手順解説

①kickflowのwebhookの設定で、webhookの対象ワークフローを任意のものを選択し、webhookの送信条件を、'ticket_completed' に設定します。

この設定によりスプレッドシートで管理したいワークフローと、検知したいイベントの種類を選択できます。

②次に、スプレッドシートにwebhookで送信された、完了したチケットのチケット番号とチケットのUUID（チケットごとに発行されるユニークな値）をスプレッドシートに記述するための関数を作成します。チケットのUUIDは後の処理で使用します

GASでwebhookを受け取る方法については以下の記事を参考にしました。

Google Spreadsheet を簡易 Webサーバーとして動かして、手軽にWebHookを受け取る方法 - Qiita

作成したスクリプト 

const doPost = (e) => {
  const postData = e.postData.contents;
  const jsonData = JSON.parse(postData);

  // チケット番号とUUIDを取得
  const ticketNumber = jsonData.data.ticket.ticketNumber;
  const ticketUuid = jsonData.data.ticket.id;

  //書き込み先のシートを取得
  const sheetId = PropertiesService.getScriptProperties().getProperty('SpreadsheetId')
  const spreadSheet = SpreadsheetApp.openById(sheetId).getSheetByName('完了済みチケットリスト');

  //スプレッドシートに書き込み
  spreadSheet.appendRow([ticketNumber, ticketUuid]);
}

kickflowからwebhookで送信された、完了したチケットの情報は画像のようにスプレッドシートに書き出されます。この処理は数秒で終了するためwebhookのタイムアウトに頭を悩ませる必要がなくなりました。

③取得したチケットのUUIDを別のスクリプトで読み込み、kickflowのAPIを利用してチケットのデータを取得する。

使用したAPIの詳細：kickflow REST API v1

スプレッドシートからUUIDを読み込むためのコード 

//完了済みチケットリストからチケットのUUIDを取得
  const sheetId = PropertiesService.getScriptProperties().getProperty('SpreadsheetId')
  const completedTicketsSheet = SpreadsheetApp.openById(sheetId).getSheetByName('完了済みチケットリスト');
  const lastRow_completedTicketsSheet = completedTicketsSheet.getLastRow();
  const ticketIdList = lastRow_completedTicketsSheet > 1 ? completedTicketsSheet.getRange(2, 1, lastRow_completedTicketsSheet - 1, 2).getValues() : [];

GASでKickflowのAPIを呼び出すためのコード 

//KickflowのAPIを呼び出しチケットデータを取得するための関数
function getKickflowTicket(id) {
  const apiUrl = `https://api.kickflow.com/v1/tickets/${id[1]}`;
  const apiKey = PropertiesService.getScriptProperties().getProperty('KickflowToken');
  const options = {
    'method': 'get',
    'headers': {
      'Authorization': `Bearer ${apiKey}`,
      'Content-Type': 'application/json'
    }
  };
  try{
    const response = UrlFetchApp.fetch(apiUrl, options);
    const result = JSON.parse(response.getContentText());
    return result;
  }
  catch(e) {
    //エラーハンドリング
    const adminChannel = PropertiesService.getScriptProperties().getProperty('corporate-it_notifications');
    const message = `KickflowAPIでのチケット情報取得時にエラーが発生しました。\nチケット番号: ${id[0]}\nError: ${e}`
    sendMessageToSlack(adminChannel, message);
  }
}

④取得したデータから必要なフィールドのデータを取得し、スプレッドシートに書き込む用に加工する

ここでは以下の問題に対応するために新しくオブジェクトと関数を作成しました。

課題2（GAS）ワークフローごとに異なる配列のインデックスをどのように指定するか

ワークフローごとにデータ取得用のkeyPathと出力時のOutput名をオブジェクトの配列で定義する

標準で設定されている項目に関しては特定のkeyが用意されているためそちらを使用します。

階層が深いものについては階層の上のkeyから配列で定義しています。

ワークフローに合わせて追加したカスタムフィールドについてはそれぞれフィールドコードが割り当てられるためそちらを使用します。

// これは購買稟議で使用しているものの一部です
const keys_purchaseOrderRequest = [
    {
      keyPath: ["ticketNumber"],
      outputKey: "ticketNumber"
    },
    {
      keyPath: ["workflow", "name"],
      outputKey: "applicationType"
    },
    {
      keyPath: ["title"],
      outputKey: "title"
    },
    {
      keyPath: ["authorTeam", "fullName"],
      outputKey: "department"
    },
    {
      keyPath: ["author", "fullName"],
      outputKey: "drafter"
    },
    {
      formCode: "123456789",
      outputKey: "contractStartDate"
    },
    {
      formCode: "987654321",
      outputKey: "contractEndDate"
    },
  ]

ここで定義したオブジェクトの配列を以下の関数で使用することで任意のデータをチケットデータから抽出することができます。

引数のticketDataにはkickflowのAPIで取得したチケットデータが、keysにはワークフローごとに設定した先述のオブジェクトの配列が渡されます。

この関数を通して任意のデータを指定したkey名で出力することができます。

//KickflowAPIで取得したチケットデータから必要なデータを抽出する関数
function extractData(ticketData, keys) {
  let extractedData = {};

  for (let key of keys) {
    if ('formCode' in key) { // formField.codeを使ってデータを取得する場合
      for (let input of ticketData.inputs) {
        if (input.formField.code === key.formCode) {
          extractedData[key.outputKey] = input.value;
          break;
        }
      }
    } else { // keyPathを使ってデータを取得する場合
      extractedData[key.outputKey] = getValueByPath(ticketData, key.keyPath);
    }
  }
  return extractedData;
}

//フィールドコードが設定されていないデータについてはkeyPathを使用してデータの取得を行う
function getValueByPath(obj, path) {
  return path.reduce((o, k) => (o || {})[k], obj);
}

この手順でチケットのデータを取得することで、ワークフローのフォーマットが変更された場合でも、変更されたフィールド以外影響を受けることなくデータの取得が可能になりました。

そして、GASでチケットデータをスプレッドシートへの書き込み用に加工することで比較的自由にオブジェクトを操作できるようになり、Zapierでのデータ加工の問題もクリアされました。

課題2（Zapier）データの加工の問題

最後に

ここまで読んでいただき、誠にありがとうございます。

本記事では、kickflowとスプレッドシートの連携について、またその過程で工夫した点などについて紹介しました。GASやサービスが提供しているAPIを活用することで、システム間の柔軟な連携が可能となることを実感した、貴重な経験となりました。

今後は、さらなる効率化に向けて、システムを実際に利用する社員とのコミュニケーションを密に行いつつ、社内のシステム連携を最適化していくことを目指しています