🦔

GASを使ったカスタムバリデーションとBigQueryへのデータロード

2022/03/08に公開

GAS

BigQuery

tech

背景・目的

事業のダッシュボードを作るために、スプレッドシートで管理している数字をBigQueryでテーブル化する必要があった。
スプレッドシートは手動で更新しているので、不正なデータが入る可能性が多分にあり、品質が担保されたデータだけを分析基盤に入れたい。

前提

開発環境としてはGCPを使っている
GCPは別のチームも使っている

選択肢

当時の自社の状況に照らし合わせたメリデメ比較。

選択肢	メリット	デメリット
スプレッドシートをそのまま外部テーブル化	運用がいらず、常に最新のテーブルを提供できる。	IF更新中にクエリが投げられるとエラーになりそう。バリデーションがかけられない。
GCSに配置し、Cloud Functionsで移送	自由にバリデーションをかけられる。GCS配置をトリガーにできるので、運用が楽。	開発必要。Cloud Functionsは別のTMでも使っているので、テスト工数が増え、運用手順などもガチガチに決める必要があるので、時間がかかりそう。
GCSに配置し、BigQuery Data Transfer Serviceで移送	実装少なそう。	バリデーションがかけられない。
GASでスプレッドシートから移送	自由にバリデーションをかけられる。	開発必要。

分析基盤の手前でバリデーションを実施したい
デリバリーの制約があったため、できれば自TMで完結させてスピーディーに開発したい
という観点から、GASでのデータロードを選択しました。

要件

では、本題のGAS。
やりたいことはシンプルに二つ。

GASでスプレッドシートの中身に対してバリデーションをかける。
バリデーションが通ったら、BigQueryにロードする。

データ仕様

スプレッドシート・ロード先のテーブルの中身は以下のように定義する。

カラム名	データ型	意味
id	STRING	一意に定義するユニークなID
year	STRING	年
month	STRING	月
weekday	STRING	曜日
num	INT	ユーザ数などの数字

バリデーション

バリデーションする要件は以下。

idがユニークか
yearが2022以上
monthが1以上12以下
weekdayが月曜〜日曜
id~weekdayにNULL、空白がないか
id~weekdayのカラムが存在するか

ロード

ロードする要件は以下。
project="{自分のプロジェクトID}"
dataset="{自分のデータセットID}"
table="target_table"

実装

validation.js

function validateData(sheet) {
  
  // 最終行を取得
  var lastRow = sheet.getLastRow(); 

  // バリデーションをかける範囲を取得
  var colId = sheet.getRange(2, getCol("id", sheet), lastRow-1).getValues()
  var colYear = sheet.getRange(2, getCol("year", sheet), lastRow-1).getValues()
  var colMonth = sheet.getRange(2, getCol("month", sheet), lastRow-1).getValues()
  var colWeekDay = sheet.getRange(2, getCol("weekday", sheet), lastRow-1).getValues()
  
  // バリデーションルールを設定
  var errorId = colId.flat().filter(function(value, i, self){
    return (self.indexOf(value) === i && i !== self.lastIndexOf(value)) || value == ""
  });
  var errorYear = colYear.filter(function(value){
    return Number(value)<2022
  });
  var errorMonth = colMonth.filter(function(value){
    const pattern = ["1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"];
    return pattern.indexOf(String(value))==-1
  });
  var errorWeekDay = colWeekDay.filter(function(value){
    const pattern = ["月曜", "火曜", "水曜", "木曜", "金曜", "土曜", "日曜"];
    return pattern.indexOf(String(value))==-1
  });

  var errorLengthDict = {
    "id" : errorId.length,
    "year" : errorYear.length,
    "month" : errorMonth.length,
    "weekday" : errorWeekDay.length
  }
  
  // すべてのエラー数の合計値を計算
  const errorSum = Object.values(errorLengthDict).reduce((prev, current) => prev + current, 0);

  // エラーが一つもなければTrue
  if(errorSum==0){
    return true
  }else{
    var errorDict = {
    "id" : errorId,
    "year" : errorYear,
    "month" : errorMonth,
    "weekday" : errorWeekDay
  }
    throw new Error("validation rule error");
  }

}

// カラム名を指定して、該当カラム番号を返す。該当するカラムがIFになければエラーを投げる。
function getCol(string, sheet) {
  var lastCol = sheet.getLastColumn();  
  var range = sheet.getRange(1, 1, 1, lastCol)  
  var values = range.getValues()[0];  

  for (i=0; i<lastCol; i++) {
    if (values[i] == string) {
      return i + 1; // カラム番号を返す
    }
  }
  throw new Error('file does not have the column:'+string);
}

insert.js

function insertData(sheet, projectId, datasetId, tableId) {
  const jsonl = getData(sheet);

  const data = Utilities.newBlob(jsonl);
  const resultInsert = BigQuery.Jobs.insert({
    configuration: {
      load: {
        destinationTable: { projectId, datasetId, tableId },
        sourceFormat: 'NEWLINE_DELIMITED_JSON'
      }
    }
  }, projectId, data);
  return resultInsert;
}

//データ取得
function getData(sheet) {
  //行（横軸）と列（縦軸）の最大数を取得
  const maxRow = sheet.getLastRow();
  const maxColumn = sheet.getLastColumn();

  //key格納配列
  const keys = [];

  //データ格納配列
  const data = [];

  //1行目のkeyの名前取得
  for (var x = 1; x <= maxColumn; x++) {
    keys.push(sheet.getRange(1, x).getValue());
  }

  //データの取得
  const _values = sheet.getRange(2, 1, maxRow-1, maxColumn).getValues();
  
  for(var i = 0; i < _values.length; i++){
    const json = {};
    for (var k = 0; k < _values[i].length; k++) {
      json[keys[k]] = _values[i][k];
    }
    data.push(json);
  }

  //JSONLに変換
  return data.map(o => JSON.stringify(o)).join('\n');
}

main.js

function main() {
  // データ取得するシート情報を指定
  const spreadSheet = SpreadsheetApp.openByUrl("{スプレッドシートURL}");
  const sheet = spreadSheet.getSheetByName("{シート名}");

  // 更新先のテーブル情報を指定
  const projectId = "{自分のプロジェクトID}";
  const datasetId = "{自分のデータセットID}";
  const tableId = "target_table";
  
  try{
    // バリデーションを実行
    var resultValidation = validateData(sheet);

    // バリデーションをpassすればinsertを実行
    if(resultValidation){
      console.log("validation passed");
      resultInsert = insertData(sheet, projectId, datasetId, tableId);
    }
  }catch(e){
    console.error(e.message);
  }
}

注意点

GASを動かすアカウントはBigQueryの編集権限を持っている必要があります。
GASから出てくるログは微妙なので、GCPのロギングコンソールでデバッグした方がいいと思います。