グレンジ Advent Calendar 2019 14日目担当の yoshida029です。
サーバーエンジニアをやっています。

新規プロジェクトでゼロからマスタ管理ツール作成する機会があったので
作成過程で覚えた機能紹介していきます。

はじめに

ゲームのマスタを作成する補助ツールです
スプレッドシートで作成します
スプレッドシート→CSVに出力、CSVをgitに反映、jenkinsで自動デプロイします
エンジニアとプランナー双方にとって幸せになるマスタ管理ツールを目指します

必要機能

CSVエクスポート機能、一括エクスポート機能
必要のない行・列の〇×制御
bool,enumなどの選択入力
フォーマットチェック、マスタ間のID存在チェック
ソート順、ID重複チェック
別マスタのIDから名称取得
別のスプレッドシートと同期・連携

オススメの関数

googletranslate関数

google翻訳してくれます。

=googletranslate(訳したいセル,翻訳元の言語設定,翻訳先の言語設定)

例

=googletranslate(訳したいセル,"ja","en")

query関数

=query(データ, クエリ, [見出し])

下記の例だとA列に〇があるレコードを取得します

例

=query(A$2:$C,"select B,C where A='○'")

query関数は複数の取得した結果をUnionすることができます。

例

=query({
    query(Item1!C$1:$F,"select A,B,C where C is not null");
    query(Item2!C$1:$F,"select A,B,C where C is not null");
})

indirect関数

=indirect(参照文字列)

他のスプレッドシートから動的にセルを参照するのに便利です。

下記の例だと、各シートのA1の値を参照します。

importrange関数

=importrange(取得したいスプレッドシートのURL,シート名!範囲)

例

=importrange("https://docs.google.com/spreadsheets/d/hogehoge/edit","シート!A1:F")

他のシートからデータを持ってくるときに使います。

この関数はvookupと組み合わせることで真価を発揮します。
下記の例だとH列のキーに一致する別シートのNAMEを取得します。

例

=VLOOKUP(H14,importrange("https://docs.google.com/spreadsheets/d/hogehoge/edit","test1!B1:C"),2,false)

substitute関数

=substitute(文字列, 検索文字列, 置換文字列, 置換対象)

例

=substitute(B2,"{{ 0 }}",C2)

文字列置換します。メッセージの置換埋め込みの確認にどうぞ。

条件付き書式あれこれ

重複した値に色つける

カスタム数式

=countif(A:A,A:A)>1

×にした列に色を塗る

カスタム数式

=A1="×"

Google Apps Script (GAS)

連想配列作成

セルからCSV出力するわけですが、連想配列にしておくと後続の処理便利です。

function loadData() {
  var indexSheet = SpreadsheetApp.getActive();
  var dataListMap = {};
  loadFilenameList.forEach(function(filename) {
    dataListMap[filename] = sheetToHash(indexSheet, filename);
  })
  return dataListMap;
}

function sheetToHash(sheet, fileName) {
  var sh = sheet.getSheetByName(fileName);
  var lastRowNum = sh.getLastRow();
  var lastColumnNum = sh.getLastColumn();
  var headerList = sh.getRange(HEADER_ROW + 1, 3, 1, lastColumnNum).getValues();
  var idList = sh.getRange(START_ROW + 1, 3, lastRowNum, 1).getValues();
  var dataList = sh.getRange(START_ROW + 1, 3, lastRowNum, lastColumnNum).getValues();
  var hashList = {};
  dataList.forEach(function(data, i) {
    if (idList[i] != "") {
      var hash = {};
      data.forEach(function(value, j){
        hash[headerList[0][j]] = value;
      })
      hashList[idList[i]] = hash;
    }
  })
  return hashList;
}

出力フォルダ探索

出力フォルダを探すとき、フォルダ探索＆なければ作成をします。

function getFolderByEnv(env) {
  var baseFolderId = 'フォルダID';
  var outputFolder = DriveApp.getFolderById(baseFolderId);
  var envFolderList = outputFolder.getFolders();

  if (env == "develop") {
    while (envFolderList.hasNext()) {
      var envFolder = envFolderList.next();
      if (env == envFolder) {
        return envFolder;
      }
    }
  } else {
    while (envFolderList.hasNext()) {
      var envFolder = envFolderList.next();
      if (envFolder == 'feature') {
        var featureFolderList = envFolder.getFolders();
        while (featureFolderList.hasNext()) {
          var featureFolder = featureFolderList.next();
          if (env == featureFolder) {
            return featureFolder;
          }
        }
        // 存在しない場合作る
        return envFolder.createFolder(env);
      }
    }
  }
}

進捗の見える化

作業中暇なので、作業テーブルに背景色をつけたり、終わったら更新日付を埋め込もう。

  for (var i = 1; i < indexList.length; i++) {
    // ファイル名を取得
    var fileName = indexList[i][テーブル名列];

    // 作業レコードを取得
    var range = indexSheet.getRange(i + 1, 1, 1, 8);
    
    // 作業レコードに色をつける
    range.setBackground('LIME');

    // CSV出力
    var result = exportCsv(ss, env, fileName, outputFolder);
    if (result) {
      // 最終更新日
      indexSheet.getRange(i + 1,出力列).setValue(Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy-MM-dd HH:mm:ss'));
    }
    // 元に戻す          
    range.setBackground(null);
  }

高速化

APIを使うこと回数を減らすのが一番早いので、複数回参照する場合はメモシートを作る。

function getIndexSheet() {
  if (getIndexSheet.memoSheet) {
    return getIndexSheet.memoSheet;
  }

  getIndexSheet.memoSheet = SpreadsheetApp.getActive().getSheetByName('index');

  return getIndexSheet.memoSheet;
}

slackに投稿する

処理終わったらslackに投稿しよう。

function hoge() {
    var title = '個別エクスポート';
    var message = 'CSV出力されました' + "\n";
    message += '```' + fileName + '```' + "\n";
    message += "実行者：" + Session.getActiveUser().getEmail() + "\n";
    postSlack(title, message);
}

function postSlack(title, message) {
  var payload = JSON.stringify({
      "attachments": [{
        "color": "good",
        "title": title,
        "text": message,
        'footer': 'https://drive.google.com/drive/u/0/folders/xxxx'
      }]
  });  

  // 投稿
  UrlFetchApp.fetch('https://hooks.slack.com/services/xxxx', {
    "method": "post",
    "contentType": "application/json",
    "payload": payload
  });
}

最後に

一つでも使える機能を知ってもらえたら幸いです。