##はじめに
概要：GAデータをRDSへ格納してSELECT結果をスプレッドシートへ反映する。

とりあえず考えた以下の３案で最も管理コストが低そうなものをチョイス
◯ APIGatewayでLambdaリクエストによるデータのやり取りを行う
　→ エラー通知さえ作れば保守がラクそう。既存のアカウントで運用可。
× GCPアカウントを利用してスプレッドシートAPIをlambdaで使う
　→ 持ってないアカウント管理が増えるからやだ。
× S3にデータを配置してputをトリガーにやり取り
　→ S3のデータソースを使う必要性はない、IOが多い。

データの流れ
スプレッドシート ⇔ GAS ⇔ APIGateway ⇔ Lambda ⇔ RDS

使うリソース

GAS
GAアドオン
APIGateway
Lambda(Node.js)
RDS(MySQL)
Systems Managerのパラメータストア
Key Management Service

##GAアドオンでレポートを取得
GA　⇨　スプレッドシート

Google Analyticsのアドオンを入れる
アドオンの Create new report でレポートを作成
アドオンの Schedule Reports で定期実行のスケージュールを設定

※アドオンはスプレッドシートではなくGoogleアカウントへ適応されるため、
　別の人も運用する場合はアドオンの導入が必要です。

##GASのスケジューラを起点にしたデータの流れ
スプレッドシート ⇔ GAS ⇔ APIGateway ⇔ Lambda ⇔ RDS

###GASでレポートの値を取得

スプレッドシート ⇔ GAS ⇔ APIGateway ⇔ Lambda ⇔ RDS

.js

// スプレッドシート
const spreadsheetId = '********************************************';  
const ss = SpreadsheetApp.openById(spreadsheetId);
const sheetName = "pv_202008";
const sheet = ss.getSheetByName(sheetName);

// セル指定
// Sheetオブジェクト.getRange(行番号, 列番号)
const title = sheet.getRange(2,2).getValues();

// アドレス指定（範囲）
// Sheetオブジェクト.getRange(アドレス)
const data = sheet.getRange('A16:C3000').getValues();

//---
//よしなに整形しちゃう。

###GASでAPIへリクエストする
スプレッドシート ⇔ GAS ⇔ APIGateway ⇔ Lambda ⇔ RDS

UrlFetchApp.fetch(url, options)を用いてAPIのURLへリクエストします。

.js

function request(requestParams) {
  let result;
  const url = "https://hogehoge.execute-api.ap-northeast-1.amazonaws.com/hugafuga/api";
  const options = {
    "method": "POST",
    "contentType": "application/json",
    "headers": {
      "Accept": "application/json",
      "Content-Type": "application/json"
    },
    "payload": JSON.stringify(requestParams)
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    result = JSON.parse(response.getContentText());
    Logger.log(rows);
  } catch(e) {
    Logger.log("エラーの内容:" + e);
  }

  return result;
}

###API Gatewayの作成
スプレッドシート ⇔ GAS ⇔ APIGateway ⇔ Lambda ⇔ RDS

今回はHTTP APIを用います。
API Gatewayの作成はLambdaのトリガー追加、又はAPI Gatewayのサービス上で作成します。

それぞれ作成時にLambda関数への紐付けを行えます。

API Gatewayのサービス上から作成する場合


Lambda関数のトリガー追加から作成する場合

ルートの設定
GASからPOSTリクエストするのでPOSTのルートを設定します。

作成後APIエンドポイントのURLが発行されます。
API エンドポイント: https://*******.execute-api.ap-northeast-1.amazonaws.com/default/test

###Lambdaで利用するDBアクセス情報の秘匿化

今回はDBアクセスを行うためアクセス情報の秘匿化をあわせて行います。
Systems Managerのパラメータストアにデータを保存し、
暗号化鍵の管理にKey Management Serviceを利用します。

鍵の作成
Key Management Service → カスタマー管理型のキー　→　キーの作成

ステップ１：キーを設定
「対応」を選択したまま次へ

ステップ２：ラベルを追加
KEYにアクセスするための名称をつけます。

ステップ３：キーの管理アクセス許可の定義
任意のユーザに管理権限をつけます。

ステップ４：キーの使用アクセス許可を定義
Lambda関数に付与しているロールへ権限を付与します。

パラメータストアへDB情報の作成
AWS Systems Manager → パラメータストア　→　パラメータの作成

※ユーザ、パスワードそれぞれ作成します。
名前：任意
タイプ：安全な文字列
KMSキーソース：現在のアカウント
KMSキーID：先程作成したキー
値：DB情報

###Lambdaで利用するDBアクセス情報の取得
今回はNode.jsでのサンプルを作ります。
SSMへリクエストして先程作成したパラメータを取得します。

Lambda関数からSSMへアクセスするためにSGを設定します。

ssm:GetParametersとsts:AssumeRoleの権限を付与し、
リソースに先程作成したパラメータのARNを設定したポリシーを作成し、ロールへアタッチします。

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "sts:AssumeRole",
                "ssm:GetParameters"
            ],
            "Resource": [
                "arn:aws:ssm:ap-northeast-1:226920958931:parameter/prod/env/database__password",
                "arn:aws:ssm:ap-northeast-1:226920958931:parameter/prod/env/database__user"
            ]
        }
    ]
}

aws-sdk/clients/ssmのgetParametersを利用してパラメータストアから値を取得します。

.js

let connection;
let connectionParams = {
  connectTimeout: 600000,
  host : ”エンドポイントを設定”,
  database : ”DB名を設定”
};

async function getSSMParams() {
  const ssm = new (require('aws-sdk/clients/ssm'))();
  const ssm_response = await ssm.getParameters({
      Names: [
        '/prod/env/database__user',
        '/prod/env/database__password'
      ],
      WithDecryption: true   //暗号化してる場合はtrueで複合化
  }).promise();
    
  if (ssm_response.Parameters != null) {
    let params = {};
    for (const param of ssm_response.Parameters) {
      params[param.Name] = param.Value;
    }
    connectionParams.user = params['/prod/env/database__user'];
    connectionParams.password = params['/prod/env/database__password'];
  }
}

###LambdaからMySQLへアクセスする
スプレッドシート ⇔ GAS ⇔ APIGateway ⇔ Lambda ⇔ RDS

今回はNode.jsにてMysqlへアクセスを行います。
node_modulesのmysqlを利用します。
createConnectionに下記パラメータを渡してコネクションを作成します。

host
database
user
password

今回は環境変数にhost (endpoint) / database (db名)の情報を設定しています。


user / passwordは前述のパラメータストアから取得します。

.js

const mysql = require('mysql');
let connectionParams = {
  host : process.env['endpoint'],
  database : process.env['db'],
};

exports.handler = async function(event, context, callback) {
  // DB情報取得
  await getSSMParams();
  connection = mysql.createConnection(connectionParams);
  connection.connect(function(err) {
    if (err) {
     console.error('error connecting: ' + err.stack);
     callback(new Error('failure'));
     response = getResponse('500', err);
     console.log("response: " + JSON.stringify(response));
     
     return response;
    }
    
    console.log('connected as id ' + connection.threadId);
  });
  console.log("Starting connection ...");

  // ここでSQL実行したりする
　// ～　省略　～
});

上記でコネクションが作成できました。

リクエストから値を取得してSQLを実行してみる

.js

// INSERT
const row = JSON.parse(event.body); // {id: 1, title: 'Hello MySQL'};
const sql = "INSERT INTO posts SET ?";
const query = connection.query(sql, post, (error, results, fields) => {
  if (error) throw error;
  // ...
});
console.log(query.sql); // INSERT INTO posts SET id = 1, title = 'Hello MySQL'

プレースホルダーを利用したデータの取り扱いはこちらの記事を参考にお試しください。

###おまけ２：LambdaからGASにレスポンスを返す
スプレッドシート ⇔ GAS ⇔ APIGateway ⇔ Lambda ⇔ RDS
データを取得してGAS側へ返すレスポンスを作成

.js

async function getResponse(status, responseBody) {
  return {
    "statusCode": status,
    "headers": {
      "Content-Type": "application/json"
    },
    "isBase64Encoded": false,
    "multiValueHeaders": { 
      "X-Custom-Header": "***************",
    },
    "body": JSON.stringify(responseBody)
  }
};

###おまけ２：GASでスプレッドシートにデータを格納する
getRangeで範囲指定して、setValuesで配列を渡すことで一括挿入ができます。
合わせてsetBorderで枠線をつけることもできます。

.js

// スプレッドシート
const spreadsheetId = 'ほげほげほげお_ふがふがお';  
const ss = SpreadsheetApp.openById(spreadsheetId);

const result = requestApi(requestParams);
if (Object.keys(result).length) {
  setNewRanking(result);
}

function setNewData(values) {
  // データを取得
  const sheet = ss.getSheetByName("シート名");
  const numRows = values.length;
  const numColumns = Object.keys(values[0]).length;
  Logger.log(numRows);
  Logger.log(numColumns);
  
  // 挿入用objectに整形
  let valuesArr = [];
  for (let i = 0; i < numRows; i++) {
    valuesArr.push(Object.values(values[i]));
  }

  sheet.insertRows(2,numRows + 2); // 日付と余白を挿入
  sheet.getRange(3, 1, numRows + 1, numColumns).setValues(valuesArr).setBorder(true, true, true, true, true, true);
}

###おまけ３：NatがないVPCでLambdaから他AWSサービスへアクセスする
PrivateLinkを作成してエンドポイント経由することでアクセスが可能です。

VPC　→　エンドポイント　→　エンドポイントの作成

サービスカテゴリ：AWSサービス
サービス名：com.amazonaws.ap-northeast-1.ssm
VPC：対象のVPC
サブネット：対象のサブネット
セキュリティグループ：Lambdaに利用しているセキュリティグループ

作成してステータスが使用可能になれば疎通可能です。

###最後に
今回はGAデータを用いたRDS更新までの大まかな設定を行いました。
あくまで疎通できるところの要点のみをピックアップしております。
サービス可する際にはAPIGatewayによるセキュリティ対応やLambdaのerrorハンドリング・通知など
運用面に必要な諸々を設定しましょう。