将Firestore数据同步到谷歌工作表

Question

我正在尝试将Firestore中的数据同步到我的谷歌工作表。

我已经创建了一个serviceAccount并将凭据保存在JSON文件中

函数文件夹已成功部署，但函数执行时出现错误。

下面是我的index.js

var functions = require('firebase-functions')
var admin = require('firebase-admin')
var { google } = require('googleapis')
var sheets = google.sheets('v4')
admin.initializeApp()
var spreadsheetId = '...'
var serviceAccount = require('./serviceAccount.json')
var jwtClient = new google.auth.JWT({
email: serviceAccount.client_email,
keys: serviceAccount.private_key,
scopes: ['https://www.googleapis.com/auth/spreadsheets'],
})
var jwtAuthPromise = jwtClient.authorize()
exports.testFunction = functions.firestore.document('/scores/{docID}').onCreate(async snap => {
if (snap.exists) {
var obj = JSON.parse(JSON.stringify(snap.data()))
await jwtAuthPromise
sheets.spreadsheets.values.append(
{
auth: jwtClient,
spreadsheetId: spreadsheetId,
range: 'new!A2',
valueInputOption: 'RAW',
requestBody: {
values: [[obj['Player'], obj['Score']]],
},
},
(err, result) => {
if (err) {
// Handle error
console.log(err)
} else {
console.log('%d cells updated.', result.updatedCells)
}
},
)
}
})

当任何文档被添加到我的scores集合时，cloud函数都会触发，然后数据被添加到谷歌工作表中。

当我手动更新Firestore时，以下是我的错误日志中的错误

testFunction Function execution took 2620 ms, finished with status: 'error'
testFunction Error: function crashed out of request scope Function invocation was interrupted.
testFunction
Error: No key or keyFile set. at GoogleToken.<anonymous> (/srv/node_modules/googleapis/node_modules/gtoken/build/src/index.js:115:23) at Generator.next (<anonymous>) at /srv/node_modules/googleapis/node_modules/gtoken/build/src/index.js:13:71 at new Promise (<anonymous>) at __awaiter (/srv/node_modules/googleapis/node_modules/gtoken/build/src/index.js:9:12) at GoogleToken.getTokenAsync (/srv/node_modules/googleapis/node_modules/gtoken/build/src/index.js:110:16) at GoogleToken.getToken (/srv/node_modules/googleapis/node_modules/gtoken/build/src/index.js:64:21) at JWT.<anonymous> (/srv/node_modules/googleapis/node_modules/google-auth-library/build/src/auth/jwtclient.js:144:40) at Generator.next (<anonymous>) at /srv/node_modules/googleapis/node_modules/google-auth-library/build/src/auth/jwtclient.js:22:71
testFunction
Error: No key or keyFile set.
at GoogleToken.<anonymous> (/srv/node_modules/googleapis/node_modules/gtoken/build/src/index.js:115:23)
at Generator.next (<anonymous>)
at /srv/node_modules/googleapis/node_modules/gtoken/build/src/index.js:13:71
at new Promise (<anonymous>)
at __awaiter (/srv/node_modules/googleapis/node_modules/gtoken/build/src/index.js:9:12)
at GoogleToken.getTokenAsync (/srv/node_modules/googleapis/node_modules/gtoken/build/src/index.js:110:16)
at GoogleToken.getToken (/srv/node_modules/googleapis/node_modules/gtoken/build/src/index.js:64:21)
at JWT.<anonymous> (/srv/node_modules/googleapis/node_modules/google-auth-library/build/src/auth/jwtclient.js:144:40)
at Generator.next (<anonymous>)
at /srv/node_modules/googleapis/node_modules/google-auth-library/build/src/auth/jwtclient.js:22:71
testFunction Unhandled rejection
testFunction
Billing account not configured. External network is not accessible and quotas are severely limited. Configure billing account to remove these restrictions
testFunction
Function execution started

Answer 1

我创建了一个promise，使用Json文件并在需要时返回授权，从而使函数与Google Sheets集成。这是我使用的方法的一个精简版本，但您可以根据自己的需要进行调整：

// Helper to get authorization
// (You may need to add the Drive scope 
// if you want to add additional users permission to edit the file)
var clientPromise = new Promise((resolve, reject) => {
const client = new google.auth.JWT({
keyFile: 'my_key_file.json',
scopes: ['https://www.googleapis.com/auth/spreadsheets', 
'https://www.googleapis.com/auth/drive'],
});
resolve(client);
});
exports.myFunction = functions.firestore
.document('myCollection/{addedDoc}')
.onCreate((snap, context) => {
// do something with the new document
// ...
return new Promise((resolve, reject) => {
// You may not need this part but you can use a new promise here if you need it.
console.log(snap);
resolve(true);
}).then(result => {
return clientPromise;
}).then(auth => {
sheets = google.sheets({
version: 'v4',
auth: auth,
});
// do something with the sheets variable
// ...
});
});