【GAS】Google Apps Script 活用事例　2つの表が混在するシートから情報を取得し、自動更新をするスクリプト

前回の記事からの続き

前回の使いづらい表から、こういう感じに作成し直しました。1つのシートに1つの表しか使わないという原則を守ったほうが効率的にスプレッドシートを使えるのですが、ここは目を瞑ろうと思います。

やりたい事として説明会の開催日が過ぎたらcloseにして、同日程の候補者の行を塗り潰したり、チェックを入れたりしたい。それを実現する自動化についてご紹介します。

コード実行後

開催終了した説明会、2021/10/13 が含まれており、かつ出席確認にチェックが付いていない箇所だけを灰色で塗り潰しています。フィルターを使って、チェックが入っていない人だけを一括で塗り潰すのであれば、1分も掛からない作業なので、自動化する意味が大きいかと問われると何とも言えないのですが、スクリプトを書いてみました。

if文の中に追加したい処理を書き足すだけで、よりも複雑な事も出来ます。例えば、表の中にメールアドレスがあれば、別日程で次回の説明会にご参加頂きたいと考えております〜的なメールを送信する事も出来ます。

コードはこんな感じ

/**
* 会社説明会管理表の自動化に関するスクリプト
* スタータスが完了になったら、表の部分を灰色に塗り潰す
*/

function refrectStatusCareeFair() {
 const sheet  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート2');

 //1つのシートの左右に表があるタイプ
 //右が集計表　左が空白行ありの名簿
 const values = sheet.getDataRange().getValues();
 const header = values[0];
 
 let column   = {
   name:       header.indexOf('名前'),
   date:       header.indexOf('開催日'),
   isAbsent:   header.indexOf('出欠確認'),
   status:     header.indexOf('ステータス'),
   
 };

 //getRangeで使用するため、連想配列内の全ての要素に対して、1を足す
 modifyObject_(column);

 //1次元配列にcloseがある配列だけを残す
 //['close', '10/14']
 const tableRight = sheet.getRange(2, column.status, sheet.getLastRow(), 2).getDisplayValues();
 
 //generateDateArray_(values, column, keyWords)
 const dateArray  = generateDateArray_(tableRight, 1, 'close');
 
 //灰色が格納された配列を作成する
 const range            = sheet.getRange(1, 1, sheet.getLastRow(), column.isAbsent);
 const headerColorArray = range.getBackgrounds()[0];
 const closedColorArray = headerColorArray.map(value => '#cccccc');

 console.log(`range：　${range.getA1Notation()}`);

 for(const eventDate of dateArray){

   //左の表から開催終了した説明会期日を塗り潰していく
   const finder  = range.createTextFinder(eventDate).useRegularExpression(true);
   const results = finder.findAll();

   console.log(`イベント：　${eventDate}`);
   console.log(`該当箇所：　${results.length}`);

   for(const result of results){
     const row         = result.getRow();
     const targetRange = sheet.getRange(row, 1, 1, column.isAbsent);
     const name        = sheet.getRange(row, column.name).getValue();
     const isCheck     = sheet.getRange(row, column.isAbsent).isChecked();
     
     //出欠確認がfalse チェックされていない場合に処理を実行する
     if(isCheck === false){
       console.log(`名前：${name}, チェックの有無：${isCheck}`);
       console.log(`range：${targetRange.getA1Notation()}`);

       targetRange.setBackgrounds([closedColorArray]);
     }else{
       continue
     }     
   }
 }
}


/**
* 会社説明会管理表からステータスがcloseの日程を探す
* 
*/
function generateDateArray_(values, column, keyWords){

//2次元配列をkeyWordsを含む行のみに絞る事が出来る
 const filteredValues = values.filter(array => {  
   if(array.indexOf(keyWords) !== -1){
     return array;
     }
   }
 );

 const newArray = generateArray_(filteredValues, column);

 console.log(filteredValues);
 console.log(newArray);

 return newArray
}


/**
* 連想配列の全ての要素に1を足す。
* 
* values[0].indexOf('名前');　//expected output: 1
* 上記のように見出し行から列の位置を特定するスクリプト
* getRangeでも使いたい場合に使用
* 
*/

function modifyObject_(original) {

const object = Object.keys(original).reduce(
  (after, key) => ({...after, [key]: original[key] += 1}),{}
);
console.log(object);
return object
}


function generateArray_(values, column){
 return values.map(record => record[column]).filter(value => value);
}