時系列データをGoogle Apps ScriptからSpreadsheetsに良い感じに書き込む

前回省いたGoogle Apps Script(GAS)でAPIなどを使って時系列データを取ってきた際、それをSpreadsheetsに埋め込む方法について。

Google Apps Scriptのプロジェクト
Sheetの取得
Sheetへの入力
日時について
GAS(JavaScript)でのDate
Spreadsheetsでの日時情報

Google Apps Scriptのプロジェクト

自分のプロジェクト - Apps Script

を見ると

こんな感じで作ったGASのプロジェクトを見れますが、青に矢印のものとSpreadsheetsの色をしたものが見えます。

自分のプロジェクトページで新しいプロジェクトを作成すれば青に矢印な通常のプロジェクトになり、このプロジェクトはGoogle Driveのルートディレクトリに保存されます。

緑のSpreadsheetsの様なアイコンのものはSpreadsheetsのメニューの ツールスクリプトエディタから開き作成したプロジェクトになります。この場合はプロジェクトファイルは作成されず、 Spreadsheetsの中で管理されている状態になります。

ただし、上にもあるようにGoogle Apps Scriptのページに行くと見ることが出来て直接開くことも出来ます。

ちょっと注意が必要な点として、 Spreadsheetsなどからスクリプトエディタ で開いた際、 Spreadsheets側のタブを閉じるとスクリプトエディタの方も閉じられてしまいます。編集中に保存してない状態だと内容が失われることもあるので思わず閉じてしまわないように。

Spreadsheetsから作ったプロジェクトでも Google Apps Scriptのページから開いた際にはSpreadsheetsがもともと開かれてないのでそういったことは起こりません。

Spreadsheetsから作る利点としては、 GASからSpreadsheetsにアクセスしようとする際にIDなどを使わずに直接アクセスできる点です。

Spreadsheetsを読んだり入力したりする際には Spreadsheetsの ツールスクリプトエディタからプロジェクトを開いて作った方が良いです。

Sheetの取得

Spreadsheets自体は先に作ってあることが前提です。

Spreadsheet Service Apps Script Google Developers

ここにある関数を使っていきます。

まずはSpreadsheetsの中にあるSheetを取得する関数に関して。もし存在しなければ作成します。

var TIME_ZONE = 'Asia/Tokyo';

function getSheet(sheetName, cols=[], formatA='yyyy/MM/dd HH:mm:ss') {
  var ss = SpreadsheetApp.getActive();
  ss.setSpreadsheetTimeZone(TIME_ZONE);
  var sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    sheet = ss.insertSheet(sheetName);
    // remain 1 additional row, to frozen first row
    // (need additional rows to fix rows)
    sheet.deleteRows(1, sheet.getMaxRows()-2);
    var nCols = cols ? cols.length: 1;
    sheet.deleteColumns(1, sheet.getMaxColumns()-1);
    cols.forEach(function(c, i) {
      sheet.getRange(1, i+1).setValue(c);
    });
    sheet.getRange('A:A').setNumberFormat(formatA);
    sheet.setFrozenRows(1);
  }
  return sheet;
}

こんな感じでできるのがこんな感じのもの。

1行目に項目名が入って、さらに固定行にしてスクロール時にも見える様にします。 Aの列が日時の列。

getSheet関数にはシートの名前、項目名(column名)の配列、日付のフォーマットを渡します。

まず、

var ss = SpreadsheetApp.getActive();

でSpreadsheetsを取ってきていますが、Spreadsheetsと紐付いたGASプロジェクトではこの様に簡単にSpreadsheetsを取得できます。

もし、Spreadsheetsと紐付いてない場合や、他のSpreadsheetsを使いたい場合は、

var ss = SpreadsheetApp.openById('<Id>');

と、SpreadsheetAppのOpenByIdという関数を使います。ここで使うIdはSpreadsheetsのURLの

https://docs.google.com/spreadsheets/d/<Id>/edit#gid=0

の様な感じで<Id>の所にある文字列になります。

取得後、すぐにsetSpreadsheetTimeZoneでSpreadsheetsのタイムゾーンを設定しています。 TIMEZONEという変数をは他でも使う可能性があるので適当な場所で宣言しておきます。

これはなぜか新規に作ったSpreadsheetsのタイムゾーンがParisになっていて、 GASとやり取りしたりSpreadsheetsをほかのサービスから使うときに日時データがおかしくなってしまうため。このデフォルト値を変更する方法がいまだに分からず、作ったSpreadsheetsのタイムゾーンは全て初期値でParisになっています。。。

その後でgetSheetByNameでシート名を指定してシートを取得します。無い場合には作成します。

insertSheetで作成
作成時にCellがある程度確保されてしまうのでdeleteRowsで最小限に行を削除(無理にやらなくても良い場合も多いかもしれない)
- この後で1行目を固定するが、その際に固定行以外に行が無いとエラーが出るので2行残す。1行目からシートの全行-2行削除する、の意味。
同様にdeleteColumnsで一旦1列目以外を全て削除。
setValueで1行目に項目名を記入。
setNumberFormatでA列の日時フォーマットを指定。
setFrozenRowsで1行目を固定。

という感じ。

Sheetへの入力

実際にSheetへ入力する際には以下の様な関数を使います。

function fillValues(sheetName, columns, data, formatA='yyyy/MM/dd HH:mm:ss') {
  var sheet = getSheet(sheetName, columns, formatA);
  var datetimes = sheet.getRange('A:A').getDisplayValues().flat();
  var data_new = [];
  data.forEach(function(d) {
    if (datetimes.includes(d[0])) return;
    data_new.push(d);
  });
  if (data_new.length) {
    sheet.getRange(sheet.getLastRow() + 1, 1,
        data_new.length, columns.length).setValues(data_new);
    sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn()).sort(1);
  }
}

これに対して、

function run() {
  var sheetName = 'MyInfo';
  var columns = ['Datetime', 'Weight (kg)', 'Fat Ratio (%)'];
  var data = [['2020/10/06 07:31:12', '123', '50'],
              ['2020/10/08 08:01:01', '125', '52'],
              ['2020/10/08 07:41:01', '125', '55']];
  fillValues(sheetName, columns, data);
}

の様な感じで使います。

1列目は必ず日時になってる必要があります。 (formatA次第では別のものでも構わないですが。)

まず、getSheetを使ってSheetを取得して、さらにA列にある日時の情報を取ってきます。

このとき、

var datetimes = sheet.getRange('A:A').getDisplayValues().flat();

で取ってきてますが、getDisplayValues()の代わりにgetValues()という関数もあります。

getValues()を使うとSpreadsheets上に保存された情報をもとに、数字や日時の様なオブジェクトとして値を取得します。

一方、getDisplayValues()は表示されているママの文字列。つまり、指定した表示形式に依存します。

そして、dataの中の各項目の1つ目の時刻情報を見て、取ってきた日時の中にないものだけ新しいデータとして入力していきます。ここでは日時を文字列としての比較をしています。

ついでに最後の所で日時でソートしています。

こんな風にすることで、時系列データを取得できるAPIから一定期間のデータを取得して、足りない分だけSpreadsheetsへ入力、ということができるようになります。

日時について

日時情報をとってくる場合、getValues()で取得するとDate型で保持されるので、それと比較しようと思うとDate型になるわけですが、これには標準時間基準で比べることになるため、 SpreadsheetsとApps Scriptの両方のタイムゾーンが合ってないとうまくいかなくなります。

この辺は、先に両方のタイムゾーンをきちんと合わせておく、 SpreadsheetsやGAS自身のタイムゾーンを関数で取得して比較し合うように時差を正す、という方法も考えられますが、結構面倒です。

getScriptTimeZone()

getSpreadsheetTimeZone()

setSpreadsheetTimeZone()

Script自体のタイムゾーンを取ってくることは可能ですが、セットする方法は手動でメニューからやるしかない?

手動で変更する場合、 SpreadsheetsのタイムゾーンはファイルGoogleスプレッドシート設定から確認、変更が出来ます。

GASのタイムゾーンはファイルプロジェクトのプロパティから確認、変更が出来ますが、なぜかこちらは初期状態で東京が選ばれてます。 (Gooleの設定的に日本の設定になってるはずなのでこっちが正しいとは思いますが。)

上のgetSheet()でSpreadsheets側のタイムゾーンは決めていますが、 GAS側は決めてないため、手動でやるのを忘れるとおかしくなってしまいます。

タイムゾーンを知ることはできるので、それ使って補正しても良いですが色々面倒。

そこでgetDisplayValues()という、表示状態そのままで取ってくる方法を使っています。

この際、見た目のママ、なので表示形式が重要になってきます。

Spreadsheetsの日時のデフォルトの表示形式が

yyyy/MM/dd H:mm:ss'

つまり、時間のみ0埋めしないでほかはする、というものになっています(私の環境では)。

これに合わせてGASでも日時を調整すれば良いのですが、見た目的に全部0埋めしてあったほうが見やすいのでSpreadsheetsでもそうして、 GAS側で日時を文字列にする際にも同じ形式にして比較しています。

逆に入力するときは文字列として日時を入力するため、 GASのタイムゾーンは関係なく、 Spreadsheetsに表示される日時はGASで入れる文字列そのものです。

このときに入力する文字列が yyyy/MM/dd HH:mm:ssと時間を0埋めするものであっても、 Spreadsheetsの表示形式が0埋めしないものなら0埋め無しで表示されますし、情報としてもDate型として保存されるので0埋めしてあったかどうかは関係なくなります。

したがって表示形式を指定するのは結構重要な部分になっています。

GAS(JavaScript)でのDate

もともとAPIで取得するような日時情報はUNIX時間で渡されることが多いです。単純な数字で済むし、確実なので。

UNIX時間はUTCでの1970年1月1日(UNIXエポック)からの秒数になります。

一方、GAS、というかJavaScriptのDateオブジェクトは UNIXエポックからのミリ秒を保存していて、 Date.now()で取れる値やコンストラクタに渡す引数としてはミリ秒なUNIX時間にしなくてはいけません。

Date - JavaScript MDN

ちょっとその辺のことに注意して、以下の様な関数を使って取ってきたUNIX時間を文字列に変更してSpreadsheetsに入力しています。

var TIME_ZONE = 'Asia/Tokyo';

function getDate(unixtime=null, timezone=null, format='yyyy/MM/dd HH:mm:ss'){
  if (!timezone) timezone = TIME_ZONE;
  if (!unixtime) {
    return Utilities.formatDate(new Date(), timezone, format);
  }
  return Utilities.formatDate(new Date(unixtime * 1000), timezone, format);
}

Spreadsheetsでの日時情報

Spreadsheetsでは日時の足し引きができたりします。また、表示形式で数字にすることも出来ます。

このとき、どの様な数字になるかというと、 1900年1月1日から何日目か、という数字になります。

2020年10月8日なら44112。時刻が入っている場合はその24時間を1とした場合の割合、つまり12:00なら0.5、15:30とかなら0.65です。

この様に日時は全て数字(シリアル値)で管理されてるので足し引きも簡単に行える、というわけ。

これはもともとMicrosoftのExcelの仕様を引き継いでいるものです。

1900と1904の日付システムの相違点 Microsoft Docs

1900年がうるう年でないという特殊な年でそれを回避するために 1904年からの計算もあるようです。

1900年から、というのはこれを考えてた頃には1900年代分位あれば十分だし、 2000年はまだ先だし、 1900年から二桁だけの年を使うというのは昔のコンピューターの資源上何かと便利だったのでそうしてたんだと思います。

ちなみに、Google Spreadsheetsだと1900年以前の日時もちゃんと認識して、数字に直すとマイナスの値になります。

一方、Excel (バージョン2009)のものは1800/01/01とかを入力すると数字には変換できず、また、マイナスの数字も日付には変換出来ませんでした。 (Spreadsheetsの方がこの辺賢いみたいです。もっと新しいのならもしかしたらできるようになってるかもしれませんが。)

最初、GASから入力する際、UNIX時間のまま入れるのが何かと便利かな、とも思ったのですが、 Spreadsheets側だとその数字は日時には直接変換できず、適当な計算を自分でしてあげなくてはいけないので面倒だな、というのと、単にシートを自分で見た際にわかりやすいほうが良かろう、ということで日時に直した状態で入力するようにしました。