はじめに
Google SpreadSheet(GSS)便利ですね!すごく好きです。
システム開発系のプロジェクトの開発を通して、テーブル設計やテストデータ、シードデータをスプレッドシートに作成しています。
GSSであれば非エンジニアの人でも見やすいですし、エンジニアでも気軽にデータを確認、変更することができます。
DBが関連するシステムであればテーブル設計やテストデータはとても重要です。
ディレクターさんやテスターさんの非エンジニアの人に「このデータはphpMyAdminから変更しといてください」といってもなかなかハードルが高いんですよね。
ハードルが高いとその重要なデータをなかなかメンテできなくなってしまいますので、少しでも簡単に変更してもらうようにします。
できたもの
- スプレッドシートをURLで共有する
- URLを入力する
- JSONで取得できるURLが取れる
コード
PHPで書きました。
function parseCSV( $url ) {
// @see https://blog.keinos.com/20170523_2295
$file = new NoRewindIterator( new SplFileObject( $url ) );
$file->setFlags( SplFileObject::READ_CSV );
$datas = [];
$header = null;
while ( $row = $file->fgetcsv() ) {
if ( null === $header ) { // 初回の一行目
$header = $row;
continue;
}
if ( count( $row ) === 1 || // 空行だったりした場合
empty( $row[0] ) || // 最初の行はIDで固定だが、そこが省かれている場合には省く
'null' === $row[0] // 最初の列にnullと書いてある行は省く
) {
continue;
}
$data = array_combine( $header, $row );
foreach ( $data as $key => $value ) {
if ( ! is_numeric( $data[ $key ] ) && // emptyが 0 を trueと解釈してしまうため
empty( $data[ $key ] ) ) { // 空行
unset( $data[ $key ] ); // 空行は許されないのでNULLにしておく
}
}
if ( empty( $data['created'] ) ) {
$data['created'] = date( 'Y-m-d H:i:s' );
}
if ( empty( $data['modified'] ) ) {
$data['modified'] = date( 'Y-m-d H:i:s' );
}
$datas[] = $data;
}
return $datas;
}
GSSにはシートをCSVで取得するURLがあるので、そのURLでCSVを取得させています。
行の取得にいくつかルールを入れています。
- 最初の行はカラム名(JSONの列名)の行としています。
- 1列目が空の場合、
null
と書いてある場合、コメント行として取り扱っています。 - 最後にcreatedとmodifiedを自動で付与しています。
このコードを動かすとこんな感じの返答が返ってきます。
$ curl -s 'https://gss-api.now.sh/v1.php?gss_id=1m4BI7R-CcjNREH4DUe1xCM3OIVVSGrGx6-7iUtIvUWE&gid=635058114' | jq .
[
{
"id": "1",
"name": "りんごごりら",
"price": "100",
"created": "2019-03-28 08:38:14",
"modified": "2019-03-28 08:38:14"
},
{
"id": "2",
"name": "ばなな",
"price": "200",
https://gss-api.now.sh/v1.php?gss_id=1m4BI7R-CcjNREH4DUe1xCM3OIVVSGrGx6-7iUtIvUWE&gid=635058114
こういった感じで、スプレッドシートIDとシートIDにあたるgid
を渡しています。
フロント
URLを入力したらcURLのコマンドに変更されるようにフロント系のコードを書いておきます。
'use strict';
import GSSCSVUrl from 'gss-csv-url';
import axiosBase from 'axios';
const gss_csv_url = new GSSCSVUrl();
// @see https://stackoverflow.com/a/6941653/2405335
const base_url = location.protocol + '//' + location.hostname + (location.port ? ':' + location.port : '');
/**
* 実際にエンドポイントへリクエストを投げて結果を表示する。
*/
function request(gss_id, gid) {
// エンドポイントへのリクエスト
// e.g. 'v1.php?gss_id=1m4BI7R-CcjNREH4DUe1xCM3OIVVSGrGx6-7iUtIvUWE&gid=635058114';
const query = 'v1.php?gss_id=' + gss_id + '&gid=' + gid;
const axios = axiosBase.create({
baseURL: base_url,
headers: {
'Content-Type': 'application/json',
'X-Requested-With': 'XMLHttpRequest'
},
responseType: 'json'
});
axios.get(query)
.then(function (response) {
// [4] フロントエンドに対してレスポンスを返す
// res.render('index', response.data);
document.getElementById('response').value = JSON.stringify(response.data, null, 2);
})
.catch(function (error) {
console.log('ERROR!! occurred in Backend.')
});
}
/**
* cURLコマンドの表示
*
* @type {{}}
*/
const refreshCurl = (gss_id, gid) => {
const output_url = base_url + '/v1.php?gss_id=' + gss_id + '&gid=' + gid;
const output = "curl '"+output_url+"'";
document.getElementById('output_url').value = output;
};
/**
* URL変化の際にリフレッシュ
*
* @param target
*/
const refresh = ({target}) => {
console.log('url changed');
/**
* Inputに入っているURL
*/
const url = document.getElementById('url').value;
const gss_id = gss_csv_url.fileid(url);
const gid = gss_csv_url.gid(url);
refreshCurl(gss_id, gid);
request(gss_id, gid);
};
/**
* サンプルでテスト。
*/
const testWithSample = () => {
console.log('clicked');
document.getElementById('url').value = 'https://docs.google.com/spreadsheets/d/1m4BI7R-CcjNREH4DUe1xCM3OIVVSGrGx6-7iUtIvUWE/edit#gid=635058114';
refresh(document.getElementById('url')); // ページロード時に実行
};
/**
* inputの変更のイベントリスナ
*
* @see https://developer.mozilla.org/en-US/docs/Web/Events/change
*/
document.addEventListener('DOMContentLoaded',function() {
document.getElementById('url').onchange = refresh;
// refresh(document.getElementById('url')); // ページロード時に実行
document.querySelector('.test_with_sample').onclick = testWithSample;
},false);
デプロイ
デプロイはNowを使いました。
GitHubと連携していると自動でデプロイされるので便利ですね。
GitHubとNowを連携させます。
後はGitHubにnow.json
を追加するとデプロイされます。
{
"alias": "gss-api",
"builds": [
{ "src": "*.html", "use": "@now/static" },
{ "src": "dist/*", "use": "@now/static" },
{ "src": "images/*", "use": "@now/static" },
{ "src": "*.php", "use": "@now/php" }
],
"files": [
"v1.php", "index.html", "dist/main.js"
],
"version": 2
}
コミットプッシュ毎にNowが動くようになりました。
environmentsという項目でデプロイ状況が確認できます。ライセンスも書いたところ項目が増えました。
なんか自動化されている感がすごく幸せですね ;)
GitHub+Nowで独立して動くので、フォークすればデプロイを含めてすぐに動かすことができます。Nowすごい…!
GSSだけでできない?
この記事を見るとできそうな感じでした。(というかGSSだけでやりたかった!)
Google SpreadSheet のデータを JSON 形式で取得する Web API をサクッと作る - Qiita
が、僕の環境では認証が入ってしまってうまくいかず、外部にAPIゲートウェイ的なものを置く方式になりました。
が、後に「上記の記事でできるよ!」と教えてもらい、確かに動いていました…!
そのほか
シートの外部参照ができるとさらにはかどりそうな気がしました。