Google Spreadsheets กับความสามารถที่มาจาก Excel ตอนที่ 4
หลังจากที่เราได้เตรียมพร้อมมาจากตอนที่แล้ว ต่อไปจะเข้าสู่การลงมือสร้างการเชื่อมต่อของเว็บไซต์เราเข้ากับ Spreadsheet API โดยทำตามขั้นตอนดังนี้
Step 1: สร้าง Client ID
1. เข้าไปยัง Wizard ของ Google Developer Console คลิก Continue และ Go to credentials ต่อไป
2. ในหน้า Add credentials to your project ให้เลือก cancel ก่อน
3. จากนั้นเข้าไปที่แท็บ OAuth consent screen ด้านบน และเลือก email address และกรอก Product name จากนั้นกด Save
4. เข้าไปที่ Credentials คลิกที่ Create credentials แล้วเลือก OAuth client ID
5. ติ๊กตัวเลือก Web application จากนั้นตั้งชื่อ และในช่อง Authorized JavaScript origins ให้ใส่ http://localhost:3000 แล้วกด Create
6. จากนั้นจะมีรายละเอียดของ Client ID ขึ้นมาให้จดบันทึกเอาไว้สำหรับใช้ในขั้นตอนต่อไป
Step 2 : ปุ่ม Google Sign-in
ในการใช้งาน Speadsheet จำเป็นต้องเชื่อมต่อบัญชี Google เสียก่อน ในขั้นตอนนี้เราจะเพิ่มปุ่มสำหรับให้ผู้ใช้ Sign-in และอนุญาตการใช้งานผ่านบัญชี Google
1. ใน views/layout.hbs เพิ่มโค้ดด้านล่างนี้ลงไปภายใน <head>
<meta name=”google-signin-scope”
content=”https://www.googleapis.com/auth/spreadsheets">
<meta name=”google-signin-client_id” content=”{CLIENT ID}”>
<script src=”https://apis.google.com/js/platform.js" async defer></script>และแทนที่ข้อความ
{CLIENT ID} ด้วย Client ID ที่ได้จากข้อ 6. ของขั้นตอนที่แล้ว
2. หลังจากเพิ่มโค้ดด้านบนที่จะช่วยเชื่อมต่อและแสดงผลปุ่ม Google Sign-in แล้ว ให้เพิ่มโค้ดด้านล่างนี้ใต้ comment Add Google Sign-in button ด้วย
<div id=”profile” class=”dock-right”><div class=”user”><b class=”name”></b></br><em class=”email”></em></div><div class=”g-signin2 btn-flat g-button no-padding” data-onsuccess=”onSignIn”></div></div>
3. ใน action.js เพิ่มฟังก์ชันการแสดงผลชื่อของ user เข้าไป
function onSignIn(user) {
var profile = user.getBasicProfile();
$('#profile .name').text(profile.getName());
$('#profile .email').text(profile.getEmail());
}จากนั้นสามารถทำการ refresh หน้าเว็บเพื่อทดสอบการทำงานของปุ่ม Google Sign-in ได้ที่บริเวณด้านบนของหน้าจอ
Step 3: เพิ่มการทำงานกับ Spreadsheet ให้กับ application
สำหรับขั้นตอนนี้เราจะเพิ่มความสามารถให้กับเว็บไซต์ในการติดตาม spreadsheet ที่เราสร้างขึ้นและแสดงผลรายการของ spreadsheet ที่เก็บไว้ในบัญชีของเรา
1. สร้างไฟล์ spreadsheets.js ภายในโฟลเดอร์ models และเพิ่มโค้ดลงไป
"use strict";
module.exports = function(sequelize, DataTypes) {var Spreadsheet = sequelize.define('Spreadsheet', {id: {type: DataTypes.STRING, allowNull: false, primaryKey: true},sheetId: {type: DataTypes.INTEGER, allowNull: false},name: {type: DataTypes.STRING, allowNull: false},});return Spreadsheet;};
2. ใน route.js เพิ่มฟังก์ชันในการดึงข้อมูลรายการ spreadsheet มาเตรียมแสดงผลบนหน้าหลัก ด้วยการแทนที่ฟังก์ชันใต้คอมเม้นท์ GET home page. ด้วยโค้ดด้านล่างนี้
router.get(‘/’, function(req, res, next) {var options = {order: [[‘createdAt’, ‘DESC’]]};Sequelize.Promise.all([models.Order.findAll(options),models.Spreadsheet.findAll(options)]).then(function(results) {res.render(‘index’, {orders: results[0],spreadsheets: results[1]});});});
3. ใน views/index.hbs เพิ่มพื้นที่สำหรับ UI แสดงผลรายการ spreadsheet ใต้ comment Add Spreadsheet Sync ดังด้านล่างนี้
<table><tr class=”title”><th>Spreadsheet</th><th><button class=”btn custom waves-effect waves-light secondary-content btn-progress”rel=”create” type=”button”>Create</button></th></tr></table><ul class=”collection with-header”>{{#each spreadsheets}}<li class=”collection-header highlight”><a class=”sync”href=”https://docs.google.com/spreadsheets/d/{{id}}/edit"target=”_blank”>{{name}}</a><button class=”secondary-content sync btn-floating waves-effect waves-green grey lighten-4 btn-progress”rel=”sync” data-spreadsheetid=”{{id}}”type=”button”><i class=”material-icons teal-text”>cached</i></button></li>{{/each}}</ul>
4. ใน public/javascripts/action.js เพิ่มฟังก์ชันการทำงานไว้รองรับปุ่ม Create และ ปุ่ม Sync ใต้ TODO ส่วนสุดท้าย
$(function() {$('button[rel="create"]').click(function() {makeRequest('POST', '/spreadsheets', function(err, spreadsheet) {if (err) return showError(err);window.location.reload();});});$('button[rel="sync"]').click(function() {var spreadsheetId = $(this).data('spreadsheetid');var url = '/spreadsheets/' + spreadsheetId + '/sync';makeRequest('POST', url, function(err) {if (err) return showError(err);showMessage('Sync complete.');});});});function makeRequest(method, url, callback) {var auth = gapi.auth2.getAuthInstance();if (!auth.isSignedIn.get()) {return callback(new Error('Signin required.'));}var accessToken = auth.currentUser.get().getAuthResponse().access_token;setProcessBarActive(true);$.ajax(url, {method: method,headers: {'Authorization': 'Bearer ' + accessToken},success: function(response) {setProcessBarActive(false);return callback(null, response);},error: function(response) {setProcessBarActive(false);return callback(new Error(response.responseJSON.message));}});}
5. ติดตั้ง library ที่จำเป็นในการใช้ Google API ผ่าน npm ด้วยคำสั่งต่อไปนี้
npm install googleapis — save
npm install google-auth-library — save6. สร้างไฟล์ sheets.js และเพิ่มโค้ดสำหรับทำงานกับ Sheet API ด้านล่างเข้าไป
var google = require(‘googleapis’);var googleAuth = require(‘google-auth-library’);var util = require(‘util’);var SheetsHelper = function (accessToken) {var authClient = new googleAuth();var auth = new authClient.OAuth2();auth.credentials = {access_token: accessToken};this.service = google.sheets({ version: ‘v4’, auth: auth });};module.exports = SheetsHelper;
จากนั้นตามด้วยฟังก์ชันสร้าง spreadsheet
SheetsHelper.prototype.createSpreadsheet = function(title, callback) {
var self = this;
var request = {
resource: {
properties: {
title: title
},
sheets: [
{
properties: {
title: 'Data',
gridProperties: {
columnCount: 6,
frozenRowCount: 1
}
}
},
// TODO: Add second (Pivot) sheet
]
}
};
self.service.spreadsheets.create(request, function(err, spreadsheet) {
if (err) {
return callback(err);
}
// TODO: Add header rows.
return callback(null, spreadsheet);
});
};7. ใน route.js ทำการเพิ่มส่วนการทำงานของ sheets.js เข้ากับ application ด้วยโค้ดด้านล่าง
var SheetsHelper = require(‘./sheets’);router.post(‘/spreadsheets’, function(req, res, next) {var auth = req.get(‘Authorization’);if (!auth) {return next(Error(‘Authorization required.’));}var accessToken = auth.split(‘ ‘)[1];var helper = new SheetsHelper(accessToken);var title = ‘Orders (‘ + new Date().toLocaleTimeString() + ‘)’;helper.createSpreadsheet(title, function(err, spreadsheet) {if (err) {return next(err);}var model = {id: spreadsheet.spreadsheetId,sheetId: spreadsheet.sheets[0].properties.sheetId,name: spreadsheet.properties.title};models.Spreadsheet.create(model).then(function() {return res.json(model);});});});
สามารถทดสอบการทำงานของปุ่ม Create ด้วยการ refresh หน้าเว็บได้ โดยเมื่อกดแล้วจะมี spreadsheet ปรากฏขึ้นมา 1 รายการ และเมื่อคลิกที่รายการจะพบ Sheet เปล่าชื่อว่า Data ถูกสร้างขึ้นมา
Step 4: เชื่อมโยงข้อมูลสู่ Spreadsheet
จากขั้นตอนที่แล้วเราสร้าง sheet เปล่าๆขึ้นมา เราจะมาทำการแต่งหน้าตาของ sheet ให้ดูสอดคล้องกับข้อมูลของเรากัน
- ใน sheets.js ในส่วน
// TODO: Add header rows.
return callback(null, spreadsheet);ให้แทนที่ด้วย
var dataSheetId = spreadsheet.sheets[0].properties.sheetId;
var requests = [
buildHeaderRowRequest(dataSheetId),
];
// TODO: Add pivot table and chart.
var request = {
spreadsheetId: spreadsheet.spreadsheetId,
resource: {
requests: requests
}
};
self.service.spreadsheets.batchUpdate(request, function(err, response) {
if (err) {
return callback(err);
}
return callback(null, spreadsheet);
});ซึ่งโค้ดด้านบนจะทำหน้าที่ติดต่อกับ spreadsheet ในการจัดการข้อมูลต่างๆภายใน sheet หลังจากเพิ่มเติมโค้ดด้านบนลงไปแล้วให้เพิ่มโค้ดเหล่านี้ด้วย
ระบุโครงสร้างของแต่ละ column ให้สอดคล้องกับ database
var COLUMNS = [{ field: ‘id’, header: ‘ID’ },{ field: ‘Customers’, header: ‘Customer Name’ },{ field: ‘ProductID’, header: ‘Product ID’ },{ field: ‘Amount’, header: ‘Units Ordered’ },{ field: ‘Price’, header: ‘Unit Price’ },{ field: ‘Status’, header: ‘Status’ }];
ฟังก์ชันสร้าง request ติดต่อกับ spreadsheet
function buildHeaderRowRequest(sheetId) {var cells = COLUMNS.map(function (column) {return {userEnteredValue: {stringValue: column.header},userEnteredFormat: {textFormat: {bold: true}}}});return {updateCells: {start: {sheetId: sheetId,rowIndex: 0,columnIndex: 0},rows: [{values: cells}],fields: ‘userEnteredValue,userEnteredFormat.textFormat.bold’}};}
ฟังก์ชัน buildHeaderRowRequest จะทำการ loop สร้าง CellData object ตามจำนวนคอลัมน์ ซึ่งจะมีชื่อตามที่เรากำหนดและแสดงผลเป็นตัวหนา และเมื่อรันจนจบ loop แล้วก็ถูกนำมารวมกันเป็นรีเควส UpdateCells
เมื่อเสร็จทั้งสองขั้นตอนนี้แล้ว spreadsheet ที่ถูกสร้างขึ้นใหม่อันต่อๆไปจะมีหัวตารางตามที่้เรากำหนดโครงสร้างเอาไว้ดังภาพ
2. ใน route.js สร้างฟังก์ชันรองรับให้ปุ่ม sync ข้อมูลสามารทำงานได้
router.post(‘/spreadsheets/:id/sync’, function (req, res, next) {var auth = req.get(‘Authorization’);if (!auth) {return next(Error(‘Authorization required.’));}var accessToken = auth.split(‘ ‘)[1];var helper = new SheetsHelper(accessToken);Sequelize.Promise.all([models.Spreadsheet.findById(req.params.id),models.Order.findAll()]).then(function (results) {var spreadsheet = results[0];var orders = results[1];helper.sync(spreadsheet.id, spreadsheet.sheetId, orders, function (err) {if (err) {return next(err);}return res.json(orders.length);});});});
3.ใน sheets.js เพิ่มฟังก์ชันปรับปรุง sheet ให้มีขนาดสอดคล้องกับข้อมูลในฐานข้อมูลของเรา
SheetsHelper.prototype.sync = function (spreadsheetId, sheetId, orders, callback) {var requests = [];// Resize the sheet.requests.push({updateSheetProperties: {properties: {sheetId: sheetId,gridProperties: {rowCount: orders.length + 1,columnCount: COLUMNS.length}},fields: ‘gridProperties(rowCount,columnCount)’}});// Set the cell values.requests.push({updateCells: {start: {sheetId: sheetId,rowIndex: 1,columnIndex: 0},rows: buildRowsForOrders(orders),fields: ‘*’}});// Send the batchUpdate request.var request = {spreadsheetId: spreadsheetId,resource: {requests: requests}};this.service.spreadsheets.batchUpdate(request, function (err) {if (err) {return callback(err);}return callback();});};
จากโค้ดข้างต้น จะเรียกใช้ UpdateSheetPropertiesRequest เพื่อปรับขนาดตารางให้สอดคล้องกับข้อมูลตารางที่เราจะเขียนลงไป และ UpdateCells จะเขียนค่าลงไปในแต่ละช่องของตาราง
และฟังก์ชัน buildRowsForOrders จะทำการแปลงข้อมูลจาก JSON ให้กลายเป็น ตาราง ด้วยโค้ดดานล่างนี้
function buildRowsForOrders(orders) {return orders.map(function (order) {var cells = COLUMNS.map(function (column) {switch (column.field) {case 'Amount':return {userEnteredValue: {numberValue: order.Amount},userEnteredFormat: {numberFormat: {type: 'NUMBER',pattern: '#,##0'}}};break;case 'Price':return {userEnteredValue: {numberValue: order.Price},userEnteredFormat: {numberFormat: {type: 'CURRENCY',pattern: '"$"#,##0.00'}}};break;case 'Status':return {userEnteredValue: {stringValue: order.Status},dataValidation: {condition: {type: 'ONE_OF_LIST',values: [{ userEnteredValue: 'PENDING' },{ userEnteredValue: 'SHIPPED' },{ userEnteredValue: 'DELIVERED' }]},strict: true,showCustomUi: true}};break;default:return {userEnteredValue: {stringValue: order[column.field].toString()}};}});return {values: cells};});}
คอลัมน์ Amount และ Price จะบังคับให้ช่องเหล่านั้นจัดเก็บข้อมูลเป็นตัวเลข และ Status จะตรวจสอบข้อมูลในช่องให้ตรงกับตัวเลือกที่ตั้งไว้
จากนั้นเราสามารถทดสอบปุ่ม sync ด้านหลังรายการให้อัพเดทให้แสดงข้อมูลที่ตรงกับใน database ได้ และเมื่อเราเพิ่ม order ใหม่ เราสามารถกด sync ได้ทันทีเช่นกัน (ในภาพมีการลบ Order#2 ออกไปจึงมีเพียง 3 รายการ)
Step 5: สร้าง Sheet สรุปข้อมูล
ในขั้นตอนนี้เราจะอาศัยความสามารถของ Google Spreadsheet ที่สร้าง Pivot table และ กราฟเพื่อทำการสรุปข้อมูลได้
1. ใน sheets.js ภายใต้คอมเมนท์ Add second (Pivot) sheet ในฟังก์ชัน createSpreadsheet เพิ่มโค้ดด้านล่างเข้าไป เพื่อสร้างชีทที่สองสำหรับการสรุปข้อมูล
{
properties: {
title: ‘Pivot’,
gridProperties: {
hideGridlines: true
}
}
}2. เพิ่มโค้ดเหล่านี้สำหรับติดต่อกับ spreadsheet เพื่อรับข้อมูลไปเขียนบนชีทใหม่ที่เราสร้างขึ้น
ต่อท้าย Add pivot table and chart.
var pivotSheetId = spreadsheet.sheets[1].properties.sheetId;
requests = requests.concat([
buildPivotTableRequest(dataSheetId, pivotSheetId),
buildFormatPivotTableRequest(pivotSheetId),
buildAddChartRequest(pivotSheetId)
]);และตามด้วยฟังก์ชันทั้งสามที่จะถูกเรียกใช้โดยโค้ดด้านบน ไว้ท้ายสุดของไฟล์
function buildPivotTableRequest(sourceSheetId, targetSheetId) {
return {
updateCells: {
start: { sheetId: targetSheetId, rowIndex: 0, columnIndex: 0 },
rows: [
{
values: [
{
pivotTable: {
source: {
sheetId: sourceSheetId,
startRowIndex: 0,
startColumnIndex: 0,
endColumnIndex: COLUMNS.length
},
rows: [
{
sourceColumnOffset: getColumnForField(‘productCode’).index,
showTotals: false,
sortOrder: ‘ASCENDING’
}
],
values: [
{
summarizeFunction: ‘SUM’,
sourceColumnOffset: getColumnForField(‘unitsOrdered’).index
},
{
summarizeFunction: ‘SUM’,
name: ‘Revenue’,
formula: util.format(“=’%s’ * ‘%s’”,
getColumnForField(‘unitsOrdered’).header,
getColumnForField(‘unitPrice’).header)
}
]
}
}
]
}
],
fields: ‘*’
}
};
}function buildFormatPivotTableRequest(sheetId) {
return {
repeatCell: {
range: { sheetId: sheetId, startRowIndex: 1, startColumnIndex: 2 },
cell: {
userEnteredFormat: {
numberFormat: { type: ‘CURRENCY’, pattern: ‘“$”#,##0.00’ }
}
},
fields: ‘userEnteredFormat.numberFormat’
}
};
}function buildAddChartRequest(sheetId) {
return {
addChart: {
chart: {
spec: {
title: ‘Revenue per Product’,
basicChart: {
chartType: ‘BAR’,
legendPosition: ‘RIGHT_LEGEND’,
domains: [
// Show a bar for each product code in the pivot table.
{
domain: { sourceRange: { sources: [{
sheetId: sheetId,
startRowIndex: 0,
startColumnIndex: 0,
endColumnIndex: 1
}]}}
}
],
series: [
// Set that bar’s length based on the total revenue.
{
series: { sourceRange: { sources: [{
sheetId: sheetId,
startRowIndex: 0,
startColumnIndex: 2,
endColumnIndex: 3
}]}}
}
]
}
},
position: {
overlayPosition: {
anchorCell: { sheetId: sheetId, rowIndex: 0, columnIndex: 3 },
widthPixels: 600,
heightPixels: 400
}
}
}
}
};
}function getColumnForField(field) {
return COLUMNS.reduce(function(result, column, i) {
if (column.field == field) {
column.index = i;
return column;
}
return result;
});
}
หลังจากเสร็จสิ้นขั้นตอนนี้ ทุกครั้งที่กดปุ่ม Create เราจะได้ Spreadsheet ที่สร้างขึ้นใหม่อยู่ใน drive ของเรา และประกอบด้วยชีทข้อมูล และชีทสรุปข้อมูลที่มีกราฟอยู่ข้างๆ
และทุกครั้งที่เรากด Sync จะทำการอัพเดท Spreadsheet นั้นๆด้วยข้อมูลปัจจุบันที่อยู่ใน application ของเราและข้อมูลสรุปจะปรากฏในชีทที่สองทันที
เมื่อมาถึงจุดนี้แล้ว เราก็จะได้ web application ที่สามารถสร้างรายงานสรุปเป็น Spreadsheet ที่สามารถปรับแต่งได้ในภายหลังและเชื่อมโยงข้อมูลเข้ากับฐานข้อมูลภายในเว็บไซต์ของเราได้ ซึ่ง Spreadsheet API ยังมีเครื่องมือที่น่าสนใจมาก สามารถศึกษา Developer documentation เพิ่มเติมได้ หรือค้นหาคำตอบด้วยตำราคู่ใจของเหล่าโปรแกรมเมอร์ Stackoverflow
ขอขอบคุณผู้อ่านทุกท่านที่ติดตามเรามาทั้ง 4 ตอนพวกเราหวังว่าข้อมูลที่เราแนะนำทั้งหมดนี้จะเป็นประโยชน์ต่อผู้อ่านและผู้อ่านสามารถนำไปประยุกต์ใช้ได้ต่อไปครับ
