2020年4月16日 / 最終更新日時 : 2024年6月20日 officeの杜 Google

Googleスプレッドシート連携タイムラインを作る【GAS】

Google Visualization APIには、Timelineと呼ばれるチャートの機能がありますが、酷く低機能で正直な所使い易いものとは言えません。そこで外部ライブラリを利用して、スプレッドシート連携のタイムライン実現できるライブラリを探してみた所、2種類ほど見つかり、これをもって、実現してみようと考えました。デフォルトでGoogle Spreadsheetへの連携に対応しているJavaScriptライブラリとしてTimelineというものがありました。もう一つは、格好良いのですが、いまいち使い勝手が良くなかったので今回は次点にしたものとして、Timeline.jsがあります。

また、独自のカスタマイズとしてjQueryのカレンダーとUI CSSの採用、およびシートの切り替え対応を実装（Visualization APIへのリクエリと再描画）、項目をクリックしjQueryダイアログを表示し、取得データを表示するなど、setQueryにて現在時刻で期限到来している項目を非表示等を追加しています。

なお、今回使用しているスプレッドシートですが、非常に単純で各シートに入ってるのは「開始日」・「終了日」・「タイトル」・「カテゴリ名」といった具合です。タイトル列のセルはHTMLも使用する事が出来ます。

※現在このライブラリ後継のVis.jsがリリースされています。

Google Apps ScriptとVis.jsでタイムラインを作る【GAS】

1 使用する材料
2 ソースコード
- 2.1 GAS側コード
- 2.2 HTML側コード
3 実行と結果
4 追加実装してる機能
5 関連リンク

使用する材料

ソースコード

以前は、Google Sites用のXMLガジェットとして作っていたものを、Google Apps Scriptでリファインしたものです。

GAS側コード

//起動時に表示するメニュー
function onOpen(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('タイムライン')
      .addItem('セットアップ', 'setup')
      .addToUi();
}

//外部貼り付け用として表示
function doGet(){
  //スクリプトレットを使えるようにしておく
  var url = 'https://officeforest.org/wp/library/mashroom.png';
  var html = HtmlService.createTemplateFromFile("index")
    .evaluate()
    .setTitle('タイムライン')
    .setFaviconUrl(url);
  return html;  
}

//セットアップ
function setup(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetId = sheet.getId();
  var Prop = PropertiesService.getScriptProperties();  
  Prop.setProperty("sheetid",sheetId);
  
  SpreadsheetApp.getUi().alert("セットアップ完了");
}

//スプレッドシートのデータを取得して返す
function requery(sheet){
  //シートIDを取得する
  var Prop = PropertiesService.getScriptProperties();  
  var ssid = Prop.getProperty("sheetid");
  
  //シートデータを取得する
  var ss = SpreadsheetApp.openById(ssid).getSheetByName(sheet).getRange("A2:D").getValues();

  //データを返す
  return JSON.stringify(ss);
}

//起動時に表示するメニュー

function onOpen(e) {

var ui = SpreadsheetApp.getUi();

ui.createMenu('タイムライン')

.addItem('セットアップ', 'setup')

.addToUi();

}

//外部貼り付け用として表示

function doGet(){

//スクリプトレットを使えるようにしておく

var url = 'https://officeforest.org/wp/library/mashroom.png';

var html = HtmlService.createTemplateFromFile("index")

.evaluate()

.setTitle('タイムライン')

.setFaviconUrl(url);

return html;

}

//セットアップ

function setup(){

var sheet = SpreadsheetApp.getActiveSpreadsheet();

var sheetId = sheet.getId();

var Prop = PropertiesService.getScriptProperties();

Prop.setProperty("sheetid",sheetId);

SpreadsheetApp.getUi().alert("セットアップ完了");

}

//スプレッドシートのデータを取得して返す

function requery(sheet){

//シートIDを取得する

var Prop = PropertiesService.getScriptProperties();

var ssid = Prop.getProperty("sheetid");

//シートデータを取得する

var ss = SpreadsheetApp.openById(ssid).getSheetByName(sheet).getRange("A2:D").getValues();

//データを返す

return JSON.stringify(ss);

}

使用する前にメニューからタイムライン⇒セットアップを実行してこのスプレッドシートのシートIDをスクリプトプロパティに格納しておきます。
単純にシート名をHTML側から取得して、そのシートの全データを返すrequery関数がGAS側の中心です。

HTML側コード

<head>
  <!-- google addon css -->
  <link rel="stylesheet" href="https://officeforest.org/wp/library/timeline/lib/addons.css">
  
  <!-- jQueryとTimeLine関係 -->
  <link rel="stylesheet" href="https://officeforest.org/wp/library/timeline/lib/jqueryuik.css">
  <link rel="stylesheet" type="text/css" href="https://officeforest.org/wp/library/timeline/timeline.css">
  <script type="text/javascript" src="https://www.google.com/jsapi"></script>
  <script type="text/javascript" src="https://officeforest.org/wp/library/timeline/timeline.js"></script>
  <script type="text/javascript" src="https://officeforest.org/wp/library/timeline/timeline-locales.js"></script> 
  <script src="//code.jquery.com/jquery-1.10.2.js"></script>
  <script src="//code.jquery.com/ui/1.11.2/jquery-ui.js"></script>
  <script src="//ajax.googleapis.com/ajax/libs/jqueryui/1.10.4/i18n/jquery.ui.datepicker-ja.js"></script>
  
  <!-- Visualization API -->
  <script src="https://www.gstatic.com/charts/loader.js"></script>

  <style type="text/css">
    body {font: 10pt arial;}
    
    .ui-state-default, .ui-widget-content .ui-state-default, .ui-widget-header .ui-state-default {
        border: 1px solid #ffffff;
        /* background: #1484e6 url(images/ui-bg_diagonals-thick_22_1484e6_40x40.png) 50% 50% repeat; */
        font-weight: bold;
        color: #dd4b39;
    }
    
    div.timeline-frame {
      border-color: #103E9C;
    }
    
    div.timeline-axis {
      border-color: #103E9C;
      background-color: #EEEFF1;
      filter: progid:DXImageTransform.Microsoft.gradient(startColorstr='#F9F9F9', endColorstr='#EEEFF1'); /* for IE */
      background: -webkit-gradient(linear, left top, left bottom, from(#F9F9F9), to(#EEEFF1)); /* for webkit browsers */
      background: -moz-linear-gradient(top,  #F9F9F9,  #EEEFF1); /* for firefox 3.6+ */
    }
    
    div.timeline-axis-text {
      font: bold 12px arial ;
      color: #103E9C;
    }
    
    div.timeline-event {
      border: none;
      background-color: blue;
    }
    div.timeline-event-selected {
      background-color: #C0D8E1;
    }
    div.timeline-event-content {
      margin: 0;
      color:white;
    }
    
    div.timeline-groups-axis {
      border-color: #103E9C;
    }
    div.timeline-groups-text {
      font: bold 12px arial ;
      color: #103E9C;
    }
    
  </style>

  <script type="text/javascript">
    var timeline;
    var nowone = getNowDate();
    var sheet = "イントラ制作";
    
    //Visualization APIの呼び出し
    google.charts.load('current', {'packages':['corechart'], 'language': 'ja'});
    
    //初期化実行
    initialize();

    //整形した現在の日付を取得する関数
    function getNowDate(){
      var date = new Date();
      var year = date.getFullYear();
      var month = date.getMonth() + 1;
      var date = date.getDate();
      if (month < 10) {
        month = "0" + month;
      }
      if (date < 10) {
        date = "0" + date;
      }
      var strDate = String(year + "-" + month + "-" + date + " 00:00:00");
      return strDate;
    }

    //GAS側から対象シートのデータを取得する
    function initialize() {
      google.script.run.withSuccessHandler(onSuccess).requery(sheet);
    }

    //シートデータを反映する
    function onSuccess(data){
      //データを取得する
      var json = JSON.parse(data);
      
      //データテーブルを構築する
      var datatable = new google.visualization.DataTable();
      datatable.addColumn('date', '開始日');
      datatable.addColumn('date', '終了日');
      datatable.addColumn('string', 'タイトル');
      datatable.addColumn('string', '担当者');
      
      //データテーブルに取得データを追加する
      var array = [];
      
      for(var i = 0;i<json.length;i++){
        //一時配列を用意する
        let temparray = [];
        
        //一時配列にpushする
        temparray.push(new Date(json[i][0]));
        temparray.push(new Date(json[i][1]));
        temparray.push(json[i][2]);
        temparray.push(json[i][2]);
        
        //arrayに追加する
        array.push(temparray);
      }
      
      //datatableにarrayを追加する
      datatable.addRows(array);
 
      // オプションの指定
      var options = {
        width:  "95%",
        height: "auto",
        minHeight: 400,
        layout: "box",
        groupsOnRight: false,
        groupsChangeable : false,
        eventMargin: 10,
        eventMarginAxis: 0,
        showNavigation: true,
        axisOnTop: true,
        locale:"ja",
      };
      
      //timelineを表示する
      timeline = new links.Timeline(document.getElementById('mytimeline'), options);
      
      //イベントリスナーを追加する
      google.visualization.events.addListener(timeline, 'select', onselect);
      timeline.draw(datatable);
      timeline.redraw();	
    }

    //タイムライン選択時のイベント
    var onselect = function (event) {
      var row = getSelectedRow();
      
      if (row != undefined) {
        alert(row);
        timeline.unselectItem(row);
      }else{
      }
    };

    //現在地へジャンプする関数
    function moveToCurrentTime() {
      timeline.setVisibleChartRangeNow();
    }

    //指定した期間にスケール表示する為のルーチン
    function setTime() {
      //エラートラップ処理
      if (!timeline){
        return;
      }
      
      var val1 = document.getElementById("startDate").value;
      var val2 = document.getElementById("endDate").value;
	
      //変数に取得した値を格納
      var newStartDate = new Date(val1);
      var newEndDate   = new Date(val2);
      
      //終了日に１日加算する
      var extEndDate = computeDate(val2);
      
      //タイムライン表示をスケール
      timeline.setVisibleChartRange(newStartDate, extEndDate);
      timeline.redraw();
    }

    //項目をクリックした時のrow番号を取得して返す関数
    function getSelectedRow() {
      var row = undefined;
      var sel = timeline.getSelection();
      if (sel.length) {
        if (sel[0].row != undefined) {
          row = sel[0].row;
        }
      }
      return row;
    }

    //アクセスコントロール
    function a_ctrl(){
      //選択名をシート名に代入する
      sheet = document.getElementById("wasabi4").value;

      //データテーブルを初期化する
      google.script.run.withSuccessHandler(onSuccess).requery(sheet);					
    }

  </script>
</head>
	<body style="font-family: Arial;border: 0 none;">
	<p>
		<b>開始日:</b> <input type="text" id="startDate" class="type1" value="" style="width: 100px;">
		&nbsp;<b>終了日:</b> <input type="text" id="endDate" class="type1" value="" style="width: 100px;">
		<input type="button" class="create" id="setStartDate" value="指定期間を表示" onclick="setTime();">
		&nbsp;&nbsp;&nbsp;<b>プロジェクト：</b>
		<select name='wasabi4' id='wasabi4' onChange='a_ctrl()' title='プロジェクトを選択'>
          <option selected>イントラ制作</option>
          <option>基幹業務システム</option>
          <option>受発注システム</option>
		</select>
	</p>
	<script>
		//日付ピッカーの表示用スクリプト
		$(".type1").datepicker({
		  showWeek: true,
		  firstDay: 1,
		 });
	</script>
	<div id="mytimeline"></div>
</body>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

<head>

body {font: 10pt arial;}

.ui-state-default, .ui-widget-content .ui-state-default, .ui-widget-header .ui-state-default {

border: 1px solid #ffffff;

/* background: #1484e6 url(images/ui-bg_diagonals-thick_22_1484e6_40x40.png) 50% 50% repeat; */

font-weight: bold;

color: #dd4b39;

}

div.timeline-frame {

border-color: #103E9C;

}

div.timeline-axis {

border-color: #103E9C;

background-color: #EEEFF1;

filter: progid:DXImageTransform.Microsoft.gradient(startColorstr='#F9F9F9', endColorstr='#EEEFF1'); /* for IE */

background: -webkit-gradient(linear, left top, left bottom, from(#F9F9F9), to(#EEEFF1)); /* for webkit browsers */

background: -moz-linear-gradient(top, #F9F9F9, #EEEFF1); /* for firefox 3.6+ */

}

div.timeline-axis-text {

font: bold 12px arial ;

color: #103E9C;

}

div.timeline-event {

border: none;

background-color: blue;

}

div.timeline-event-selected {

background-color: #C0D8E1;

}

div.timeline-event-content {

margin: 0;

color:white;

}

div.timeline-groups-axis {

border-color: #103E9C;

}

div.timeline-groups-text {

font: bold 12px arial ;

color: #103E9C;

}

</style>

var timeline;

var nowone = getNowDate();

var sheet = "イントラ制作";

//Visualization APIの呼び出し

google.charts.load('current', {'packages':['corechart'], 'language': 'ja'});

//初期化実行

initialize();

//整形した現在の日付を取得する関数

function getNowDate(){

var date = new Date();

var year = date.getFullYear();

var month = date.getMonth() + 1;

var date = date.getDate();

if (month < 10) {

month = "0" + month;

}

if (date < 10) {

date = "0" + date;

}

var strDate = String(year + "-" + month + "-" + date + " 00:00:00");

return strDate;

}

//GAS側から対象シートのデータを取得する

function initialize() {

google.script.run.withSuccessHandler(onSuccess).requery(sheet);

}

//シートデータを反映する

function onSuccess(data){

//データを取得する

var json = JSON.parse(data);

//データテーブルを構築する

var datatable = new google.visualization.DataTable();

datatable.addColumn('date', '開始日');

datatable.addColumn('date', '終了日');

datatable.addColumn('string', 'タイトル');

datatable.addColumn('string', '担当者');

//データテーブルに取得データを追加する

var array = [];

for(var i = 0;i<json.length;i++){

//一時配列を用意する

let temparray = [];

//一時配列にpushする

temparray.push(new Date(json[i][0]));

temparray.push(new Date(json[i][1]));

temparray.push(json[i][2]);

//arrayに追加する

array.push(temparray);

}

//datatableにarrayを追加する

datatable.addRows(array);

// オプションの指定

var options = {

width: "95%",

height: "auto",

minHeight: 400,

layout: "box",

groupsOnRight: false,

groupsChangeable : false,

eventMargin: 10,

eventMarginAxis: 0,

showNavigation: true,

axisOnTop: true,

locale:"ja",

};

//timelineを表示する

timeline = new links.Timeline(document.getElementById('mytimeline'), options);

//イベントリスナーを追加する

google.visualization.events.addListener(timeline, 'select', onselect);

timeline.draw(datatable);

timeline.redraw();

}

//タイムライン選択時のイベント

var onselect = function (event) {

var row = getSelectedRow();

if (row != undefined) {

alert(row);

timeline.unselectItem(row);

}else{

}

};

//現在地へジャンプする関数

function moveToCurrentTime() {

timeline.setVisibleChartRangeNow();

}

//指定した期間にスケール表示する為のルーチン

function setTime() {

//エラートラップ処理

if (!timeline){

return;

}

var val1 = document.getElementById("startDate").value;

var val2 = document.getElementById("endDate").value;

//変数に取得した値を格納

var newStartDate = new Date(val1);

var newEndDate = new Date(val2);

//終了日に１日加算する

var extEndDate = computeDate(val2);

//タイムライン表示をスケール

timeline.setVisibleChartRange(newStartDate, extEndDate);

timeline.redraw();

}

//項目をクリックした時のrow番号を取得して返す関数

function getSelectedRow() {

var row = undefined;

var sel = timeline.getSelection();

if (sel.length) {

if (sel[0].row != undefined) {

row = sel[0].row;

}

return row;

}

//アクセスコントロール

function a_ctrl(){

//選択名をシート名に代入する

sheet = document.getElementById("wasabi4").value;

//データテーブルを初期化する

google.script.run.withSuccessHandler(onSuccess).requery(sheet);

}

</script>

</head>

<p>

   <b>プロジェクト：</b>

<option selected>イントラ制作</option>

<option>基幹業務システム</option>

<option>受発注システム</option>

</select>

</p>

//日付ピッカーの表示用スクリプト

$(".type1").datepicker({

showWeek: true,

firstDay: 1,

});

</script>

</body>

いくつか、jQuery UI CSSに加工をしたものを使っています。また別途個別にStylesheetを追加しています。
今回Timelineでは日付のデータは0:00:00といった日付のデータが含まれていないので、2020/4/15 0:00:00といった形にするためにnew Date()で加工を要しています。
セレクトボタンを選ぶと、シートを切り替えてタイムラインを再描画します（a_ctrl関数）。
このライブラリはGoogle Visualization APIのDataTable機能を利用してるものなので、Visualization APIを利用しています。
データをそのままDataTableに入れられないので、再加工して配列をaddRowsで加えています。
デフォルトでは英語表記のタイムラインですが、日本語化するモジュールをロードさせているだけでは日本語化しません。optionsにてLocaleをjaにしておくことが肝要です。
このライブラリのタイムラインは何種類ものタイムラインを作れる為、様々なサンプルを参考にしてみてください。
onselect関数はVisualization APIにてaddlistenerにてクリック時イベントで呼び出されます。
getSelectedRow関数を用意し、クリック時に対象のタイムラインのrow番号をalert表示します。
optionsにてheightがautoの時だけ、minHeightを指定できます。指定がない場合にはタイムライン表示項目に応じてエリアが縮小拡大します。
今回のパーツを応用してつくったものが、備品貸出管理フォームになります。

実行と結果

ウェブアプリケーションですので、ウェブアプリケーションとして公開をすると利用する事が可能です。もちろん、スプレッドシート上でもDialogとして表示するようにすれば画面上でも利用が可能です。

追加実装してる機能

起動時や再読込時に現在時刻へジャンプさせるようにしています(moveToCurrentTime関数)。
オリジナルのtimeline.jsにはgroupingをした時のタイムラインのポジションに関してバグがあるので、手修正しました。5331行目付近のコードを、Math.max(options.groupMinHeight, group.labelHeight) + options.eventMarginに修正するとズームして、リサイズされても、タイムライン項目の縦ポジションがズレたりしません。
現在時刻時点で終了日を経過してる項目に関してはsetQueryにてフィルタするようにしています。
縮尺設定にてテキストボックスにjQueryカレンダーを設定
項目をクリック時にjQueryダイアログボックスを表示。スプレッドシートの4列目の項目（担当者名）を表示するように設定してる。
プルダウンメニューでシートIDを切り替えて再表示するようにしてあります。
終了日指定にフィルタ時には便宜的に１日を加算する