2
Help us understand the problem. What are the problem?

posted at

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

やりたいこと

「ユーザー登録画面で、どこまで進んだ人(どこで離脱した人)が何人いるか」をスプシに書き込みたい。
CleanShot 2021-12-24 at 14.12.41@2x.png

実装

メインの処理

スプシに書き込む関数.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

Register as a new user and use Qiita more conveniently

  1. You can follow users and tags
  2. you can stock useful information
  3. You can make editorial suggestions for articles
What you can do with signing up
2
Help us understand the problem. What are the problem?