Help us understand the problem. What is going on with this article?

GASで外部APIと連携(3/3)

More than 5 years have passed since last update.

前回からの続きです。
よろしくお願いします!

今回もPivotalTracker(以下PT)からスプレッドシートにガントチャートを作成する(※1)というお話です.
前回はAPI(PT APIv3)からデータを取得してデータベース(ScriptDb)に入れるところまででした.
それを受けまして今回は、日付データとシート上の位置を対応付けるという事をしたいと思います。

前提

  • APIから取得した日付データを参照してスプレッドシートにカレンダー形式で配置したい
  • スプレッドシート上にカレンダーっぽいものを配置してある
  • 取得する日付文字列の形式が予めわかっている
  • 日付の処理にはjavascriptのDateクラスのメソッドも少し使う

流れ

  1. 日付文字列をAPIから取得する
  2. 文字列をパースしてDateオブジェクトに変換する
  3. ヘッダー部分の日付を取得する
  4. ヘッダーの日付を走査してAPIからの日付と比較する
  5. ヒットした日付のある列番号を得る

サンプル

順を追って説明するのがとても面倒になってきたので,とりあえずサンプルを出してしまいます.
ちょっとしたイベントを仮定して,カレンダーっぽいものを作ってみます.

gas_calendar.gs
var oneDay = 1000*60*60*24,         //ミリ秒で1日分
    term   = 28,                    //チャートの期間の日数
    col1st = 3,                     //チャートの開始列
    row1st = 3,                     //チャートの開始行
    events = [
      { start_at: '2012/12/22 10:00:00 UTC', name: '忘年会'       } ,
      { start_at: '2012/12/24 12:00:00 UTC', name: 'Xmasパーティ' } ,
      { start_at: '2012/12/31 10:15:00 UTC', name: '紅白歌合戦'   } ,
      { start_at: '2013/01/01 00:00:00 UTC', name: '初詣'         } ,
      { start_at: '2013/01/10 15:00:00 UTC', name: '誕生日'       } 
];                                                                      //(1)

//Dateオブジェクトから列番号を取得する
function getColumnByDate(theDate) {
  var sheet = SpreadsheetApp.getActiveSheet(),
      chartDates = sheet.getRange(1, col1st, 1, term+1).getValues()[0];

  if( theDate < chartDates[0] )
    return 0;
  for(var i=0; i<term; i++){
    var chartDate = chartDates[i];
    if(judgeDate(chartDate, theDate))                                   //(2)
      break;
  }
  return i + col1st;                                                      //(3)
}

//2つのDateオブジェクトが同じ日だったら,trueを返す
function judgeDate(date1, date2){
  var res = false,
      diff_time = date2 - date1;
  if( (diff_time <= oneDay) && (date1.getDate() == date2.getDate()) )
    res = true;
  return res;
}

//チャートのヘッダーをシートに書き出す
function setHeader(){
  var week   = ['','','','','','','']; 
  var today  = new Date(), 
      today  = new Date(today.getYear(), today.getMonth(), today.getDate()),
      sheet  = SpreadsheetApp.getActiveSheet(),
      endDay = new Date(today.getTime() + oneDay * term);

  sheet.getRange(1, 1).setNumberFormat('yyyy/MM/dd').setValue(today);
  sheet.getRange(1, 2).setNumberFormat(' 〜 yyyy/MM/dd').setValue(endDay);
  sheet.getRange(1, col1st, 1, term+1).setNumberFormat('d');
  sheet.getRange(2, 1, 1, 2).setValues([['イベント名', '開始時刻']]);

  for(var i=0; i<term; i++){
    var thisDate = new Date(today.getTime() + oneDay * i),
        day = week[thisDate.getDay()],
        dateValues = [[thisDate],[day]],
        column = i + col1st,
        range = sheet.getRange(1, column, 2)
          .setValues(dateValues);
    sheet.setColumnWidth(column, 20);
  }
}

//イベントをチャートに挿入
function insertEvents(){
  var sheet  = SpreadsheetApp.getActiveSheet();
  for(var i=0; i<events.length; i++){
    var name  = events[i].name,
        start = new Date(events[i].start_at),                           //(4)
        index = getColumnByDate(start);                                 //(5)

    var cell_name  = sheet.getRange(i+row1st, 1),
        cell_start = sheet.getRange(i+row1st, 2),
        cell_chart = sheet.getRange(i+row1st, index);

    cell_name.setValue(name);
    cell_start
      .setNumberFormat('MM/dd a hh:mm')                                 //(6)
      .setValue(start);
    cell_chart.setValue('');
  }
}

//サンプルを実行
function sample_calendar(){
  SpreadsheetApp.getActiveSheet().clear();
  setHeader();
  insertEvents();
}

(1)こんな感じのデータがAPIから取得できたと仮定してしまいます.(※2)
(2)チャート上の日付の値とAPIから取得した日付とを比較します.
(3)judgeDate()で2つが同じ日だとわかると,列番号を返してくれます.
(4)イベントの開始時刻をDateオブジェクトに変換しています.
(5)そのDateオブジェクトから該当する列番号を取得します.
(6)セルの数値書式を設定しています.

というわけで,sample_calendar()を実行すると,↓こんなかんじになるかと思います.

screenshot\_sample\_calendar

ポイント

  1. 時刻をUTC時刻として扱っている
  2. セルの数値書式をsetNumberFormat()で設定する

1に関しては,自分でとてもハマってしまったところです.

GASの仕様なのか自分の環境の問題なのか,なぜかタイムゾーンの設定がPSTになってしまいます.スクリプトのプロパティやらスプレッドシートの設定やらのタイムゾーンは全て東京(GMT+09:00)にしてあるのですが...
Googleさんのサーバーの場所のタイムゾーンになってしまっている?のでしょうか...
よくわかりません.
PSTになってしまうとわかっていれば,決め打ちでオフセットを掛けても問題はないかもしれませんが,やはりそれではスマートではないので,UTC時刻を使うことにしました.

By default, all dates are returned in the user's timezone, which can be
configured on the Profile page (accessible via the drop-down menu under your
name in the top right corner). If you prefer dates to be returned in UTC, add
the X-Tracker-Use-UTC header.

PTのAPIリファレンスには↑のように書いてあり,ヘッダーに「UTCでちょうだいね」と添えてあげる事で,日付データをUTC時刻で返してくれます.(サンプルコードの日付文字列のような形式になります)

2は日付データのシート上での扱いについてです.

setFormula()を使って,スプレッドシート内の関数である,Text(value,
format) などとしてもOKかとおもいますが,こちらの方がシンプルです.
注意点として,直接スクリプト上で,月や日を文字列として整形してから書きだしてしまいますと,後でその日付データを使おうとした時にまたパースしないといけないので,値は日付データのままで,書式を設定してあげると都合が良いです.
スプレッドシートの日付データを取得すると,そのままDateオブジェクトとして扱ってくれる(?)ようですので.

まとめ

3回目で説明が雑になってしまいました...(その上,長い!)
以上が実際外部APIと連携したスクリプトを書いてみて自分で使ったTipsのまとめです.

一応,外部APIからデータを取得してスプレッドシートに並べるという一連の流れを追えたのではないかと勝手に思っています.

GASは気軽にいろいろ遊べるというところがとてもいいですね!
Javascriptの勉強にもなりました.


※1 実際の作成の経緯についてはこちらのブログを参照してください.
※2 現実のデータとはなんの関係もありません.

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした