【Google Apps Script】その5 該当する日付の行が存在したら上書き、存在しなかったら挿入する

この記事はGoogle Apps Scriptを実例交えて基礎からざっくり学ぶ Advent Calendar 2017　5日目の記事です。

本アドベントカレンダーは@rt_pの個人プロジェクトですが、筆者はAteam Brides Inc. Advent Calendar 2017にも参加しています。そちらでも出張版記事を書いているので、覗いていただけると嬉しいです。

はじめに

スプレッドシートでデイリーのデータを蓄積していこうとした際、その日の集計データがあったら上書き、無ければ新たに行を追加したいことってないですか？

DBを使ったアプリケーションではよくある、pkeyのレコードがあればUPDATE、無ければINSERTするような処理ですね。

1日に1回処理が走るプログラムだったら問答無用で挿入すればいいのですが、1時間ごとに集計が走ったりするとその辺の調整面倒ですよね…

今回はそれをGASで実現します。
APIを使って外部からデータを投げ込む処理は15日目あたりの記事でやる予定です。

1．スプレッドシートを用意

下記テーブルをコピーして、スプレッドシートにペーストしてください。

日付	ページ1 UU	ページ2 UU	ページ3 UU	ページ4 UU
2017/12/01	10	15	11	3
2017/12/02	15	12	9	3
2017/12/03	13	10	20	7

こんな感じになるはずです。

スクリプトエディタを開き、以下コードに置き換えて実行します。
スクリプトエディタの開き方や承認が必要ですメッセージが出た際の対処法が分からない場合は
アドベントカレンダー1日目のHello, world!記事をご参照ください。

insert_or_update.gs

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var newData = ['2017/12/04', 150, 160, 160, 100];
  insertOrUpdate(sheet, newData);
}

// 行の存在に応じて追加もしくは更新を行う
function insertOrUpdate(sheet, data) {
  var row = findRow(sheet, data[0]);
  if (row) { // 行が見つかったら更新
    sheet.getRange(row, 1, 1, data.length).setValues([data]);
  } else { // 行が見つからなかったら新しくデータを挿入
    sheet.appendRow(data);
  }
}

// 行番号を返す
function findRow(sheet, date) {
  var searchDate = Utilities.formatDate(new Date(date), 'Asia/Tokyo','yyyy/MM/dd'); // タイムゾーン違いが面倒なので、GASでの日付比較はgetTimeよりこっちの方が楽
  var values = sheet.getDataRange().getValues();

  for (var i = values.length - 1; i > 0; i--) {
    var dataDate = Utilities.formatDate(new Date(values[i][0]), 'Asia/Tokyo','yyyy/MM/dd');
    if (dataDate == searchDate) {
      return i + 1; // 配列のキーは0から始まり、行数は1から始まるのでズレを直す
    }
  }
  return false;
}

実行すると、2017/12/04という新しい行の追加が確認できるはずです。

既に行が存在する時は追加ではなく更新されることを確認する為、ソースコードのnewDataを少し調整して再実行します。

insert_or_update.gs

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var newData = ['2017/12/04', 100, 100, 100, 100]; // 更新が分かりやすいように、全て100にして再実行し確認する
  insertOrUpdate(sheet, newData);
}
.
.
.

新しい行の追加ではなく、行の更新が行われたことが確認できます。

今回のサンプルコードでは、4日目の記事で触れたUtilities.formatDate()を使っての日付比較を行っています。
getTime()ではなくformatDate()で日付比較をしている理由は上記記事参照。

for (var i = values.length - 1; i > 0; i--) {
配列のキーを0から昇順に調べるのではなく、降順に調べている点に注目です。
日付の新しい行がスプレッドシートの下に順次追加されていく形になるので、降順に調べた方がループ回数が少なくマッチする為です。

一致する日付の行があった際は該当する行番号を返し、setValues()を実行して値の更新を行います。
setValuesは一次元配列ではなく二次元配列を渡す必要があるので、dataという配列を[]で囲って二次元配列にしている点に注目です。

検索の結果一致する行が無かった場合はfalseを返すので、sheet.appendRow(data)が実行されます。

appendRow(rowContents)

引数として配列を渡すことで、新しい行にA列から値をセットします。
これを使えば、わざわざシートの最終行数を調べてgetRange()とsetValues()を実行して…という手間を踏まなくて済みます。

本日は以上です。

おわりに

実際はGASの中に追加したい値をべた書きすることはほとんど無いと思います。
最初にお伝えしたように、APIを使って外部からデータを投げ込む処理は15日目あたりの記事でやる予定です。
その際にこれがかなり活きてくるはずです。

本アドベントカレンダーは、1日目～9日目あたりは基礎編でGASに関する基本的な操作を紹介していきます。
10日目以降は応用編ということで、実際に作ったアプリケーションとそれに関する技術の紹介になります。お楽しみに！

明日

【Google Apps Script】その6　スプレッドシートを誰がいつ開いたか記録する
となります。
今回の知識を応用し、スプレッドシートを開いたユーザーと日時を自動記録する仕組みを作ります。

前の記事
【Google Apps Script】その4　日付を綺麗に整形する
次の記事
【Google Apps Script】その6　スプレッドシートを誰がいつ開いたか記録する