如何在Google云端功能中使用Google Sheet API [英] How to use Google sheets API while inside a google cloud function
问题描述
我正在尝试Google的云功能服务，我想阅读和编写Google电子表格，但似乎无法找到任何示例或方法来执行此操作。
I'm trying out Google's Cloud Functions service and I want to read and write a Google Spreadsheets but can't seem to find any examples or ways to do this.
我的问题是因为Google云功能的示例javascript是：
My problem steams from the fact that the example javascript for a Google cloud function is:
exports.helloWorld = function helloWorld (req, res) {
res.send(`Hello ${req.body.name || 'World'}!`);
};
这有效，但我想以google为例从谷歌电子表格中读取：
This works but I want to do what google has as a example to read from a Google spreadsheet:
gapi.load('client:auth2', initClient);
function initClient() {
gapi.client.init({
discoveryDocs: DISCOVERY_DOCS,
clientId: CLIENT_ID,
scope: SCOPES
}).then(function () {
// Listen for sign-in state changes.
gapi.auth2.getAuthInstance().isSignedIn.listen(updateSigninStatus);
// Handle the initial sign-in state.
gapi.client.sheets.spreadsheets.values.get({
spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
range: 'Class Data!A2:E',
}).then(function(response) {
var range = response.result;
if (range.values.length > 0) {
appendPre('Name, Major:');
for (i = 0; i < range.values.length; i++) {
var row = range.values[i];
// Print columns A and E, which correspond to indices 0 and 4.
appendPre(row[0] + ', ' + row[4]);
}
} else {
appendPre('No data found.');
}
}, function(response) {
appendPre('Error: ' + response.result.error.message);
});
});
}
有没有人知道这是否可行或示例说明如何做某事与此相似？
Does anyone know if this is possible or an example that shows how to do something similar to this?
推荐答案
以下是我使用Google云功能的方法。我认为OAuth不太适合，因为云功能通常无人值守。幸运的是，服务帐户，用于机器到机器的通信。
Here is how I did it with Google Cloud Functions. I figured that OAuth wouldn't be a good fit, as Cloud Functions often run unattended. Fortunately there are service accounts, meant for machine-to-machine communication.
< a href =https://i.stack.imgur.com/IDw3F.png =noreferrer>
在步骤1中，在您的计算机上下载了JSON格式的密钥文件。将其保存在项目目录中并重命名为 credentials.json 。
In step 1, a key file in JSON format was downloaded on your computer. Save it in your project directory and rename it credentials.json.
将步骤3中的API密钥复制并保存在名为 api_key.json 的文件中项目目录。它应该如下所示：
Copy and save the API key from step 3 in a file called api_key.json in your project directory. It should look like this:
{
"key": "<PASTE YOUR API KEY HERE>"
}
5。授予对服务帐户的电子表格访问权限
使用在步骤1中创建的服务帐户电子邮件共享电子表格。
5. Grant spreadsheet access to the service account
Share the spreadsheet with the service account email created in step 1.
这是我的代码，每次调用Cloud函数时都会在电子表格中附加一行。
Here is my code which appends a row to the spreadsheet each time the Cloud Function is called.
const {google} = require('googleapis');
exports.reply = (req, res) => {
var jwt = getJwt();
var apiKey = getApiKey();
var spreadsheetId = '<PASTE YOUR SPREADSHEET ID HERE>';
var range = 'A1';
var row = [new Date(), 'A Cloud Function was here'];
appendSheetRow(jwt, apiKey, spreadsheetId, range, row);
res.status(200).type('text/plain').end('OK');
};
function getJwt() {
var credentials = require("./credentials.json");
return new google.auth.JWT(
credentials.client_email, null, credentials.private_key,
['https://www.googleapis.com/auth/spreadsheets']
);
}
function getApiKey() {
var apiKeyFile = require("./api_key.json");
return apiKeyFile.key;
}
function appendSheetRow(jwt, apiKey, spreadsheetId, range, row) {
const sheets = google.sheets({version: 'v4'});
sheets.spreadsheets.values.append({
spreadsheetId: spreadsheetId,
range: range,
auth: jwt,
key: apiKey,
valueInputOption: 'RAW',
resource: {values: [row]}
}, function(err, result) {
if (err) {
throw err;
}
else {
console.log('Updated sheet: ' + result.data.updates.updatedRange);
}
});
}
希望这有帮助！
Hope this helps!
这篇关于如何在Google云端功能中使用Google Sheet API的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
