プログラミング

GAS入門!初心者がスプレッドシートのエディタで家計簿を作成してみた!

今までお金の管理は全てエクセルで行っていましたが、ちょうど年も変わったので、GAS初心者がスプレッドシートのスクリプトエディタで作成した自作関数を使ってオリジナル仕様に変更をしました!

いぶきんぐ
いぶきんぐ

エクセルの家計簿、なんか、使いにくい…!

スマホでアプリに打ち込むのは面倒だし、見にくい!

そうだ!自分で作ってしまおう!

以下では、その作成方法を記載します!

はじめに

使うもの

スプレッドシート

以上!

参考図書

なかなか、Google Apps Scriptについてまとめて記載をしている本がないですが、こちらの本は基礎から実践まで体系的にマスターすることができるのでお勧めです!

インターン先で、G suiteを使用しているのですが、これを学んだことによって今までの面倒な作業を大幅に改善することができたので、Google Apps Script様様です。

業務改善にもオススメです!

是非、活用してみてください。

仕様

収支の管理に4枚のシートを使用します。

  • 1枚目は日々の支出の詳細
  • 2枚目は年間支出を月単位でカテゴリごとにまとめたもの
  • 3枚目は日々の収入の詳細
  • 4枚目は年間収入を月単位でカテゴリごとにまとめたもの

という仕様です。

雰囲気としては、このページに記入をして、

 

「追加したメニュー」の「支出計算」を押すと

下の写真のように、累計収入のページに記入してくれるプログラムです!

 

それでは、作っていきます!

前準備

1枚目の収入シートのD列カテゴリは、2枚目の2行目の累計収入シートのデータと同期して変更できるようにしたいので、その設定をします。

データの入力規制

手順はとても簡単です!

  1. 収入シートのD列のセルをどこでもいいので一カ所選択
  2. 「データ」を押して、その中の「データの入力規制」を選択
  3. 条件の「リストを範囲で指定」を選択
  4. 累計収入シートから選択したいカテゴリを選択

これだけです!

下に手順の写真を載せます。

これでOKです!

あとは、完成したセルをコピーするだけです!

さて、ここからが本番です!

コードを作成

スプレッドシートからシートを取得

まず、スプレッドシートからシートを取得します。

function spending_management() {
  var shuunyu = 0;
  var ruikei_shuunyu = 1;
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  Logger.log(sheets[ruikei_shuunyu].getName());
}


ログを確認して、「累計支出」と表示されていれば大丈夫です!

シートから指定したセルの値を取得

今回は、「支出」シートのD1の値を取得します。

D1には前回まで記録したデータの行数を記録します。

そのため、初めて記録する際は、D1に2を入れます。

var last_position = 'D1';
var pre_last_row = sheets[shuunyu].getRange(last_position).getValues()[0][0];
Logger.log(pre_last_row);

D1に入力した値である「2」がログに出力されていればOKです!

シートに値が記録されている行数を取得

何行目まで値が入っているかを確認します。

var last_row = sheets[shuunyu].getLastRow(); 
Logger.log(last_row);

これで、前回までに記録した行と、今回入力した行数を取得したので、その差分を計算します。

もし、変更がされていればさらに関数を実行すますが、変更がなければそこで終了するように条件分岐をします。

var data_num = last_row - pre_last_row;
  if(data_num == 0){
    return;
  }else{
  }

ここから、elseの中身を記述していきます。

連想配列(辞書型)の作成

連想配列ってなに!

僕も初めて知りました。Pythonで言うところの辞書型のようなものらしいです。

ここでは、カテゴリとそれに対応する、累計収入シートの列のアルファベットがセットになるようにしていきます。

  1. 累計収入シートからカテゴリのデータを配列で取得
  2. forEachメソッドを使って初期化した連想配列に値を入れる
var category_dict = {};
var category_list = sheets[ruikei_shuunyu].getRange('B2:M2').getValues()[0];
category_list.forEach(function(category, index, array){
    category_dict[category] = wmap_column_convert(index + 2);
});
Logger.log(category_dict);


こんな感じになっていればOKです!

ちなみに、連想配列の中身の順番はばらばらになるみたいです。

ここで、使用している「wmap_column_convert」関数は入力した数字のアルファベットを返す関数です。

入力した番号のアルファベットを返す自作関数

function wmap_column_convert(colmun_number) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var result = sheet.getRange(1, colmun_number);
  result = result.getA1Notation();
  result = result.replace(/\d/,'');
  return result;
}

 

新規追加したデータのみを計算する

var new_data = sheets[shuunyu].getRange('A'+String(pre_last_row+1)+':D'+String(last_row)).getValues();
for(var i=0;i<data_num;i++){
  var month_row = new_data[i][0].getMonth() + 3;
  var set_pos = category_dict[new_data[i][3]] + String(month_row);
  sheets[ruikei_shuunyu].getRange(set_pos).setValue(sheets[ruikei_shuunyu].getRange(set_pos).getValue() + new_data[i][2]);
}
sheets[shuunyu].getRange(last_position).setValue(last_row);

 

  1. 新規追加したデータを取得
  2. 1列目の日付から何月のデータなのかを取得
  3. 4列目のカテゴリから累計支出のどこに入力するのかを取得
  4. 累計支出シートの該当するセルに参照した値を足す
  5. 新規追加分だけ繰り返す
  6. 最後に、最終行をD1に記録して終了!

これだけです!

これで、計算をする関数自体は完成です!

最後は使いやすくするために、ボタンで実行できるようにします。

自作関数をスプレッドシート上のボタン実行できるように

function onOpen() {
  var ui = SpreadsheetApp.getUi();           
  var menu = ui.createMenu('追加したメニュー');  
  menu.addItem('支出計算', 'spending_management');
  menu.addToUi();                        
}

これで、毎回スクリプトエディタを開かなくてもシート上から関数を実行できるようになります。

最後に

収入でも全く同じように作っていけます!

コードまとめ

function wmap_column_convert(colmun_number) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var result = sheet.getRange(1, colmun_number);
  result = result.getA1Notation();
  result = result.replace(/\d/,'');
  
  return result;
}

function spending_management() {
  var last_position = 'D1';
  var shuunyu = 0;
  var ruikei_shuunyu = 1;
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  Logger.log(sheets[ruikei_shuunyu].getName());
  var pre_last_row = sheets[shuunyu].getRange(last_position).getValues()[0][0];
  Logger.log(pre_last_row);
  var last_row = sheets[shuunyu].getLastRow();
  Logger.log(last_row);
  var data_num = last_row - pre_last_row;
  if(data_num == 0){
    return;
  }else{
    
    var category_dict = {};
    var category_list = sheets[ruikei_shuunyu].getRange('B2:M2').getValues()[0];
    category_list.forEach(function(category, index, array){
      category_dict[category] = wmap_column_convert(index + 2);
    });
    Logger.log(category_dict);
    var new_data = sheets[shuunyu].getRange('A'+String(pre_last_row+1)+':D'+String(last_row)).getValues();
    for(var i=0;i<data_num;i++){
      var month_row = new_data[i][0].getMonth() + 3;
      var set_pos = category_dict[new_data[i][3]] + String(month_row);
      sheets[ruikei_shuunyu].getRange(set_pos).setValue(sheets[ruikei_shuunyu].getRange(set_pos).getValue() + new_data[i][2]);
    }
    sheets[shuunyu].getRange(last_position).setValue(last_row);
  }   
}

function income_management() {
  var last_position = 'D1';
  var shishutsu = 2;
  var ruikei_shishutsu = 3;
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  var pre_last_row = sheets[shishutsu].getRange(last_position).getValues()[0][0];
  Logger.log(pre_last_row);
  var last_row = sheets[shishutsu].getLastRow();
  Logger.log(last_row);
  var data_num = last_row - pre_last_row;
  if(data_num == 0){
    return;
  }else{
    var new_data = sheets[shishutsu].getRange('A'+String(pre_last_row+1)+':D'+String(last_row)).getValues();
    var category_dict = {};
    var category_list = sheets[ruikei_shishutsu].getRange('B2:K2').getValues()[0];
    category_list.forEach(function(category, index, array){
      category_dict[category] = wmap_column_convert(index + 2);
    });
    Logger.log(category_dict);
    
    for(var i=0;i<data_num;i++){
      var month_row = new_data[i][0].getMonth() + 3;
      var set_pos = category_dict[new_data[i][3]] + String(month_row);
      sheets[ruikei_shishutsu].getRange(set_pos).setValue(sheets[ruikei_shishutsu].getRange(set_pos).getValue() + new_data[i][2]);
    }
    sheets[shishutsu].getRange(last_position).setValue(last_row);
  }   
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();           
  var menu = ui.createMenu('追加したメニュー');  
  menu.addItem('支出計算', 'spending_management');
  menu.addSeparator();                       
  menu.addItem('収入計算', 'income_management');
  menu.addToUi();                         
}

 

簡単に作れて、カスタマイズもかなり自由なので、おすすめです!

しかも、スプレッドシートなので、日付をバラバラに入力をしても簡単に日付順に並び替えたり、グラフ表示ができます!

自己管理に最適です!

以上、「GAS初心者がスプレッドシートで家計簿を作成してみた!」でした!!

是非、活用してみてください!

 

リクエストやコメントなどをいただけると嬉しいです!

Twitterから更新報告をしております!

いいね・フォローしていただけると泣いて喜びます。(´;ω;`)

オススメのプログラミングスクールをご紹介

タイピングもままならない完全にプログラミング初心者から

アホいぶきんぐ
アホいぶきんぐ
プログラミングってどこの国の言語なの~?

たった二ヶ月で

いぶきんぐ
いぶきんぐ
え!?人工知能めっちゃ簡単にできるじゃん!

応用も簡単にできる…!!

という状態になるまで、一気に成長させてくれたオススメのプログラミングスクールをご紹介します!

テックアカデミーのPython+AIコースを受講した僕が本音のレビュー・割引あり! というプログラミング完全初心者だった僕が Tech Academy(テックアカデミー)のPython×AIコース を二ヶ月間...

COMMENT

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です