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

[グレンジ Advent Calendar 2019](https://qiita.com/advent-calendar/2019/grenge) 14日目担当の yoshida029です。
サーバーエンジニアをやっています。

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

# はじめに

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

# 必要機能

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

# オススメの関数
## googletranslate関数

google翻訳してくれます。

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

```text:例
=googletranslate(訳したいセル,"ja","en")
```

![e2528f61-c7f1-1269-b3b4-8268d2b6ff8e.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218170/130053a4-a17f-183e-c994-f1c5f5cac8a6.png)



## query関数

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

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

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

![a0a501c4-4efd-ae94-68f2-23e78f472297.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218170/d074832f-8793-1877-af28-cf7e4672b45b.png)


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

```text:例
=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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218170/e689ed9d-7ef3-6cb5-5f50-d0e371e82cf3.png)


## importrange関数

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

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

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

![80ad38b8-7a33-9884-4ffe-3bcb85bfe799.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218170/d99789f5-62fb-5ee8-43cc-80da0a3f1b35.png)

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

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

![78fb177b-6d66-1284-f036-47c986daebf2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218170/1d4cb79d-490d-2b7e-9c58-5da7c0166ce0.png)

## substitute関数

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

```text:例
=substitute(B2,"{{ 0 }}",C2)
```

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

![ed0d62a3-0390-f410-d160-dfafa32047eb.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218170/87104143-e23d-718f-e7df-83be459e745a.png)


# 条件付き書式あれこれ

## 重複した値に色つける

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

![ea5c8b22-cb0c-ff0b-0a66-0eb25b3ebf29.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218170/3345132c-16fd-b0d2-1c84-f20985c19ff9.png)


## ×にした列に色を塗る


```text:カスタム数式
=A1="×"
```

![611e1f93-a145-bbcc-0e8f-c54a8617405b.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218170/03662040-816d-af4b-c4c4-3b01bebe23b5.png)

# Google Apps Script (GAS)

## 連想配列作成

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

```gas
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;
}
```

## 出力フォルダ探索

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

```gas
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);
      }
    }
  }
}
```
## 進捗の見える化

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

```gas
  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を使うこと回数を減らすのが一番早いので、複数回参照する場合はメモシートを作る。

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

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

  return getIndexSheet.memoSheet;
}
```

## slackに投稿する

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

```gas
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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218170/e58666ac-0d78-12f3-9e59-cfee5e62a00d.png)


# 最後に

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