如何在谷歌云功能中使用谷歌表格 API [英] How to use Google sheets API while inside a google cloud function
问题描述
我正在试用 Google 的 Cloud Functions 服务，我想阅读和编写 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'}!`);
};
这可行，但我想做谷歌的例子，从谷歌电子表格中读取:
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 Cloud Functions 的方法.我认为 OAuth 不太适合，因为 Cloud Functions 经常在无人值守的情况下运行.幸运的是，有服务帐户，用于机器对机器的通信.
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.
在第 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.
这是我的代码，每次调用云函数时都会在电子表格中附加一行.
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!
这篇关于如何在谷歌云功能中使用谷歌表格 API的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
