SpreadsheetにGASを仕込んで、発火のためのボタンを作っても、PCから開いた時しか押せない問題。

でも出先でスマホでささっと動かしたい時もあるじゃん～
という時は、「セルに入力したら発火する」というトリガーを使うと良いと聞いたのでそれでアプリを作って見ます。

今回作るのは最高に単純なお小遣い帳……というか、「今月いくら使ったよ帳」です。使った金額を入力すると、今月いくら使ったかを計算して表示してくれるようにしたいです。

一々GAS使わなくても出来るじゃんと言う感じもしますが、思った様なUI（１画面内で、上にぽちっと入れたら下にぱっと出るようにしたい）にならないのでGASでがんばります。

一番単純なのはセルAに入力、セルBに合計値を保存しておいて、Aに入力されたらBに足す、というだけですが、消えちゃったりしても面倒なので、シート１のセルAに入力→シート２に履歴保存→シート１のセルBに当月の合計値を表示、という仕様にしたいと思います。

０・入出力シートと履歴シートを作る

C3セルに数字を入れたらC5セルに当月合計が出るようにしたいものです。（スマホで開いた時に見やすいように余白セルを用意しています）

日付と金額だけ入れるようにしましょ。とりあえず手動でテストデータを入れました。

１・履歴シートから当月合計を計算する所を作る

const ACTIVE_SHEET = SpreadsheetApp.getActiveSpreadsheet();
const RECEIPT_SHEET = ACTIVE_SHEET.getSheetByName("履歴");
let receipt = RECEIPT_SHEET.getDataRange().getValues();

const DATE_COL = 0
const PRICE_COL = 1

const NOW = new Date();
const Y = NOW.getFullYear();
const M = NOW.getMonth() + 1;
const D = NOW.getDate();

function total() {
  let total_price = 0;
  for(let index in RECEIPT){
    let row = reseipt[index]
    let receipt_date = new Date(row[DATE_COL]);
    let receipt_y = receipt_date.getFullYear() + 1;
    let receipt_m = receipt_date.getMonth();
    if(receipt_y != Y || receipt_m != M){continue}

    let price = row[PRICE_COL];
    total_price = total_price + price;
  }
  return total_price
}

作りました。
履歴シートに貯まってる情報を二次元配列として取得して、forでぶん回して、当月のデータだけ足し算。
この二次元配列は後で使い回しまくるので、グローバルで定義してます。constじゃなくてletなのは後で値を追加する必要があるからです。（本来、グローバル変数定義するのはアレな実装なんですけども、セルの値を取得するオペレーションは一日辺りの上限回数が定められているので、できるだけ節約したいのでそんな実装にしてあります）

今日の日付取得したあと、YとMの他にDを定義してますが、これは後で使います。あと、Mだけ「月インデックス」とかいうJanuaryを0、Decemberを11とする数値で取れるので＋1してあげています。なんでそんな実装なの。

２・出力セルに結果を出力する所を作る

const INPUT_SHEET = ACTIVE_SHEET.getSheetByName("入力");
const INPUT_CELL = INPUT_SHEET.getRange("C3");
const OUTPUT_CELL = INPUT_SHEET.getRange("C5");

function output(value){
  OUTPUT_CELL.setValue(value);
}

関数化するほどのこともなかったけど一応……

３・入力された数値を履歴シートに転記する処理を作る

function input(value){
  let today = Y + "/" + M + "/" + D;
  let new_row = [[today, value]]

  let input_row = receipt.length;
  let input_start_col = DATE_COL + 1;
  let input_end_col = PRICE_COL + 1;
  let range = RECEIPT_SHEET.getRange(input_row, input_start_col, 1, input_end_col);
  range.setValues(new_row);
  receipt = RECEIPT_SHEET.getDataRange().getValues();
}

与えられた数値に今日の日付を添えて、シートに追記。
ここで設定したDATE_COLとかPRICE_COLは二次元配列に入れたときのインデックス基準ですが、getRangeするときはA1を(1,1)とする行番号／列番号で渡す必要があるのでそこを調整。
最後に配列を取得し直しているのは、日付周りの処理が面倒でスプレッドシート側の処理に任せた方がスムーズだったからです。

４・入力セルに入力があったら一連の処理を発火出来るようにする

セルの値が変更されたことを感知して一連の処理が走るように設定します。

function onEvent(event){
  Browser.msgBox("編集されました")
}

関数名がonEventで、引数を一つとる関数を用意しておくと、セルの値が変更されるごとにこの関数が発火します。何が起きたかは引数に自動的に入ってきます。

しかし、全てのセルの全ての変更を感知して発火してしまうので、該当のセルが変更されたのかどうかを判断する必要あります。

function is_input_cell(event){
  let sheet = event.source.getSheetName(); 
  let row = event.range.getRow();
  let col = event.range.getColumn();

  if(sheet != INPUT_SHEET.getSheetName()){return false}
  if(row != INPUT_CELL.getRow()){return false}
  if(col != INPUT_CELL.getColumn()){return false}
  return true
}

onEventからそのままeventをはいどうぞして、該当セルかどうかを判定する関数を切り出しておきます。関数は小さく小さく。
そんで、こう

function onEdit(event){
  if(!is_input_cell(event)){
    Browser.msgBox("そこじゃない");
    return
  }
  Browser.msgBox("入力されました")
}

これで、該当のセル以外に入力されたら処理終了、該当のセルに入力されたときだけ処理続行、という処理が作れました。

後は一連の処理を合体すれば完成です。

さらにこれを仕込んだスプレッドシートをドライブに保存しておいて、スマホからGoogleDriveアプリで開き、ショートカットをホームに追加しておけばアプリ感覚でサッと立ち上げられます。百点満点ですね。