きっかけ
カラオケの料金を比較するグラフをGASで作成したい！
ワンドリンクがない飲み放題の店もあり、時間が短ければ固定費がかからない→そっちの方が得じゃん！ってなったことがあったりしたので...
条件
1, 時間、料金、固定費（ワンドリンク）を基にしてグラフを作成したい
2, ユーザーが入力すべき情報は利用時間、時間ごとの料金、固定費のみにすること
3, スマホから実行できるようにすること
成果物イメージ
GASで作ってみる
まず、計算開始、リセットなどの操作を入力するコントローラーを作る
下ごしらえにチェックボックスをE2,F2に配置してください！（お好みの場所でも可）
イメージ
app script
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)行分削除するということになる。
グラフ作成
まず、料金の計算はこんな感じ
// 時間の入力
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文で出力している。（下の画像のようになる）
それでは、グラフの作成をする
余談ですが、見出しを作るのプロパティがわからず、3時間ぐらい苦戦していました
// グラフ削除をする関数（ここでは割愛）
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'が本当にわからなかった；；
下のようなグラフができたら成功です。
完成...?
ではありません。これから認証ボタンを実装します
ぶっちゃけ自分で使う用なら要らないし、公開する用にしては手間が多すぎるのですが...
function createTrigger() {
const triggers = ScriptApp.getProjectTriggers();
// 既存のトリガーを削除
triggers.forEach(trigger => {
if (trigger.getHandlerFunction() === 'onEdit') {
ScriptApp.deleteTrigger(trigger);
}
});
// 新しいトリガーを作成
ScriptApp.newTrigger('onEdit')
.forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
.onEdit()
.create();
}
というような感じです。これを適当な図形を作ってそこに割り当てましょう！（認証も何も、計算ボタンも図形に割り当てればよかったのでは...？）
これでようやく完成です。
完成！
少し端折りましたが、やりました！完成です！
これには、「何時間を超えたら、〇〇の方がお得」ということがわからないだとか、認証が面倒くさすぎるという残された課題があります。
GASって楽しいし、意外と簡単にできるのでぜひ日常的に使ってみてください〜！
参考
https://qiita.com/sakaimo/items/9c0205699b9646ffeb46
https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet?hl=ja