【GAS】Google Apps Script 活用事例　ファイル内に、1年分の月次シートを新規作成するスクリプト

超絶めんどくせー事、言い出しやがった.....。

年単位で管理する事を想定していない、月次シートを、一つにまとめなければならなくなった、そういう場面が実務であり、「超絶にめんどくせー.....」と思って、スクリプトで片付けました。その時に使用したスクリプトを公開します。

1つのファイル内に、1年分の月次シートを新規作成するスクリプト

function dateArray() {
const startDate   = new Date('2019/06/01');
const endDate     = new Date('2020/06/01');

console.log(startDate);
console.log(endDate);


let dateArray = [];

for(let d = startDate; d < endDate; d.setMonth(d.getMonth()+1)) {
  console.log('iの内容 %s',d);
  
  dateArray.push(Utilities.formatDate(d, 'Asia/Tokyo', 'yyyy/MM/dd'));
}//for
console.log('配列の内容',dateArray);
return dateArray
}//end


function createNewSheet() {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const monthList   = dateArray();
 
 for(let i = 0; i < monthList.length; i++){
   spreadsheet.insertSheet(1);
   spreadsheet.getActiveSheet().setName(monthList[i]);
 }//for

}//end

for文で、1ヶ月後の日付を生成し、それをシート名として使用して、新しいシートを作り続けるというものです。

それぞれの月次シートの総稼働人数を取得するスクリプト

下記のスクリプトは、1月、2月のように別々に分かれている各ファイル（※コピーして作成している。）のJ列にある稼働人数を全部合計して、集計期間内での総稼働人数を算出する時に使用したものです。

let number = 0;

変数numberに、J列の内容を足し続けて、総稼働人数を算出します。

function　addupFiles() {

 //10月から5ヶ月間が対象
 const urlArray = [
   'https://docs.google.com/spreadsheets/d/********************************************/edit#gid=************',
   'https://docs.google.com/spreadsheets/d/********************************************/edit#gid=************',
   'https://docs.google.com/spreadsheets/d/********************************************/edit#gid=************',
   'https://docs.google.com/spreadsheets/d/********************************************/edit#gid=************',
   'https://docs.google.com/spreadsheets/d/********************************************/edit#gid=************'
 ]//urlArray
 
 let newArray   = [];
 
 //M列が0以上だったら、1人としてカウントし、配列に追加する。
 for(let i = 0; i < urlArray.length; i++){
   const spreadsheet = SpreadsheetApp.openByUrl(urlArray[i]);
   const sheet       = spreadsheet.getSheetByName('RAW');
   const lastRow     = sheet.getRange('A2:A').getValues().filter(String).length;
   const range       = sheet.getRange('J2:J' + lastRow);
   
   console.log(range.getA1Notation());
   
   
   const values = range.getValues();
   let number   = 0;
   
   for(let j = 0; j < values.length; j++){
     if(0 < values[j][0]){
       number += values[j][0];
       
     }//if
   }//for
   newArray.push(number);
 }//for
 
 //5ヶ月間の総稼働人数が把握出来る。
 console.log(newArray);
}//end

小さく試す

//サンプルスクリプト　挙動確認用
function sample() {
 const spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/******************/edit#gid=************');
 const sheet       = spreadsheet.getSheetByName('DB');
 const values      = sheet.getRange('B5:M166').getValues();
 
 //console.log(values);
 
 let newArray = [];
 
 //J列が、0以上、つまり1ヶ月に、1時間でも稼働があった人のみを対象とする。
 for(let i = 0; i < values.length; i++){
   if(0 < values[i][11]){
     newArray.push(values[i][11]);
     
   }//if
 }//for
 console.log(newArray.length);
}//end

sample()で、上手くいくかやってみて、addupFilesで、5つのファイルを連続で取得し、処理を繰り返すという事をやってみました。これで、どれほど時間が浮いた事か。