使用NodeJS操作GoogleSheets
行程前準備
在GCP上建立一個Google Sheets API專案
啟動Sheets API服務
建立OAuth用戶端憑證並下載json檔到專案路徑
複製官方範例的程式碼(https://developers.google.com/sheets/api/quickstart/nodejs)
安裝googleapis(yarn add googleapis)
官方範例內容
const fs = require('fs');
const readline = require('readline');
const {google} = require('googleapis');
// If modifying these scopes, delete token.json.
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'];
// The file token.json stores the user's access and refresh tokens, and is
// created automatically when the authorization flow completes for the first
// time.
const TOKEN_PATH = 'token.json';
// Load client secrets from a local file.
fs.readFile('credentials.json', (err, content) => {//credentials.json修改成自己的憑證路徑
if (err) return console.log('Error loading client secret file:', err);
// Authorize a client with credentials, then call the Google Sheets API.
authorize(JSON.parse(content), listMajors);
});
/**
* Create an OAuth2 client with the given credentials, and then execute the
* given callback function.
* @param {Object} credentials The authorization client credentials.
* @param {function} callback The callback to call with the authorized client.
*/
function authorize(credentials, callback) {
const {client_secret, client_id, redirect_uris} = credentials.installed;
const oAuth2Client = new google.auth.OAuth2(
client_id, client_secret, redirect_uris[0]);
// Check if we have previously stored a token.
fs.readFile(TOKEN_PATH, (err, token) => {
if (err) return getNewToken(oAuth2Client, callback);
oAuth2Client.setCredentials(JSON.parse(token));
callback(oAuth2Client);
});
}
/**
* Get and store new token after prompting for user authorization, and then
* execute the given callback with the authorized OAuth2 client.
* @param {google.auth.OAuth2} oAuth2Client The OAuth2 client to get token for.
* @param {getEventsCallback} callback The callback for the authorized client.
*/
function getNewToken(oAuth2Client, callback) {
const authUrl = oAuth2Client.generateAuthUrl({
access_type: 'offline',
scope: SCOPES,
});
console.log('Authorize this app by visiting this url:', authUrl);
const rl = readline.createInterface({
input: process.stdin,
output: process.stdout,
});
rl.question('Enter the code from that page here: ', (code) => {
rl.close();
oAuth2Client.getToken(code, (err, token) => {
if (err) return console.error('Error while trying to retrieve access token', err);
oAuth2Client.setCredentials(token);
// Store the token to disk for later program executions
fs.writeFile(TOKEN_PATH, JSON.stringify(token), (err) => {
if (err) return console.error(err);
console.log('Token stored to', TOKEN_PATH);
});
callback(oAuth2Client);
});
});
}
/**
* Prints the names and majors of students in a sample spreadsheet:
* @see https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
* @param {google.auth.OAuth2} auth The authenticated Google OAuth client.
*/
function listMajors(auth) {
const sheets = google.sheets({version: 'v4', auth});
sheets.spreadsheets.values.get({
spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',//google sheets id
range: 'Class Data!A2:E',//指定讀取的範圍，Class Data則是分頁簿名稱
}, (err, res) => {
if (err) return console.log('The API returned an error: ' + err);
const rows = res.data.values;
if (rows.length) {
console.log('Name, Major:');
// Print columns A and E, which correspond to indices 0 and 4.
rows.map((row) => {
console.log(`${row[0]}, ${row[4]}`);
});
} else {
console.log('No data found.');
}
});
}憑證路徑修改完後可執行專案的主程式檔，將會進行授權app的認證，網頁上會出現授權碼，將授權碼貼到終端機上
建立Auth驗證函數
官方範例上要操作sheets的API是透過authorize函式驗證，為了方便性所以我們重構成以下程式碼
function getAuth(fn){//fn參數用來接收要執行的函式
fs.readFile('憑證.json',(err,content)=>{
if(err) return console.error(err);
authorize(JSON.parse(content),fn);
})
}取得sheets上的工作表
async function getSheets(auth) {
const sheets = google.sheets({ version: 'v4', auth });
const request = {
spreadsheetId: '你的ID',
includeGridData: false
}
try {
const response = (await sheets.spreadsheets.get(request)).data;//有執行順序需求所以使用async...await
const sheets_info = response.sheets;
sheets_info.forEach(element => {//最終得到的結果會是以陣列型態回傳，可以使用instanceof驗證
console.log(element);
});
} catch (e) {
console.log(e);
}
}
getAuth(getSheets);//呼叫Auth驗證函數，將要執行的函數傳遞過去建立新的工作表
async function addSheets(auth){
const sheets=google.sheets({ version: 'v4', auth });
const request={
spreadsheetId: yourId,
resource:{
requests:[{
addSheet:{
properties:{
title:'測試工作表標題',
}
}
}]
}
};
try{
const response=(await sheets.spreadsheets.batchUpdate(request)).data;
}catch(e){
console.error(e);
}
}
getAuth(addSheets);
/*
執行時候可能出現權限不足問題(GaxiosError: Insufficient Permission)
解決方式:
1.先刪除一開始產生的token.json
2.修改SCOPES變數為:const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
3.重新驗證後會產生一個具有讀寫權限的token.json
*/新增資料到工作表
async function addValues(auth){
const sheets=google.sheets({ version: 'v4', auth });
const title='測試工作表標題';
const request={
spreadsheetId:yourId,
range:`${title}!A1:A3`,//寫入的範圍從A1寫到A3，title為sheets名稱
valueInputOption: "USER_ENTERED",//寫入的選項
resource:{
values:[['test'],['dev'],['col']]//寫入的值，以二維陣列表示
}
};
try{
await sheets.spreadsheets.values.update(request);
console.log('資料寫入成功!');
}catch(e){
console.error(e);
}
}取得工作表內的資料
async function getSheetsValues(auth) {
const sheets = google.sheets({ version: 'v4', auth });
const request = {
spreadsheetId: yourId,
range:'測試工作表標題'//要取得的範圍，目前是取得整張工作表
}
try {
const response = (await sheets.spreadsheets.values.get(request)).data;
console.log(JSON.stringify(response,null,2));
} catch (e) {
console.log(e);
}
}GoogleSheetsAPI裡的功能還很多，詳細資料內容:https://developers.google.com/sheets/api/quickstart/quickstarts-overview