LoginSignup
4

More than 3 years have passed since last update.

ゲームのマスタ管理で使うスプレッドシートに欲しいといわれがちな機能

Last updated at Posted at 2019-12-13

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

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

はじめに

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

必要機能

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

オススメの関数

googletranslate関数

google翻訳してくれます。

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

e2528f61-c7f1-1269-b3b4-8268d2b6ff8e.png

query関数

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

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

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

a0a501c4-4efd-ae94-68f2-23e78f472297.png

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の値を参照します。

4f48f2e5-6247-c49c-c910-eb44be9326f1.png

importrange関数

=importrange(取得したいスプレッドシートのURL,シート名!範囲)
=importrange("https://docs.google.com/spreadsheets/d/hogehoge/edit","シート!A1:F")

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

80ad38b8-7a33-9884-4ffe-3bcb85bfe799.png

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

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

78fb177b-6d66-1284-f036-47c986daebf2.png

substitute関数

=substitute(文字列, 検索文字列, 置換文字列, 置換対象)
=substitute(B2,"{{ 0 }}",C2)

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

ed0d62a3-0390-f410-d160-dfafa32047eb.png

条件付き書式あれこれ

重複した値に色つける

カスタム数式
=countif(A:A,A:A)>1

ea5c8b22-cb0c-ff0b-0a66-0eb25b3ebf29.png

×にした列に色を塗る

カスタム数式
=A1="×"

611e1f93-a145-bbcc-0e8f-c54a8617405b.png

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
  });
}

8f77c7ae-d419-51ab-401b-e082ef076481.png

最後に

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
4