MetabaseでSQLを実行した結果をGAS（Google Apps Script）でスプレッドシートに書き出す

# 基本となる情報

今回やりたいことを `curl` で実行した結果が [MetabaseにREST APIを使ってアクセスしSQLを実行する](https://qiita.com/piggydev/items/2d690de8e50cd8f9b523) にあります。
これを踏まえて、ここでは `GAS` を使って実行し、スプレッドシートに出力します。

# GAS

後述の GAS に関する注釈です。

- 実行する関数を `execMain` として、それ以外の関数は `_` をプレフィックスにつけて `_functionName` という形にしています。
  - スクリプトでは使われていないものの、デバッグなどに便利なユーティリティ関数も追加しています（`_getProperties` など）。
- 複数の環境（ここでは QA と PROD）が存在することを前提としています。
  - 但し、環境毎にユーザ名、パスワードを区別することまではしていません。
     - パスワードはスクリプトプロパティに保存しているので、環境毎に key 名を変えることで対応は可能です。
     - ユーザ名はハードコードしていますが、これもスクリプトプロパティに保存することで対応は可能です。
- パスワードは、popup で入力した値をスクリプトプロパティに保存しています。
- スクリプトプロパティの key 名は、`<xxxProp>` という形式にしています。
- 出力先のシートは、一度削除して作り直しています。もし他からリンクしている場合は、シートの gid が変わるためリンクが切れます。リンクを生かすためには、同じシートに上書きするようにGASを書き換える必要があります。

```javascript
/**
 * This script executes SQL on Metabase and saves the result into the given sheet.
 *
 * Run execMain.
 *
 * Useful documents of this script:
 * - https://github.com/metabase/metabase/wiki/Using-the-REST-API
 * - https://github.com/metabase/metabase/blob/master/docs/api-documentation.md#post-apidataset
 **/

const URL = {
  QA: 'https://<urlForQA>/',
  PROD: 'https://<urlForPROD>/'
};

const tableNumInMetabase = {
  QA: 1,
  PROD: 2
};

const queryResultSheetName = '<sheetname>';
const columns = ['id', 'email', 'name', 'createdAt', 'updatedAt'];
const userName = '<username>';
const tableName = '<tablename>';
const environment='<qa or prod>';

const execMain = (dryRun = false) => {
  const props = _initialize();
  const sheet = props.sheets.queryResult;
  const sessionId = _getSessionId(props);

  // Fetch data
  //
  // NOTE: Sessions are good for 14 days by default; you can configure this value by setting the env var MAX_SESSION_AGE (value is in minutes).
  //   see more: https://github.com/metabase/metabase/wiki/Using-the-REST-API
  let response = _fetchData(props, sessionId, dryRun);
  if (response.getResponseCode() === 401) {
    response = _fetchData(props, sessionId, dryRun);
  }
  const rows = JSON.parse(response).data.rows;

  // Append the rows to the sheet
  sheet.appendRow(columns);
  const values = sheet.getDataRange().getValues();
  rows.forEach((row) => {
    values.push(row);
  })

  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

const _initialize = () => {
  const env = environment;
  const queryResultSheet = _initializeQueryResultSheet();
  const password = _getPassword();

  return {
    sheets: {
      queryResult: queryResultSheet,
    },
    env: env,
    password: password
  };
};

const _fetchData = (props, sessionId, dryRun) => {
  if (!sessionId) { sessionId = _getSessionId(props) }

  const payload = {
    database: tableNumInMetabase[props.env.toUpperCase()],
    native: {
      query: `SELECT ${columns.join(', ')} FROM ${tableName}`
    },
    type: 'native'
  };

  const options = {
    method: 'post',
    muteHttpExceptions: true,
    headers: {
      'Content-Type': 'application/json',
      'X-Metabase-Session': sessionId
    },
    payload: JSON.stringify(payload),
  };
  console.log(`dryRun = ${dryRun}`, options);
  if (dryRun) return;

  //console.log(`line89: ${props.env}`);
  const response = UrlFetchApp.fetch(URL[props.env.toUpperCase()] + 'api/dataset', options);
  return response;
}

/**
 * Delete the existing queryResult sheet and create a new one.
 **/
const _initializeQueryResultSheet = () => {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(queryResultSheetName);
  if (sheet) {
    ss.deleteSheet(sheet);
  } else {
    console.log(`Sheet: ${queryResultSheetName} not found`);
  }

  ss.insertSheet(queryResultSheetName);
  return ss.getSheetByName(queryResultSheetName);
}

const _getPassword = () => {
  let password = PropertiesService.getScriptProperties().getProperty('passwordProp');
  if (!password) {
    password = Browser.inputBox('Input the password');
    PropertiesService.getScriptProperties().setProperty('passwordProp', password);
  }
  return password;
}

const _getSessionId = (props) => {
  _delSessionId();

  const payload = {
    username: userName,
    password: PropertiesService.getScriptProperties().getProperty('passwordProp')
  };

  const options = {
    method: 'post',
    headers: {
      'Content-Type': 'application/json'
    },
    payload: JSON.stringify(payload),
  };

  const response = UrlFetchApp.fetch(URL[props.env.toUpperCase()] + 'api/session', options);
  const sessionId = JSON.parse(response).id;
  PropertiesService.getScriptProperties().setProperty('sessionIdProp', sessionId);
  return sessionId;
}

const _delPassword = () => {
  PropertiesService.getScriptProperties().delProperty('passwordProp');
}

const _delSessionId = () => {
  PropertiesService.getScriptProperties().deleteProperty('sessionIdProp');
}

const _getProperties = () => {
  console.log(PropertiesService.getScriptProperties().getProperties());
}

const _deleteAllProperties = () => {
  console.log(PropertiesService.getScriptProperties().deleteAllProperties());
}
```