【GAS】Google Apps Script 活用事例　月次シートから年間集計シートの最終行にコピーする。

作業漏れを防ぐ、決められた事を淡々と。

実務でサービスに関して寄せられたクレームを集計しています。流れとしては、SQLで抽出→CSVに変換→月次クレーム集計表を作成→クレームデータベースに情報を追加という事をしています。

いわゆるコピペなので、手動でも出来るのですが、2つのシートを行き来する作業が何回もあると作業漏れなどが発生する可能性が高くなります。そこでGASの出番です。（本音では、基幹システム内で分析が出来るようにして欲しい.....。でもね予算というか懐事情が......。）

元データには全国の支店のクレーム内容があり、主に関東圏のみで集計しており、関係ない支店を省いた上で、通算集計に転記したいというのが今回、GASで実現したい事です。完成形のソースコードはこちら。

完成形のソースコード

2021/02/06　当初書いたソースコードをリライトしました。不要な変数や間違っていないけれども冗長な記述等を削除しました。

//月次クレーム内容を、年間集計表に貼り付ける
function copyToAnnualSheet() {
 const spreadsheet = SpreadsheetApp.openById('************');
 const annualSheet = spreadsheet.getSheetByName('コピー先');

 //年間集計のA列　最終行を取得
 const targetRow = annualSheet.getRange('A:A').getValues().filter(String).length;
 const emptyRow  = targetRow + 1;

 console.log('貼り付け先のシート名：',spreadsheet.getName());
 console.log('最終行直下の空白行：', emptyRow); 

 //月次クレーム内容
 const newValues   = getOriginalValues_();
 const targetRange = annualSheet.getRange(emptyRow, 1, newValues.length, newValues[0].length);

 console.log('貼り付け予定の範囲：　', targetRange.getA1Notation());

 //貼り付け
 targetRange.setValues(results);

 //コピー後の最終行を取得
 const allDataRange = annualSheet.getRange('A2' + ':J' + targetRow);
 console.log('月次データ貼り付け後の範囲：　', sortRange.getA1Notation());

 allDataRange.sort([
   {column: 2, ascending: false},
   {column: 4, ascending: false}
 ]);
}




//月次のシートの値を精査して、集計対象外の支店を除外する。
function getOriginalValues_() {
 const originalSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('元シート');
 
 //月次のコピー内容
 const lastRow = originalSheet.getRange('A:A').getValues().filter(String).length;
 const range   = originalSheet.getRange('A2' + ':J' + lastRow);
 const values  = range.getValues();

 console.log('取得範囲：　',range.getA1Notation());
 console.log('取得内容：　', values);


 //除外対象のユニット
 const units = ['大阪', '兵庫', '名古屋', '神戸'];
 let results = []; //コピー対象の要素を入れる配列

 const stringDate  = (date, number, format) => {
  date.setDate(date.getDate() + number);
  return Utilities.formatDate(date, 'JST', format);
 }

 for (let i = 0; i < values.length; i++) {

   //クレーム発生日と、クレームがカスタマーサポートに寄せられた日付
   const date  = stringDate(values[i][3], 0, 'yyyy/MM/dd');
   const date2 = stringDate(values[i][4], 0, 'yyyy/MM/dd');

   //変数の初期化のため、for文内で宣言
   let count = 0;
   
   //除外するユニットと一致するかチェックする
   for (let j = 0; j < units.length; j++) {
     console.log('values[i][1]: %s', values[i][1]);
     console.log('units: %s', units[j]);
       
     count = count + values[i].indexOf(units[j]); //indexOf()の結果を合計する
     console.log('%s: %s: %s', count, values[i][1], units[j]);
     
     if (count === -1 * units.length) { //合計値が-4（全部不一致）だったらresult[]にpush()
       results.push(values[i]);
     }//if
   }//for_j
 }//for_i

 console.log(results);
 return results
}//end

紐付いていない別シートを取得する場合は、openById('************') で取得します。​

range.getA1Notation());　は覚えておくと確認する際に便利です。console.logした時に範囲をA2:J50みたいな感じで表記してくれます。

indexOfで文字列を検索。

indexOfは配列の先頭から検索して、一致したら配列番号を返します。不一致の場合は、-1を返します。indexOfは一次元配列でしか使えないから、var jを使って、処理をしているという事も最近になって分かりました。分かってきました。下記のブログがすごく分かりやすいです。

【GAS】スプレッドシートのセル範囲（二次元配列）を検索する｜もりさんのプログラミング手帳

var units = ['大阪', '兵庫', '名古屋', '神戸']; //除外するユニット
var results = []; //コピー対象の要素を入れる配列

今回のスクリプトでは、units　に集計対象外の支店名が入っています。

集計対象外の支店名に、全て不一致の時に空の配列　resultsに追加していきます。つまり、resuletsには東京支店、神奈川支店、新宿支店のように集計したい支店だけが入った配列となっています。その配列を年間集計のシートに貼り付けます。

values[i][1]がユニット、支店情報で、units[j]、jには除外するユニットが入っている.....units[0]だったら、大阪.......。これ書いている今も、このスクリプトが、ちょっと難しいなぁと感じています。備忘録的に残しておかないと絶対忘れるヤツ。

配列を貼り付ける時のアレが、ようやく理解できた瞬間

sheetTo.getRange(targetRow, 1,results.length,results[0].length).setValues(results); //最終行のA列:J列に項目をコピーする。
 

targetRowは、.filter(String).length　で最終行の数値が入っています。
GASを覚えたての頃分からなかったのが、results.lengthとresults[0].lengthの違い。

results[0].lengthはイメージで覚えたら、ピンと来るようになりました。配列番号の0番目の長さ　＝　つまり、列数の事なのです。
results.length　は、配列そのものの長さ　＝つまり、行数と覚えておけば配列の貼り付けも出来るようになります。

allDataRange.sort([
   {column: 2, ascending: false},
   {column: 4, ascending: false}
 ]);

コピー先の範囲を全て並び替えます。日付が最新のが先頭で、支店別に並び替えます。sortメソッドは配列を破壊してしまうため、並びを元に戻せません。スクリプト実行前に変更履歴を更新しておいた方がいいかもしれません。

盲点だった、タイムゾーン設定の罠

余談、スクリプトは完璧なはずなのに、コピーした日付が1日ズレるという現象に直面しました。これが結構厄介で、11/30のような月末だと、12/1、翌月になってしまうために、アレ、数が合わない....という事になりました。結果はスプレッドシートのタイムゾーンが東京になっていないためでした。ノンプロ研のメンバーに助けてもらいました。

values[i][3] = Utilities.formatDate(new Date(values[i][3]),'Asia/Tokyo','yyyy/MM/dd');
values[i][4] = Utilities.formatDate(new Date(values[i][4]),'Asia/Tokyo','yyyy/MM/dd');

タイムゾーンに問題があるのかなと思って足した記述。なくても動きます。この記事がとても参考になりました。スプレッドシート本体のタイムゾーンとプロジェクトのタイムゾーン？？？統一してくれ！！ややこしすぎる

GAS スプレッドシートで意図した日時にならない?!そんな時はタイムゾーンの設定が必要です！ - Qiita