Redash × GASで25,000回/月のスプシ貼り付けを自動化した話

※こちらはミツモアAdvent Calendar 2024の12/19の記事です。

こんにちは、ミツモアの非エンジニアの深澤です。

ミツモアでは、社内のデータ活用を促進するため、Redashのデータをスプレッドシートに連携する社内ツール「importdataGAS」を開発・運用しています。開発時に公開した記事「Redash importdataを完全にGAS化した」から早くも一年が経ち、ツールも大幅にパワーアップしました。

そこで今回は、ミツモアが大切にしているバリューの一つ「テクシン（=テクノロジーでシンプルに）」を実現するimportdataGASが、この一年間でどう改善されたのかをご紹介します。

（改善はLLMをフル活用することで非常にスピーディーに行うことができました。テクシン！）

importdataGASとは？

タスク登録シートのイメージ

簡単に言うと、Redashからスプレッドシートへのデータインポートを自動化するGAS製のツールです。

RedashのクエリID、貼付先のスプレッドシート、実行頻度を「タスク」として登録すれば、あとは自動的に貼付けが行われる便利なものです。

詳しい仕組みについては昨年の記事をご覧ください。

Redash importdataを完全にGAS化した - ミツモア Tech blog

2024年の実績

この１年で、importdataGASはいくつかの課題を克服し、社内での活用度がさらに向上しました。

当初445個だった登録タスク数は、現在672個（一時停止中73個含む）に増加し、月間の自動貼り付け回数は約25,000回(推計)に達しました。

これは1回の手作業を3分と考えると、月1,250時間（1日42時間！）の作業を自動化できている計算です。

全Redashクエリ実行の約20%がこのツール経由という数字からも、確実に社内で定着していると言えそうです。

改良したポイント

importdataGASが抱えていた課題と、それをどのように乗り越えたかを紹介します。

重いデータの貼り付け能力を向上

前バージョンでは、”重いデータの貼り付けに時間がかかる・頻繁に失敗する・タイムアウトする”という課題がありました。「2,000行ずつに分割して貼り付ける」などの工夫をしていましたが、数万行のデータの貼り付けは成功率が五分五分という状態でした。

新バージョンでは、貼り付け処理をrange.setValuesからSheets APIのspreadsheets.values.update に変更し、重いデータの貼り付け能力が大幅に改善されました。

Method: spreadsheets.values.update | Google Sheets | Google for Developers

数万行 × 数十列のデータも安定して貼り付けできるようになったことでタイムアウトやエラー率が激減し、処理効率が大幅に向上しました。

また、Redashからデータを取得する際、以前は.json でリクエストしていましたが、「一定サイズ以上のデータを取得しようとすると、データが途中で切れる」という現象が発生していたため、.csv に変更しました。（GASの制限なのか、RedashのAPIの仕様なのかは不明。パースしようとするとUnterminated string in JSON at position ~ エラーが発生する）

GASのトリガーの個数制限を克服

GASを普通に使う分には意識することはありませんが、GASには”1ユーザー/スクリプトあたり20個”というトリガー数の制限が存在します。

前バージョンでは全タスクを１アカウントで実行していたため、この制限により”ツールの利用者が増えるにつれて一人当たりが実行可能なタスク数が減っていってしまう”という悲しみを抱えていました。

新バージョンでは、各ユーザーがそれぞれトリガーを設置する方式に変更し、トリガーの制限を完全に克服しました。

トリガーの設置は、スクリプトを仕込んだボタンをクリックすることでワンクリックで完了するようにし、タスク数が0になった際は自動的にトリガーが削除される仕組みも実装しました。

各ユーザーがスクリプトの実行者となったことにより、”貼り付け先シートの編集権限を実行者に付与する”というひと手間も削減されました。

GASがタイムアウトする問題を克服

トリガーの個数制限解消の喜びも束の間、一人で100個を超えるタスクを登録するユーザーが現れ、今度はGASの30分タイムアウト制限に直面することになりました。

この課題に対しては、タイムアウト時にフォローアップ処理を実行する仕組みを導入し、最大60分の処理時間を確保することで解決しました。

// 処理開始時に30分後に実行されるトリガーを作成し、処理が完了したらトリガーを削除する処理

// タイムアウト時再開用のトリガーをセット
ScriptApp.newTrigger(`processUncompletedScheduledTask`)
   .timeBased()
   .after(31 * 60 * 1000) // 31分後
   .create();
// 最終更新行をプロパティに保存
PropertiesService.getScriptProperties().setProperty(Session.getEffectiveUser().getEmail(), TASK_LIST_START_ROW_INDEX);

// タスク実行
executeTasks(taskListFiltered, true, true);

// 実行が完了した場合は再開用のトリガーを削除
deleteTriggers(`processUncompletedScheduledTask`);

シートを一枚に統合し一覧性を向上

使いやすさの面でも進歩を見せました。

前バージョンでは実行頻度ごとに20枚に分けていたタスク登録シートを１枚に統合し、タスクの追加・編集がより楽になりました。

本実行シートとは別に用意していたテスト実行用シートも統合し、選択した行をワンクリックでテスト実行できるようにしました。

これにより、複数人が同時にテストしようとした際の高度な駆け引きを不要にし、テスト実行後の転記の手間を削減しました。

管理のしやすさと集計の効率も大きく向上しました。（そして何より、迫力が増して利用者のテンションが上がりました。たぶん）

その他の細かな改善

貼り付けタイプの選択肢を拡充
- 新たに”値貼り付け”および”最終行への追加”に対応しました。
  - 最終行への追加はSheets APIのspreadsheets.values.appendを使用することで簡単に実装できました。
- これにより、特定部署向けに別ツールとして開発していたimportdataGASの亜種を統合できました。
タスク一覧の自動並び替え
- タスクの編集時に目的のタスクを探す手間を軽減するため、夜な夜な私の代わりに実行者ごとにタスクをを並び替える処理をspreadsheets.batchUpdateを使用して実装しました。
- Sheets APIのbatchUpdateはアトミックに実行されるため、並び替えが中断してシートがグチャグチャになる心配がない点が推しポイントです。
  
  Method: spreadsheets.batchUpdate | Google Sheets | Google for Developers
ステータス管理の導入
- タスクの設定項目に”ステータス”を追加しました。
- 「気づかずに他人のタスクを削除してしまう」という悲劇が繰り返されなくなりました。
- ちなみにステータスを「有効🔔」に設定すると、実行が失敗した際にSlackでメンション付きのエラー通知が受け取れます。
区切り文字を変更
- redashから受け取ったデータを整形する処理で、区切り文字を”,”（カンマ）からより登場頻度の低い”§”（セクション）に変更しました。
- ”,”(カンマ)が含まれるデータもより安全に貼り付けられるようになりました
夜間の実行をスキップ
- 深夜帯の実行をスキップするようにしたことで、実行コストを削減しました。

Redash 実行結果のモニタリングのはなし

Redash fingerprintを入れて解決したよ

Redashは、各アカウントでログインして実行したということはわかりますがBigQuery上ではどのクエリを実行したのかを把握することはできません。また、パラメータも存在するため、同じRedash Queryでも実行SQLは異なり、どの実行がどのクエリによるものなのかを特定することがとても困難でした。そのため、Redash Queryにfingerprintを付与する仕組みを構築しました

具体的には

select
    *
from
    dataset.datatable

というクエリを書いた場合、hourlyで次のように先頭行に1行追加するバッチ処理を仕込みました。ここの10001は、RedashのURLの部分が入るようにしてあります。

https:///queries//source

-- Redash Query : 10001
select
    *
from
    dataset.datatable

クエリにこのfingerprintがあることで、次のSQLを実行すると、どのクエリがどの程度定期的に実行され、それらがどの程度コストが掛かっているのかを可視化することができます。

SELECT
      REGEXP_EXTRACT(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query, r"-- Redash Query\s*:\s*(\d+)") AS query_id,
    ROUND(SUM(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes) /1000000000, 3) AS total_GB,
    ROUND(
      SUM(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes) / 1000000000 / COUNT(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query),
      3
     ) AS onetime_GB,
    ROUND(SUM(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes) /200000000000, 4) AS PRICE,
    COUNT(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query) AS query_count,
FROM
    `<< project name >>.AuditLogs.cloudaudit_googleapis_com_data_access_*`
WHERE
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.eventName = 'query_job_completed' 
    and PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) >= '2024-01-01'
    and PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) <= '2024-12-31'
    and protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query like '-- Redash Query : %'
    and protopayload_auditlog.authenticationInfo.principalEmail = '<< service account >>'
GROUP BY
    query_id
ORDER BY
    total_GB DESC

この結果とimportdataGASの設定を組み合わせると誰がどのように実行してそれらがどの程度コストがかかっているのかの全貌がRedashというBIツールでも可視化をすることが可能になり、将来BIツール移行する際(現状決まった予定はないです)であったりコスト削減大会を開催するときなどに活躍してくれそうな予感がしています。

さいごに

Redash × GASで、よりよいデータ利活用ライフを！

ミツモアでは様々な職種の仲間を積極的に採用しています！ご興味がある方はぜひ気軽に面談しましょう！

採用ページ: https://meetsmore.com/company/recruit
求人一覧: https://hrmos.co/pages/meetsmore/jobs