5．ejs内のjavaScriptからsocket.io経由でSpreadSheetを読み込む
app.js側
const sheetReader = require('./javaScripts/googleApis/sheetReader.js');
//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++//
// ～～const io = socketio(server);
io.sockets.on('connection', socket =>
{
socket.on('socket1', (data, func) =>
{
const ope = "+";
const res = Number(data.val1) + Number(data.val2);
func(ope, res);
});
socket.on('socket2', (data, func) =>
{
const ope = "*";
const res = Number(data.val1) * Number(data.val2);
func(ope, res);
});
socket.on('readSheet', async (data, func) =>
{
const sheetID = data.sheetID;
const range = data.range;
try {
const res = await sheetReader.read(sheetID, range);
func(null, res);
}
catch(err) {
func(err.errors[0]);
}
});
});sheetReader.js
exports.read = async (sheetID, range) =>
{
const creds = await readCreds();
// JSON Web Token(JWT)の設定
const jwtClient = new google.auth.JWT(
creds.client_email,
null,
creds.private_key,
["https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive"]
);
// 認証が通るか確認
try {
result = await jwtClient.authorize();
}
catch(err) {
throw err;
}
const request = {
spreadsheetId: sheetID,
range: range,
majorDimension: "ROWS",
valueRenderOption: "FORMATTED_VALUE",
dateTimeRenderOption: "SERIAL_NUMBER",
auth: jwtClient,
};
try
{
const res = (await sheets.spreadsheets.values.get(request)).data;
return res.values;
}
catch(err) {
throw err;
}
}top.ejs側
<!DOCTYPE html>
<html>
<head>
<meta name="author" content="@MagicalWatosan">
<title><%= title %></title>
<link rel="stylesheet" href="/test/stylesheets/top.css"/>
<script type="text/javascript" src="/socket.io/socket.io.js"></script>
</head>
<body oncontextmenu="return false;">
<input type="text" id="val1" value=12> <span id="ope"> </span> <input type="text" id="val2" value=34> <span id="res"> </span>
<br>
<input type="button" value="足し算" onclick="socketTest(1);">
<input type="button" value="掛け算" onclick="socketTest(2);">
<br>
<input type="text" id="sheetID" value="1msKsJutQIyiKl9ZSSuGaN6uAwVegy1sIHK4_wu82Y_M">
<input type="text" id="range" value="シート1!A1:E2">
<br>
<input type="button" value="読込" onclick="readSheet();">
<br>
<div id="output"> </div>
<script type="text/javascript">
const socket = io.connect("<%= host %>");
function socketTest(num)
{
// "足し算"を押したら"socket1"，"掛け算"を押したら"socket2"でemit
socket.emit('socket' + num, {val1: val1.value, val2: val2.value}, (o, r) =>
{
ope.innerText = o;
res.innerText = "= " + r;
});
}
function readSheet(num)
{
// "読込"を押したら"readSheet"でemit
socket.emit('readSheet', {sheetID: sheetID.value, range: range.value}, (err, res) =>
{
if(err)
{
alert("エラー: " + err.message + "(" + err.reason + ")\n"
+ "「<%= owner %>」又は全員の閲覧権が必要です");
output.value = "";
}
else {
output.innerHTML = res.join("<br>");
}
});
}
</script>
</body>
</html>router.jsのレンダリング部分
警告メッセージ用に，認証に対応するアカウントも渡しておく．
const host = "/";
const owner = "philosophastra@negima-taisen.iam.gserviceaccount.com";
router.use('/test', (req, res, next) =>
{
// "views"でセットしたフォルダ内のejsファイル（test/top.ejs）
const file = 'test/top';
// 引数として渡すオブジェクト<%= title %>として利用できる
const obj = {title: 'Test', host: host, owner: owner};
// Webページをレンダリング
res.render(file, obj);
});結果（認証が通らない場合）
結果（認証が通った場合）
