カテゴリー: 未分類

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;
}