GASとSpreadsheetで作業自動化をしたらみんなが台パンしなくなった話

2023年1月5日 11:20

セレスのエンジニア　吉元です。
今回はGASとSpreadsheetを使った社内の作業コストの削減にチャレンジした話。

弊社では広告メディアをサービス展開しているため、
ユーザーから受けた問い合わせを広告代理店を通して
広告掲載主に状況を確認する業務的フローが発生する。

その対応を行っているのが弊社の台パン部隊こと「カスタマーサポートチーム」。
早速コストが削減できそうな手作業ボリュームを確認してみよう。
ヒヤリングした結果は以下の通りである。

自動化するには実に良い題材とは思わんかね？
もはや手作業による人的コストの消耗戦。

消耗戦とは、戦争時における交戦状態のひとつ。戦闘の決定的な主導権をどちらも握れないまま、長期継続的に同一地点に戦力を投入し続けて損害を出し続ける状態、あるいは強固に防衛された要塞など一地点の攻略のために、攻撃側が長期継続的に多大な損害を出しながら攻撃を続けることを言う。

Wikipedia出典

さもありなん。

そしてさらに追い打ちとばかりに悲惨な話が出るわ出るわ。
・膨大なデータをExcelに詰め込んだことによるPCのフリーズ
・代理店の問い合わせ仕様変更でフォーマット変更
・不具合による問い合わせ増（これはごめん）

これは確かに台パンしてキーボードクラッシュするレベル。

ってな訳で放置しておくには忍びないので
最も時間がかかるという問い合わせメール（①）から
Excelに集計する（②）フローの自動化を考えてみる。

DB管理してCSVに吐き出すほうが健全だが今回は速度重視で一刻も早く台パンを止めたい。机壊れちゃう。

問い合わせはメールは「MailDealer」を使用しているためAPIでアクセス可能。
MailDealerはAPI連携拡張オプションを使う。
これを使うとMailDealer内のフォルダ毎にメール情報を取得できるオプションが開放されるということだ。

MailDealerのAPIは仕様書があるためアクセス方法はそちらを参照すること。
ここではAPIの詳細は省略。APIのアクセスには制限もあるので注意する。

「メール受信日の範囲」「フォルダ番号」「メール状態（新着メール）」を指定。
最終的にメール内容から特定部分をSpreadsheetに吐き出すので、
Spreadsheetのセルに受信日の範囲を設定して日付の範囲を取る。
APIへアクセスするため、UrlFetchApp.fetch()を利用してHTTP通信を実行する。

function myFunction()
{
    const mailList = fetchMailList(
        sheet.getRange("B2").getDisplayValue(),
        sheet.getRange("B3").getDisplayValue(),
        folder_id,
        status_id
    )
}

// 細かいところは省略
function fetchMailList(from, to, folder_id, status_id)
{
    const url = "MailDealerのエンドポイント"
    const option = {method: post, payload: {JSONエンコードしたパラメータ}, headers: {methodを指定}}
    const response = UrlFetchApp.fetch(url, options)
    return JSON.parse(response)
}

適当だが「B2」「B3」に日付を持ち、メール受信の範囲を取得することにした。getDisplayValue()で取得しているのは、日付をString型でAPIに渡したいから。
Spreadsheetは日付のデータをgetValue()で取得するとDatetime型として扱うのでこういう時は不便。（セル上はカレンダー出るからUI的には便利だけど）
日付の整合性を考慮するならDatetime型で評価したほうがいい。
Validationせんでいいのかって？知らない英単語ですね。

function myFunction()
{
    const mailList = fetchMailList(
        sheet.getRange("B2").getDisplayValue(),
        sheet.getRange("B3").getDisplayValue(),
        folder_id,
        status_id
    )

    mailList['items'].forEach(function(data) {
        console.log(data.メールID)
        console.log(data.本文)
    })
}

MailDealerのAPIから返ってきたデータが取得できたらメール内容を参照してみる。
メール本文はが「items」の中にメールID毎のレコードで格納されている。
省略しているが、HTTPレスポンスコードやリクエスト成否も格納されているので、
レスポンスを評価。しょっぱいエラーを出さないように。

フローで示した通り、今回対象にしたサービスは問い合わせページがあり、問い合わせの際にある程度テンプレートが決まっている。問い合わせ内容が一部自動的に生成されるため、取得したい項目は正規表現で取得ができた。

氏名：てすと　太郎
申込番号：12345678
メアド：hogehoge@hoge.hoge
本文：広告使ったのにマイページに反映されないよ

問い合わせメールbodyの例

ある程度整理された文章の場合は正規表現で抽出する方法で部分的に抜き出してくることができる。とりあえずmatch()を使って『氏名』を抽出する。

function myFunction()
{
  const text = "氏名：てすと　太郎"
  const regex = new RegExp(/氏名：(.+)/)
  console.log(text.match(regex))
}

match()の返値は配列で、正規表現で指定した「(.+)」部分の抽出が成功すると
インデックス１に格納され、取得できない場合match()はnullを返す。
今回の例では以下のような返値になった。

[ '氏名：てすと　太郎',
  'てすと　太郎',
  index: 0,
  input: '氏名：てすと　太郎',
  groups: undefined ]

これだけでも機能として概ね問題ないが、
空白や改行コードが残るのが嫌な場合にはreplase()を使って除去する。
使い回す想定なら関数作って使いやすい形にしておく。
flagの引数を指定できるのは、複数行を抽出したいときに『m』や『s』を指定して利用する。

function extractWord(text, search_word, flag)
{
  const regex = new RegExp(search_word, flag)
  const result = text.match(regex)
  return (result === null) ? '' : result[1].replace(/\s/, '')
}

正規表現を使った文字列検索やフラグ設定については以下で詳細な解説がある。

次は一覧にまとめるためにSpreadsheetへの書き込んでみる。
速度を意識しないならappendRow()を使用するのが楽で、
空き行から１レコードずつメール取得順に書き込んで並べてくれる。

function myFunction()
{
  
  mailList['items'].forEach(function(data) {
      const name = extractWord(data.本文, /氏名：(.*)/)
      const mail = extractWord(data.本文, /メアド：(.*)/)

      sheet.appendRow([
        data.メールID,
        name,
        mail
      ])
  })
  
}

appendRow()というよりは１レコードずつ書き込む方法は遅い。
GASからSpreadsheetへの書込みは基本的に通信が発生するため、
セル単位や行単位に書き込む方法は速度重視ならオススメしない。
GASでSpreadsheetを読み書きする場合は可能な限りSheetへのアクセスを減らすと良い。配列かオブジェクトに抽出データを突っ込んで１度に書き込むと動作が軽快に見えるので気分がアガる。~~あなたさっき日付バラバラに読み込んでたよね~~

  const list = [
    [1, "テスト　太郎", "test@test", "問い合わせ本文１"],
    [2, "テスト　太郎", "hoge@test", "問い合わせ本文２"]
  ]

  const spSheet = SpreadsheetApp.openById("xxxxxx")
  const sheet = spSheet.getActiveSheet()
  sheet.getRange("A1:D"+ list.length).setValues(list)

書き込みが成功すると気になるのが行サイズ。
Spreadsheetのセルに改行を含む文字列を書き込むと行サイズが大きくなり、
一覧として見えづらい（要件として全文見たい勢がいるため依頼者のヒヤリング推奨）。

GASではデフォルトの機能でSpreadsheetの行サイズを制御できないため、
「Google Sheets API（Sheets）」のサービスの追加を行い解決する。

GASの左メニューにある「サービス」からGoogle Sheets APIを追加すると
以下のようにSheetsに対してPixelの設定をオブジェクトに詰めてAPIにリクエストできる。

  settingPixelSize(sheet_id)
  {
    const requests = {
      updateDimensionProperties: {
        properties: {pixelSize: 21},
        range: {
          sheetId: sheet_id, 
          startIndex: 1, 
          endIndex: 10, 
          dimension: "ROWS"
        },
        fields: "pixelSize"
      }
    }
    Sheets.Spreadsheets.batchUpdate({requests: requests}, sheet_id);
  }

指定したサイズ（pixelSize: 21、１～１０行目）の行サイズに変更が可能。
これを使って他にもSpreadsheetに対しての制御は色々できそう。

大枠はこれで完了。
細かい部分では一覧の削除機能を入れたり、CSVのダウンロード機能も提供。
重複データがあれば配列に含めないことで追加分のみ書き出すようにもした。

また重要な点として、個人情報が絡むのでシートへのアクセス権限を設定。
社内の公開範囲を制限し、社外からのアクセスも勿論不可。GASも同様。

他にできることとしては
MailDealerのAPIリクエストや文字列からの抽出は流用可能であるため、
classオブジェクト作ってライブラリとして提供する形も良さそう。

GASでも流行りのアーキテクチャを取り入れてモダンに作れると思うが、
スケール的にそこまで手を回さんでもなー。うーん…と考えてる間にモノとしては完成していた。

兎も角、完成したブツのテストを動かして実用として問題ないことを確認。
台パンチームに展開した。

結果としてカスタマーサポートの問い合わせ対応時間が３割程（月間作業にして40時間くらい）浮く。まじか…。

作業にゆとりができて台パンの音は社内から消え、
机の耐用年数を引き伸ばすことにも成功した。

しかしこんな＼手作業マジ卍／な状況を知ってしまうと、
人知れず手作業による浪費をしている社員がいるんじゃないかと
気になって夜しか眠れない。
次なる台パンはあなたの隣に潜んでいるのかも知れない。