Googleスプレッドシートはリストや対応表を作ったりとさまざまな局面で利用できます。
また、クラウドなので複数人と同時に編集したりすることができるため、素早く最新情報を共有することができます。
スプレッドシートはもう一つ魅力があります。
それはGoogle Apps Script(GAS)を使うことで、スプレッドシートを拡張できるということです。
エクセルで言うマクロみたいなものですが、JavaScriptライク(というかJavaScriptが使える)なので、フロントエンドエンジニアには敷居が低いのは今の時代に即してます。
さて、ここからが本題。
スプレッドシートとGASをつかってウェブサイト制作を効率化させることができます。
何らかのリストや対応表、簡単な商品情報など地味にコピペが面倒なものなどはGASでファイルを作って、中身をコピペするなりJSONファイルとして制作で使うなりするという方法にすることで効率化を図れます。
事前準備
ファイル置き場用ディレクトリの作成
GASではHTML5のBlobを使ったストリーム上でのファイル生成を行うことができないため、実ファイルを生成する必要があります。
(ファイルをダウンロードする場合)
また複数人で利用する場合には適切な共有設定を行いましょう
作成したディレクトリのIDはスクリプトで必要なので控えておきます。
スプレッドシートでGASを使えるようにする
ツール > スクリプトエディタでGASが使えるようになります。
スプレッドシートに「書き出し」ボタンを作成
/**
* スプレッドシートのメニューを拡張
*/
function onOpen(){
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [
{
name : "JSON出力",
functionName : "dialogEntries"
}
];
spreadsheet.addMenu("書き出し", entries);
}
onOpen関数を使うとスプレッドシートを開いた時に処理を行ってくれます。
上記はスプレッドシートに「書き出し」と言うメニューを追加する構文です。
保存したらスプレッドシートを一旦閉じて再度スプレッドシートを開くとメニューが追加されます。
entries変数内は書き出しボタンを押した時にドロップダウンで表示されるメニュー名になります。
nameが名称、functionNameは関数名。
GASで構文を記述
/**
* ファイルを作る
*/
function dialogEntries(){
var sheet_name = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName(); // アクティブなシート名を取得
var sheet_info = SpreadsheetApp.getActiveSheet(); // シートを取得
var sheet_data = sheet_info.getDataRange().getValues(); // データの存在する領域内を取得
var result = {};
// 取得したセルを何か処理する
for(var i = 0, len = sheet_data.length; i < len; i++){
var d = sheet_data[i]
result[d[0]] = d[1] // A列がkey、B列がvalueの場合
}
body = JSON.stringify(result, null, 2); // bodyはグローバル
var download_dir_id = '0BzmUVJgeTfw5TC1YTE4yN3V0Njg'; // ファイルを置くディレクトリID
var current_date = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyyMMdd_HHmmss'); // 日付の取得
var file_name = sheet_name + '_' + current_date + '.json';
var blob = Utilities.newBlob("", 'application/json', file_name).setDataFromString(body, 'UTF-8');
var fileID = DriveApp.getFolderById(download_dir_id).createFile(blob).getId();
url = "https://drive.google.com/uc?export=download&id=" + fileID; // ダウンロードURL
var output = HtmlService.createTemplateFromFile('download'); // download.html
var html = output.evaluate().setHeight(420).setWidth(600);
var ui = SpreadsheetApp.getUi();
ui.showModalDialog(html, file_name); // file_nameはタイトル
}
上記でA列をキー名、B列をvalueとしたJSONを作成することができます。
スプレッドシートの内容を取得
var sheet_name = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName(); // アクティブなシート名を取得
var sheet_info = SpreadsheetApp.getActiveSheet(); // シートを取得
var sheet_data = sheet_info.getDataRange().getValues(); // データの存在する領域内を取得
sheet_dataには1行ごとに配列として格納されるのでfor文等でループさせる事が可能。
列も同様に配列として格納されている。
内容を整形する
var result = {};
// 取得したセルを何か処理する
for(var i = 0, len = sheet_data.length; i < len; i++){
var d = sheet_data[i]
result[d[0]] = d[1] // A列がkey、B列がvalueの場合
}
上記は例なので複雑な処理を行うことでよりさまざまなことが実現可能。
保存用のJSONファイル名を作成
var current_date = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyyMMdd_HHmmss'); // 日付の取得
var file_name = sheet_name + '_' + current_date + '.json';
シート名.jsonにしてもいいが、そうするとファイル保管場所に同じ名前のファイルがずっと生成されるのであえて日付を後につける
ダイアログでJSONの中身を確認させつつダウンロードさせる
var blob = Utilities.newBlob("", 'application/json', file_name).setDataFromString(body, 'UTF-8');
var fileID = DriveApp.getFolderById(download_dir_id).createFile(blob).getId();
url = "https://drive.google.com/uc?export=download&id=" + fileID; // ダウンロードURL
var output = HtmlService.createTemplateFromFile('download'); // download.htmlを挿入
var html = output.evaluate().setHeight(420).setWidth(600);
var ui = SpreadsheetApp.getUi();
ui.showModalDialog(html, file_name); // file_nameはタイトル
ファイルを生成し、所定のディレクトリにファイルを作成し、htmlをダイアログで挿入という処理を行っている。
<!-- download.html -->
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<title>JSON ダウンロード</title>
</head>
<body>
<style type="text/css">
#json {
height: 320px;
overflow: scroll;
border:1px solid #ddd;
padding:10px;
text-align: left;
}
.button-download {
display: inline-block;
text-decoration: none;
padding: 10px;
width: 120px;
text-align: center;
border-radius: 4px;
border: 1px solid #4191e8;
background-color: #4191e8;
color: #fff;
font-size: 14px;
transition: all 0.3s ease;
}
.button-download:hover {
background-color: #fff;
color: #4191e8;
}
</style>
<pre id="json"><code>
<?
output.append(body);
?>
</code></pre>
<div align="center">
<?
output.append('<a class="button-download" href="' + url + '">ダウンロード</a>');
?>
</div>
</body>
</html>
htmlファイルの追加方法は下記図の通り
スクリプトのbodyとurlの変数はhtml内でも使用するためあえてvarで初期化していません。
これでスプレッドシートからファイルを作成することができます。
例としてJSONファイルにしましたが.txtでの出力などもできます。
構成を変更したなどで、.htaccessを使ってリダイレクトさせたいときなどもスプレッドシートから作成したりできるので使い道は無限です。
是非、効率化するためにGASを使ってみましょう。
※書き出しボタンを押すと一番最初は「承諾が必要」と言われるので承諾をして下さい。