銀行、証券、クレカなど口座が増えすぎて管理不能。
なのでGoogleスプレッドシートとGASで作ったスクリプトなどを保存しておきます。
口座、口座分析シート
口座シート項目
sort1 sort2 sort1_2auto sort3 baseCurrency bankname ymd Total history
GAS
とりあえずザクっと作っただけなので色々と見苦しいコードだと思いますが用は果たすので。
function myFunction() {
var sheet = SpreadsheetApp.
getActiveSpreadsheet().getSheetByName('口座');
lastRow = sheet.getLastRow();
Logger.log(lastRow);
lastCol = sheet.getLastColumn();
Logger.log(lastCol);
for(let i = 0; i <= lastCol; i++) {//列名配列を作っている。
const body = sheet.getRange(1, i+1).getValue();
//Logger.log(body);
if(body==''){
break;
}
//Logger.log(body);
eval([body]+"_Col" + " = " + (i+1));
}
var komoku =['sort1','sort1_2auto','sort3','baseCurrency'];
for(let k = 0; k < komoku.length; k++) {//項目配列を作る
eval( "var "+komoku[k]+"_Array = []");
}
//var sort1_Array = [];
// var sort1_Array = [];//重複削除
var space_Array = [];//重複削除
for(let i = 3; i <= lastRow; i++) {//sort1配列を作る
for(let k = 0; k < komoku.length; k++) {
// eval( "const "+komoku[k]+"_Col_val = 3");
eval( "var "+komoku[k]+"_Col_val = sheet.getRange(i, "+komoku[k]+"_Col).getValue()");
} //const 使えないっぽい。evalには？
// const sort1_Col_val = sheet.getRange(i, sort1_Col).getValue();
for(let k = 0; k < komoku.length; k++) {
if (!(eval(komoku[k]+"_Col_val in "+komoku[k]+"_Array"))) {
eval(komoku[k]+"_Array["+komoku[k]+"_Col_val] = 0;");
// sort1_Array[sort1_Col_val] = 0;
}
}
/*
if (!(sort1_Col_val in sort1_Array)) {
sort1_Array[sort1_Col_val] = 0;
}
*/
let gak = parseInt(sheet.getRange(i,Total_Col).getValue());
if(!isNaN_(gak)){
for(let k = 0; k < komoku.length; k++) {
eval(komoku[k]+"_Array["+komoku[k]+"_Col_val] = "+komoku[k]+"_Array["+komoku[k]+"_Col_val] + gak;");
}
// sort1_Array[sort1_Col_val] = sort1_Array[sort1_Col_val] + gak;
}
}
//ソート
for(let k = 0; k < komoku.length; k++) {
eval(komoku[k]+"_Array = sorting_desc("+komoku[k]+"_Array);");
}
// sort1_Array = sorting_desc(sort1_Array);
/////////////////////特別処理
sort1_2auto_mod_Array = [];
//sort1_2auto_mod_Array.push('strawberry');
for (let basekey in sort1_Array) {
if(basekey==""){
continue;
}
// Logger.log('basekey:' + basekey + ' value:' + sort1_Array[basekey]);
for (let key in sort1_2auto_Array) {
if ( key.match(basekey+",")) {
sort1_2auto_mod_Array[key]=sort1_2auto_Array[key];
}
}
}
//分析シート出力
var analystic_sheet = SpreadsheetApp.
getActiveSpreadsheet().getSheetByName('口座分析');
analystic_sheet.appendRow(['集計開始']);
analystic_sheet.deleteRows(1,analystic_sheet.getMaxRows()-1);
for(let k = 0; k < komoku.length; k++) {
eval("set_data_for_analystic_sheet(analystic_sheet,"+komoku[k]+"_Array,'"+komoku[k]+"');");
}
// set_data_for_analystic_sheet(analystic_sheet,sort1_Array,'sort1');
set_data_for_analystic_sheet(analystic_sheet,sort1_2auto_mod_Array,'sort1_2auto_mod');
//set_data_for_analystic_sheet(analystic_sheet,space_Array,'filler');
///色分けなど
analystic_sheet_lastRow = analystic_sheet.getLastRow();
analystic_sheet_lastCol = analystic_sheet.getLastColumn();
for(let i = 1; i <=analystic_sheet_lastRow; i++) {
const midashi_val = analystic_sheet.getRange(i, 1).getValue();
//Logger.log('midashi_val:' + midashi_val);
var cells = analystic_sheet.getRange(i,1,1,analystic_sheet_lastCol);
if(midashi_val == '合計'){
// analystic_sheet.getRange(i,1,1,2).setNumberFormat('[$¥-411]#,##0');
cells.setNumberFormat('[$¥-411]#,##0');
}
if(midashi_val == '金額'){
cells.setNumberFormat('[$¥-411]#,##0');
}
if(midashi_val == '%'){
cells.setNumberFormat('0.00%');
}
if(midashi_val == 'filler'){
cells.setBackground("#FFFF00");
}else{
cells.setBackground("#FFFFFF");
}
}
}
function set_data_for_analystic_sheet(analystic_sheet,Data_Array,msg){
//Date型でオブジェクト生成(初期値は現在日時)
var date = new Date();
//現在時刻を表示
//Logger.log(Utilities.formatDate( date, 'Asia/Tokyo', 'yyyy/MM/dd hh:mm:ss'));
var ymd = Utilities.formatDate( date, 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss');
analystic_sheet.appendRow(['filler',msg,ymd]);
var total_sum = 0;
for(var key in Data_Array){
total_sum = total_sum + Data_Array[key];
}
analystic_sheet.appendRow(['合計',total_sum]);
var val_Array = ['項目'];
for(var key in Data_Array){
// Logger.log('key:' + key);
//Logger.log('value:' + Data_Array[key]);
val_Array.push(key);
}
analystic_sheet.appendRow(val_Array);
var val_Array = ['金額'];
for(var key in Data_Array){
// Logger.log('key:' + key);
//Logger.log('value:' + Data_Array[key]);
val_Array.push(Data_Array[key]);
}
analystic_sheet.appendRow(val_Array);
var val_Array = ['%'];
for(var key in Data_Array){
// Logger.log('key:' + key);
//Logger.log('value:' + Data_Array[key]);
val_Array.push(Data_Array[key]/total_sum);
}
analystic_sheet.appendRow(val_Array);
}
function isNaN_ (value) {
return typeof value === 'number' && value !== value
}
function sorting_desc(obj){
var pairs = Object.entries(obj);
pairs.sort(function(p1, p2){
var p1Val = p1[1], p2Val = p2[1];
// return p1Val - p2Val;
return p2Val - p1Val;
})
obj = Object.fromEntries(pairs);
return obj;
}
