【GAS】Google Apps Script 活用事例　ステータスが対応済の行を一括で削除、対応履歴に移動するスクリプト

Ctrl + X（切り取り）をして、他のシートに移動させたい

前回の記事で、自動化した書類作成のスクリプトです。処理内容としては、テンプレートを複製し、文字を差し替えた上で指定のGoogle Driveに保存し、そのURLを書き込み、ステータスを作成済に変更してくれます。

sheet.getRange(rangeNumber, 6).clearContent();
sheet.getRange(rangeNumber, 6).setValue('作成済');

今回は、作成済みになっている行を切り取りして、対応履歴に移動させます。キーボードの操作でいうと、Ctrl + X（切り取り）ですね。

1.　シートからステータスが作成済を探して、空の配列に追加していく
2.　作成済の行が入った2次元配列を、対応履歴のシートの最終行に貼り付け
3.　ステータスが作成済となっている行の削除
4.　行には日付が入っているケースが多いので、日付で最新順に並び替え

という処理をしています。

完成形のスクリプト

function responseHistory() {
 var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 var sheet       = spreadsheet.getSheetByName('シート名');
 var values      = sheet.getDataRange().getValues();
 var results     = [];//対応が完了したものを格納する配列
 
 //見出しを省くため
 for( var i = 1; i < values.length; i++ ){
   if( values[i][0] == ''){ continue; }
   
   var immigrationDate  = Utilities.formatDate(new Date(values[i][4]),'Asia/Tokyo','yyyy/MM/dd');//入国日
   var birthDate        = Utilities.formatDate(new Date(values[i][10]),'Asia/Tokyo','yyyy/MM/dd');//生年月日
   var employmentDate   = Utilities.formatDate(new Date(values[i][11]),'Asia/Tokyo','yyyy/MM/dd');//入社年月日（契約開始日）
   var issuedDate       = Utilities.formatDate(new Date(values[i][12]),'Asia/Tokyo','yyyy/MM/dd');//書類の発行日
   var status           = values[i][5];
   
   /*対応状況が完了になったら、新たな配列に入れる。対応履歴のシートに移動する。*/
   if( status == '作成済'){
     results.push(values[i]);
   }//if
 }//for_i
 Logger.log(results);
 
 /*対応状況が作成済みになったものを入力のシートから消していく ++ではなく、--になっている事に注意が必要*/
 for(var j = values.length-1; j >= 0; j--){
 
   if(values[j][5] == '作成済' && values[j][0] !== '' ){
   
     /*配列はゼロから始まるため*/
     var rowPosition = j +1
     sheet.deleteRows(rowPosition, 1);
     
     Logger.log(j);
   }//if
 }//for_j
 
 
 var sheet2        = spreadsheet.getSheetByName('対応履歴');
 var lastRow       = sheet2.getLastRow()+1;
 var paseteValues  = sheet2.getRange( lastRow, 1, results.length, results[0].length ).setValues(results);
 var sortRange     = sheet2.getRange( 2, 1, sheet2.getLastRow()-1, sheet2.getLastColumn() );
 
 sortRange.setFontColor('#000000');//フォントを黒にする。
 
 //日付が新しい順に並び変える
 //  sortRange.sort([
 //    { column: 1, ascending: false }
 //  ]);
}

空白行の処理

if( values[i][0] == ''){ continue; }

QUERY関数やARRAYFORMULA関数などを使っている場合は、空白行の処理が必ず必要です。[i][0]　は、スタッフIDの列です。スタッフIDが空白だったら処理をスキップ。そんな感じです。

if( status == '作成済'){
     results.push(values[i]);
   }//if
 }//for_i

もし、ステータスが作成済だったら、行まるごと、新しい配列に追加という処理です。最後にこの配列を別シートに貼り付けます。

ハマったポイント：微妙に変わるpushの書き方

array.push([eventStartDay,person,eventTitle,eventStartTime,eventEndTime]);
 

カレンダーの予定をスプレッドシート に書き出すスクリプトのpushの書き方
2次元配列　[[*****],[******],[******]]　みたいな感じ。

values[i][0] → 1次元配列（セル）
values[i]　  → 2次元配列（行全体）

元シートから作成済の行を削除するための記述

for(var j = values.length-1; j >= 0; j--){
 
   if(values[j][5] == '作成済' && values[j][0] !== '' ){
   
     /*配列はゼロから始まるため*/
     var rowPosition = j +1
     sheet.deleteRows(rowPosition, 1);
     
     Logger.log(j);
   }//if
 }//for_j

書いた当初、気づかず無限ループを書いてしまってようで、5分間もの間、セルに値を入力しても削除され続けてしまうスクリプトを書いてしまい、半泣きでスクリプト書いていました。

小さいけれど、超大切なポイント

values.length-1となっています。values.lengthは行数を返してくれます。シートが1000行の場合、1000と返ってきますが、配列はゼロから始まるので、-1してあげなければいけません。

そうしないとundefinedになってしまいます。

if文、条件分岐の書き方色々

if( values[j][5] == '作成済' && values[j][0] !== '' )//ステータスが作成済みかつ、IDが空白以外だったら、
if( values[i][0] == ''){ continue; }　　　　　　　　　　//IDが空白だったら処理をスキップ

スプレッドシートで様々なTo Do管理をしているケースって割と多いので、使ってみてください。今回の場合は特に、999からゼロに向かって、-1ずつ減っていくので、空白行の処理をしないと、処理に時間が掛かりすぎてしまうので、忘れずに実装する事が大切かなと思います。