【GAS】カラオケの料金比較グラフ自動生成に挑戦した話

# きっかけ
カラオケの料金を比較するグラフをGASで作成したい！
ワンドリンクがない飲み放題の店もあり、時間が短ければ固定費がかからない→そっちの方が得じゃん！ってなったことがあったりしたので...
# 条件
1, 時間、料金、固定費（ワンドリンク）を基にしてグラフを作成したい
2, ユーザーが入力すべき情報は利用時間、時間ごとの料金、固定費のみにすること
3, スマホから実行できるようにすること
# 成果物イメージ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470400/bf5c2169-06fe-196f-ea54-8002dacb64e7.png)

# GASで作ってみる
まず、計算開始、リセットなどの操作を入力するコントローラーを作る
下ごしらえにチェックボックスをE2,F2に配置してください！（お好みの場所でも可）
### イメージ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470400/ec50d9d1-fa3d-7ca7-b0aa-aa1d438a1c49.png)

### app script
```javascript:code.gs
function onEdit(e) {
    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    const inputSheet = spreadsheet.getSheetByName('input');

    // リセットボタン
    const resetButtonRange = inputSheet.getRange(2, 6);
    const isResetButtonTrue = resetButtonRange.getValue();
    if (isResetButtonTrue) {
        // 文字のみを削除、書式は維持
        const inputLastRow = inputSheet.getLastRow();
        const inputRange = inputSheet.getRange(2, 1, inputLastRow - 1, 4);
        inputRange.clearContent();

        // リセットボタンをfalseに変更
        resetButtonRange.setValue(false);
        // ログ表示用セルに表示
        inputSheet.getRange(5, 5).setValue("データがリセットされました。");
    }

    // 計算開始ボタン
    const calcButtonRange = inputSheet.getRange(2, 5);
    const isCalcButtonTrue = calcButtonRange.getValue();
    if (isCalcButtonTrue) {
        // ログを表示
        inputSheet.getRange(5, 5).setValue("計算中...");
        // ボタンをfalseに変更
        calcButtonRange.setValue(false);
        // グラフ作成関数を呼び出す
        createChart();
    }
}

```
注意！
getRangeは1から始まるため、Aの場合は1, Bならば2を入力する必要がある
#### inputLastRow - 1 について
getRangeは
`getRange(開始行, 開始列, 行数, 列数)`のような形がある。今回の場合、開始行が2のため、-1している。
例: 2-5行を削除したい場合、2行目から4行分削除する。
一般化すると、開始行-最終行まで削除したい場合は、開始行から(最終行 - 開始行 + 1)行分削除するということになる。

# グラフ作成
まず、料金の計算はこんな感じ
```javascript:code.gs
// 時間の入力
const valueOfTime = inputSheet.getRange('A2').getValue();
// rowはそれぞれ、0: name, 1: fee, 2:fixedCost となっている
const outputValues = data.map(row => [row[0], row[1], row[2]]);

// E2より下にvalueOfTime まで繰り返す。数値を calcSheet に出力
for (let i = 0; i <= valueOfTime; i++) {
    calcSheet.getRange(i + 2, 4).setValue(i); // E2から始まるため i + 1
    calcSheet.getRange(i + 2, 5).setValue(i * 30); // 表示用時間
}

// セルに式（関数）を出力
const names = data.map(row => row[0]);
    for (let i = 0; i < names.length; i++) {
        calcSheet.getRange(1, 6 + i).setValue(`=A${i + 2}`);
        for (let j = 0; j <= valueOfTime; j++) {
            calcSheet.getRange(j + 2, 6 + i).setValue(`=B${i + 2} * ${j} + C${i + 2}`);
        }
    }

// resultSheetに最安値を出力
const priceRange = calcSheet.getRange(valueOfTime + 2, 6, 1, names.length);
const priceValues = priceRange.getValues();
const minValue = Math.min.apply(null, priceValues[0]); // ２次元配列のため、columnを指定
const minIndex = priceValues[0].indexOf(minValue);
const minPriceKaraoke = calcSheet.getRange(1, minIndex + 6).getValue(); // minIndexまでに５列ある, indexは0から始まる => +5 +1
const resultValues = [minPriceKaraoke, minValue]

```
上の処理は、1列目に挙げられた必要なデータと、時間のデータをあわせて、セルに計算式をfor文で出力している。（下の画像のようになる）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470400/35fe2b2c-7780-4b91-2157-b23b3980c3fc.png)

それでは、グラフの作成をする
余談ですが、見出しを作るのプロパティがわからず、3時間ぐらい苦戦していました:sweat_drops:
```javascript: createChart
// グラフ削除をする関数（ここでは割愛）
deleteExitsingCharts(resultSheet);

// グラフの設定
const chart = resultSheet.newChart()
    .setChartType(Charts.ChartType.LINE)
    .addRange(dataRange)
    .setPosition(3, 1, 0, 0)
    .setOption('title', 'カラオケ料金')
    .setOption('hAxis.title', '時間（分）')
    .setOption('vAxis.title', '料金')
    .setOption('pointSize', 5)
    .setOption('legend', {position: 'top'})
    .setNumHeaders(1)
      
// resultSheetにグラフを追加
resultSheet.insertChart(chart.build());

```
setOptionの 'legend'が本当にわからなかった；；
下のようなグラフができたら成功です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470400/f1b9533b-0290-8eb9-94a5-4d7f981ac2b4.png)

# 完成...?
ではありません。これから認証ボタンを実装します
ぶっちゃけ自分で使う用なら要らないし、公開する用にしては手間が多すぎるのですが...
```javascript:init.gs
function createTrigger() {
  const triggers = ScriptApp.getProjectTriggers();

  // 既存のトリガーを削除
  triggers.forEach(trigger => {
    if (trigger.getHandlerFunction() === 'onEdit') {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  // 新しいトリガーを作成
  ScriptApp.newTrigger('onEdit')
    .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
    .onEdit()
    .create();
}

```
というような感じです。これを適当な図形を作ってそこに割り当てましょう！（認証も何も、計算ボタンも図形に割り当てればよかったのでは...？）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3470400/c0896fba-9e52-9900-539a-8fbeb461d2f2.png)

これでようやく完成です。
# 完成！
少し端折りましたが、やりました！完成です！
これには、「何時間を超えたら、〇〇の方がお得」ということがわからないだとか、認証が面倒くさすぎるという残された課題があります。
GASって楽しいし、意外と簡単にできるのでぜひ日常的に使ってみてください〜！

# 参考
https://qiita.com/sakaimo/items/9c0205699b9646ffeb46
https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet?hl=ja