はじめまして、データストラテジストのoyabuです。 RedashからCSVでデータをエクスポートして、GoogleDriveに保存、更にCSVをSpreadsheet化してようやく可視化の準備が整うの、めんどくさいですよね。それらを自動化するGASを作ったので、書きます

注意点

極限までサボりたかったのでChatGPTに聞いてツギハギして動けばヨシの精神で作りました。出来上がったものをみて、コードの整然さやエラーハンドリングについて、思うところは多々ありますがそのままにしています。社内用に展開しているものは複数クエリに対応しているのですが、1->nになると本筋と関係のない話題が増えるので今回はデータを抽出するクエリが1つに限定されたGASのコードを考えることにします

課題

まずそもそもなんでこれやったかです。以下が理由です

RedashのQuery API はSpreadhsheet上でIMPORTDATAできて便利だが、パラメータを使っているクエリのデータは抽出できない
基本CSV->G Spreadsheetに変換したうえで加工することが多いので、施行回数が多くなるとつらい
using redashAPI with GASの記事はいくつかみかけるが、ポーリングをsleepなどにまかせていて、重いクエリがそもそも回せない

やったこと

上記問題を解くために、以下を実施しました

パラメータつかってるクエリからもAPI使ってデータ抽出できるようにする
時間がかかるクエリはポーリングする(GASのtimeoutである6minに負けない)
CSV->スプレッドシート化まで自動化

中でも本記事で触れるのはあんまり情報として見ない(気がする)以下の項目です

RedashのUser API Keyの簡単な解説とGASでの使い方
GASのトリガーを使ったポーリング

その他についてはよく見るので、詳細については本記事では触れません

RedashのUser API Keyの簡単な解説と使い方

詳しくは公式を参照していただければと思うのですが、めんどくさいです。まずresponseが書いてありません。愚直にrequestして、responseをみる必要があります。つらいです

API

今回やりたいことを実現するうえでの主役はこいつになります /api/queries/<id>/results クエリIDとパラメータを渡してPOSTしたときにキャッシュされた結果があればそれを、なければクエリを実行するエンドポイントです

responseの中にstatusを格納したキーが無く、query_resultキーがあればデータが返ってきた。なければクエリが実行されたので、ポーリングしてデータが返ってくるまで待つ。の判断をしないといけないのでちょっとゾワゾワします

親切にやるならこっちでstatusを判断してObjectとしてラップして返しちゃう関数を作るのがよいと思いますが、今回のコンセプトは極限までサボる。です。心を鬼にしてChatGPTが出したものを正として進めていきます。

余談ですがChatGPTは一瞬で80点までは出してくれるのですが、100点までChatGPTオンリーで詰めるのはちょっとしんどいと思ってます。(百里を往くものは九十を半ばとす。なので、このあたりは自前の実装でも同じことは言えますが、一段抽象化されているのでコントロールが効きづらく、十里がより遠くなる印象)

出来上がり is belowなのですが、ここに関する白眉なコードはこんな感じです

function _getJobId(queryId, param) {
  const apiUrl = `${host}/api/queries/${queryId}/results`;
  const data = {
    parameters: param
  };
  const payload = JSON.stringify(data);
  const options = {
    'method' : 'post',
    'contentType': 'application/json',
    'headers': {
      'Authorization': 'Key ' + apiKey
    },
    'payload' : payload,
    'muteHttpExceptions': true
  };
  let results = UrlFetchApp.fetch(apiUrl, options);
  let isResult = !!JSON.parse(results).query_result;
  console.log(isResult);
  if (isResult) {
    console.log(JSON.parse(results).query_result.data.rows);
    return JSON.parse(results).query_result.data;
  }
  const jobId = JSON.parse(results).job.id;
  return jobId;
}

なんとキャッシュがあれば配列を、なければjobIdを返します。ChatGPTが言うので仕方ないですが結構しんどいですとはいえ、/api/queries/<id>/results の仕様上どこかでこんな感じの処理が必要になってきます(もうちょい考慮してwrapするべきという議論は置いておきます)

GASのトリガーを使ったポーリング

GASのtimeoutが6minなので、sleepで待ってもそもそも重いクエリの実行が無理になってしまいます。一方でGASはトリガーが結構な量作れたりするので、これを使ってポーリングすると楽になるシーンが多いです。変数は渡せないので、そこはspreadsheetのシート上に持たせる解き方で頑張ります。(ほんとは実行者のみ編集できるセルとかにしたほうがいいけど、サボります)

例えばこんな感じです。今回は自分を呼んでます

  data = getJobResult(jobId);
  if (!data) {
    ScriptApp.newTrigger('generateRedashFiles').timeBased().after(min * 60 * 1000).create();
    return;
  }

結果のキャッシュがなかったときは、Redash側でクエリが実行されるので、一旦ジョブIDをスプレッドシートに保存しといて、再度自分を呼び出したときに参照するようにしますこんな感じでトリガーが作成されます。急いでなかったり、重めのクエリかもなー。というときは30minとかで良さそうな気もします

出来上がり

ChatGPTにいっぱい聞いてツギハギしてちょっとだけ手直しした結果がこれです。とりあえず動きます。使い方は後述します。

const host = '${redashのホスト名}';
const apiKey = '${redah user API Key}';
const urlSheetName = 'URLリスト'; // TODO: edit as each env
const ss = SpreadsheetApp.getActiveSpreadsheet();
const folderId = '${データ保存先のG DriveフォルダID}';
let min = 30;

function generateRedashFiles() {
  let sheet = ss.getSheetByName(urlSheetName);

  let data;
  let url = sheet.getRange(2, 2).getValue();
  let fileName = sheet.getRange(2, 1).getValue();
  let jobId = sheet.getRange(2, 3).getValue();

  if (!jobId || jobId == '') {
    jobId = getJobId(url);
    // jobID or data
    if (jobId instanceof Object) {
      data = jobId;
      json2Csv(fileName, data);
      return;
    } else {
      sheet.getRange(2, 3).setValue(jobId);
    }
  }

  data = getJobResult(jobId);
  if (!data) {
    ScriptApp.newTrigger('generateRedashFiles').timeBased().after(min * 60 * 1000).create();
    return;
  }
  json2Csv(fileName, data);
  importCsvFilesToSpreadsheet();
}

function getJobResult(jobId) {
  const jobStatusUri = `${host}/api/jobs/${jobId}?api_key=${apiKey}`;
  let queryResultId = null;
  const jobStatus = JSON.parse(UrlFetchApp.fetch(jobStatusUri)).job;
  const status = jobStatus.status;
  if (status === 3 || status === 4) {
      queryResultId = jobStatus.query_result_id;
  } else {
    return;
  }
  const jobResultUri = `${host}/api/query_results/${queryResultId}.json?api_key=${apiKey}`;
  results = UrlFetchApp.fetch(jobResultUri);
  return JSON.parse(results).query_result.data;
}

function getJobId(url) {
  let payload = generatePayload(url);
  let queryId = payload[0];
  let param = payload[1];
  let jobId = _getJobId(queryId, param);
  return jobId;
}

function _getJobId(queryId, param) {
  const apiUrl = `${host}/api/queries/${queryId}/results`;
  const data = {
    parameters: param
  };
  const payload = JSON.stringify(data);
  const options = {
    'method' : 'post',
    'contentType': 'application/json',
    'headers': {
      'Authorization': 'Key ' + apiKey
    },
    'payload' : payload,
    'muteHttpExceptions': true
  };
  let results = UrlFetchApp.fetch(apiUrl, options);
  let isResult = !!JSON.parse(results).query_result;
  console.log(isResult);
  if (isResult) {
    console.log(JSON.parse(results).query_result.data.rows);
    return JSON.parse(results).query_result.data;
  }
  const jobId = JSON.parse(results).job.id;
  return jobId;
}

function generatePayload(url) {
  url = url.split('#')[0];
  let match = url.match(/\/queries\/(\d+)\/source/);
  let queryId = match ? match[1] : null;
  console.log(queryId);
  let params = {};
  let queryString = url.split('?')[1];
  const regex = /^\d{4}-\d{2}-\d{2}--\d{4}-\d{2}-\d{2}$/;
  if (queryString) {
    let pairs = queryString.split('&');
    pairs.forEach((pair) => {
      let kv = pair.split('=');
      let key = decodeURIComponent(kv[0]).substring(2);
      let val = decodeURIComponent(kv[1] || '');
      if (regex.test(val)) {
        let dates = val.split('--');
        val = {
          'start': dates[0],
          'end': dates[1]
        }
      }
      params[key] = val;
    });
  }
  console.log(params);
  return [queryId, params];
}

function getFolderId() {
  const folderUrl = ss.getSheetByName(settingSheetName).getRange('B1').getValue();
  console.log(folderUrl);
  const matches = folderUrl.match(/[-\w]{25,}/);
  if (!matches) {
    throw new Error('Invalid folder URL');
  }
  return matches[0];
}

function moveFileToFolder(fileId) {
  const folder = DriveApp.getFolderById(folderId);
  const file = DriveApp.getFileById(fileId);
  file.moveTo(folder);
  console.log(`File "${file.getName()}" has been moved to folder "${folder.getName()}"`);
}

function importCsvFilesToSpreadsheet() {
  var folder = DriveApp.getFolderById(folderId);
  var csvFiles = folder.getFilesByType(MimeType.CSV);

  // 新しいスプレッドシートを作成し、特定のフォルダに移動
  var spreadsheet = SpreadsheetApp.create('summary');
  var spreadsheetFile = DriveApp.getFileById(spreadsheet.getId());
  folder.addFile(spreadsheetFile);
  DriveApp.getRootFolder().removeFile(spreadsheetFile);

  var firstSheet = true;

  while (csvFiles.hasNext()) {
    var file = csvFiles.next();
    var fileName = file.getName();
    var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());

    if (firstSheet) {
      // 最初のCSVファイルの場合、既存のシートを使用
      var sheet = spreadsheet.getSheets()[0];
      sheet.setName(fileName);
      firstSheet = false;
    } else {
      // 2つ目以降のCSVファイルの場合、新しいシートを作成
      var sheet = spreadsheet.insertSheet(fileName);
    }

    // CSVデータをシートに書き込む
    var range = sheet.getRange(1, 1, csvData.length, csvData[0].length);
    range.setValues(csvData);
  }

  // 最後にスプレッドシートを開く
  SpreadsheetApp.getActiveSpreadsheet().toast('CSVファイルの集約が完了しました。', '完了', 5);
  var url = spreadsheet.getUrl();
  Logger.log('スプレッドシートのURL: ' + url);
}

// json to csv
function json2Csv(fileName, data) {
  // CSV文字列を生成
  let csvContent = '';

  // ヘッダーを追加
  const headers = data.columns.map(column => column.friendly_name);
  csvContent += headers.join(',') + '\n';

  // 各行のデータを追加
  data.rows.forEach(row => {
    const rowValues = data.columns.map(column => {
      const value = row[column.name];
      // CSVの規則に従って、カンマや改行を含む値をダブルクォートで囲む
      return `"${value.toString().replace(/"/g, '""')}"`;
    });
    csvContent += rowValues.join(',') + '\n';
  });

  // CSVファイルをGoogleドライブに保存
  const file = DriveApp.createFile(fileName + '.csv', csvContent, MimeType.CSV);

  // ファイルのURLをログに出力
  Logger.log('CSVファイルが作成されました: ' + file.getUrl());
  moveFileToFolder(file.getId());
}

使い方

URLリスト シートを作って、こんな感じに設定します

jobID部分は後で更新されます

GASに上記の出来上がりコードを貼って generateRedashFiles 関数を実行します

ジョブIDが更新され、トリガーが登録されます

時間が来るとトリガーが実行され、結果がキャッシュされていれば指定のフォルダにCSVとスプレッドシートが保存されます

終わりに

ところどころ手直しはしたいですが、とりあえず動くものができました。今回は簡単のために単一クエリに話しを限定しましたが、RedashAPIをGASで動かせることの最大の強みは、パラメータだけ変えたクエリをスプレッドシート上で大量に作れるところだと思います。なんだかんだパラメータを設定する、、他のパラメータも別画面で設定する、、結果がでるまで待つ。。やっぱり別の設定のがよいな。。設定し直す。。結果がでるまで待つ。。みたいな作業がBIツールを使っているとどうしても発生しがちなので、そこをG Suiteにまかせて富豪的に解決出来るのは他の作業ができて個人的には便利なところかと思っています。

それではさようなら

every Tech Blog

株式会社エブリーのTech Blogです。

GASを使ってRedashからデータ抽出して、G Spreadsheetを作成する

注意点

課題

やったこと

RedashのUser API Keyの簡単な解説と使い方

GASのトリガーを使ったポーリング

出来上がり

使い方

終わりに