GASでRedashのAPIを叩いてスプレッドシートに記入するサンプルコード

やりたいこと

「ユーザー登録画面で、どこまで進んだ人(どこで離脱した人)が何人いるか」をスプシに書き込みたい。

実装

メインの処理

スプシに書き込む関数.gs

function writeWeeklySampleDataToSpreadSheet() {
  const SHEET_ID = "{sheet_id}";
  const spreadSheet = SpreadsheetApp.openById(SHEET_ID);
  const sheet = spreadSheet.getSheets()[0];
  const yesterday = calcYesterday();
  const sevenDaysAgo = calc7daysAgo();
  const weeklySampleData = fetchWeeklySampleDataFromRedash();
  sheet.appendRow([
    sevenDaysAgo + ' ~ ' + yesterday,
    weeklySampleData[0]['count(*)'],
    calcTransitionRate(weeklySampleData[0]['count(*)'], weeklySampleData[1]['count(*)']),
    weeklySampleData[1]['count(*)'],
  ]);
}

呼び出されるメソッド

呼び出されるメソッドたち.gs

function fetchWeeklySampleDataFromRedash() {
  const endpoint = 'http://{redash-host}/api/queries/{query_id}/results.json?api_key={api_key}'
  return fetchDataFromRedash(endpoint)
}

function fetchDataFromRedash(endpoint) {
  const response = UrlFetchApp.fetch(endpoint);
  return JSON.parse(response.getContentText()).query_result.data.rows;
}

function calcYesterday() {
  var date = new Date();
  const day = date.getDate();
  date.setDate(day-1);
  return Utilities.formatDate(date, 'JST', 'yyyy-MM-dd');
}

function calc7daysAgo() {
  var date = new Date();
  const day = date.getDate();
  date.setDate(day-7);
  return Utilities.formatDate(date, 'JST', 'yyyy-MM-dd');
}

function calcTransitionRate(before, after) {
  a = Math.round((after/before)* 1000)
  return (a/10 + '%')
}

Redash上のクエリ

SELECT '年齢まで入力済' AS 'registration_status', count(*)
FROM users
WHERE created BETWEEN DATE_ADD(CURRENT_DATE, INTERVAL - 7 DAY) AND CURRENT_DATE
  AND age IS NOT NULL

union

SELECT '性別まで入力済' AS 'registration_status', count(*)
FROM users
WHERE created BETWEEN DATE_ADD(CURRENT_DATE, INTERVAL - 7 DAY) AND CURRENT_DATE
  AND age IS NOT NULL
  AND gender IS NOT NULL