我正在尝试使用Google Sheets API spreadsheets.values.batchUpdate函数的方式与here相同。此示例使用Google Apps Script。我想使用Google Cloud Functions来代替。
spreadsheets.values.batchUpdate API文档是可用的。请注意，它与spreadsheets.batchUpdate不同，spreadsheets.batchUpdate具有不同的语法。我对使用前者感兴趣。
我想用环境做同样的事情：谷歌云函数+nodejs8+ googleapis模块。
这是上面链接中的Google Apps脚本示例代码：
function updateGoogleSheet(spreadsheetId) {
/* Written by Amit Agarwal */
/* Web: ctrlq.org Email: amit@labnol.org */
var data = [
{
range: "Sheet1!A1", // Update single cell
values: [
["A1"]
]
},
{
range: "Sheet1!B1:B3", // Update a column
values: [
["B1"],["B2"],["B3"]
]
},
{
range: "Sheet1!C1:E1", // Update a row
values: [
["C1","D1","E1"]
]
},
{
range: "Sheet1!F1:H2", // Update a 2d range
values: [
["F1", "F2"],
["H1", "H2"]
]
}];
var resource = {
valueInputOption: "USER_ENTERED",
data: data
};
Sheets.Spreadsheets.Values.batchUpdate(resource, spreadsheetId);
}.
这是新环境中的上述代码：
const {google} = require('googleapis');
const sheets = google.sheets({version: 'v4'});
const auth = new google.auth.JWT (credentials.client_email, null, credentials.private_key, ['https://www.googleapis.com/auth/spreadsheets']);
function updateGoogleSheet (spreadsheetId) {
/* Written by Amit Agarwal */
/* Web: ctrlq.org Email: amit@labnol.org */
var data = [
{
range: "Sheet1!A1", // Update single cell
values: [
["A1"]
]
},
{
range: "Sheet1!B1:B3", // Update a column
values: [
["B1"],["B2"],["B3"]
]
},
{
range: "Sheet1!C1:E1", // Update a row
values: [
["C1","D1","E1"]
]
},
{
range: "Sheet1!F1:H2", // Update a 2d range
values: [
["F1", "F2"],
["H1", "H2"]
]
}];
var resource = {
spreadsheetId: spreadsheetId,
auth: auth,
valueInputOption: "USER_ENTERED",
data: data
};
sheets.spreadsheets.values.batchUpdate (resource);
}但是，这会导致以下错误：Error: Invalid JSON payload received. Unknown name "data[range]": Cannot bind query parameter. Field 'data[range]' could not be found in request message. Invalid JSON payload received. Unknown name "data[values]": Cannot bind query parameter. Field 'data[values]' could not be found in request message. at Gaxios.request (/srv/node_modules/gaxios/build/src/gaxios.js:70:23) at <anonymous> at process._tickDomainCallback (internal/process/next_tick.js:229:7) response: { config: [Object], data: [Object], headers: [Object], status: 400, statusText: 'Bad Request' }, config: { url: 'https://sheets.googleapis.com/v4/spreadsheets/......spreadsheetId....../values:batchUpdate?valueInputOption=USER_ENTERED&data%5Brange%5D=Sheet1%21A1&data%5Bvalues%5D=A1&data%5Brange%5D=Sheet1%21B1%3AB3&data%5Bvalues%5D=B1&data%5Bvalues%5D=B2&data%5Bvalues%5D=B3&data%5Brange%5D=Sheet1%21C1%3AE1&data%5Bvalues%5D=C1&data%5Bvalues%5D=D1&data%5Bvalues%5D=E1&data%5Brange%5D=Sheet1%21F1%3AH2&data%5Bvalues%5D=F1&data%5Bvalues%5D=F2&data%5Bvalues%5D=H1&data%5Bvalues%5D=H2', method: 'POST', paramsSerializer: [Function], headers: [Object], params: [Object], validateStatus: [Function], retry: true, responseType: 'json', retryConfig: [Object] }, code: 400, errors: [ [Object] ]
我的问题是，如何将resource参数格式化为spreadsheets.values.batchUpdate（），使请求成功？
我找不到任何关于这方面的文档，但从上面的原始代码示例来看似乎是可能的。
2条答案
按热度按时间8cdiaqws1#
如果我的理解是正确的，那么这个修改是什么？
发件人：
收件人：
注：
参考：
如果这不能解决你的问题，我道歉。
8ehkhllq2#
UPDATE
As per the comments, neither the
paramsorrequestBodyproperties appear viable (even though both are documented on the home page of the github repo ). From the samples I've seen, therequestBodyparameter is always paired with a service that is initialized with oauth credentials as follows:Where the request no longer has the auth property:
The above should work. If not, users will at least be aware of the issue.
̶T̶h̶e̶ ̶[̶g̶o̶o̶g̶l̶e̶ ̶a̶p̶i̶ ̶n̶o̶d̶e̶j̶s̶ ̶c̶l̶i̶e̶n̶t̶]̶[̶1̶]̶ ̶w̶o̶r̶k̶s̶ ̶a̶ ̶b̶i̶t̶ ̶d̶i̶f̶f̶e̶r̶e̶n̶t̶l̶y̶.̶ ̶[̶A̶c̶c̶o̶r̶d̶i̶n̶g̶ ̶t̶o̶ ̶t̶h̶e̶ ̶d̶o̶c̶u̶m̶e̶n̶t̶a̶t̶i̶o̶n̶]̶[̶2̶]̶ ̶y̶o̶u̶ ̶h̶a̶v̶e̶ ̶t̶o̶ ̶s̶p̶e̶c̶i̶f̶y̶ ̶a̶ ̶
̶r̶e̶q̶u̶e̶s̶t̶B̶o̶d̶y̶̶ ̶p̶a̶r̶a̶m̶e̶t̶e̶r̶ ̶a̶s̶ ̶f̶o̶l̶l̶o̶w̶s̶:̶̶I̶f̶ ̶y̶o̶u̶ ̶w̶a̶n̶t̶ ̶t̶o̶ ̶m̶a̶k̶e̶ ̶t̶h̶e̶ ̶d̶i̶s̶t̶i̶n̶c̶t̶i̶o̶n̶ ̶b̶e̶t̶w̶e̶e̶n̶ ̶U̶R̶L̶ ̶p̶a̶r̶a̶m̶e̶t̶e̶r̶s̶ ̶a̶n̶d̶ ̶P̶O̶S̶T̶ ̶b̶o̶d̶i̶e̶s̶ ̶m̶o̶r̶e̶ ̶e̶x̶p̶l̶i̶c̶i̶t̶,̶ ̶y̶o̶u̶ ̶c̶a̶n̶ ̶a̶l̶s̶o̶ ̶w̶r̶i̶t̶e̶ ̶y̶o̶u̶r̶ ̶r̶e̶s̶o̶u̶r̶c̶e̶ ̶o̶b̶j̶e̶c̶t̶ ̶a̶s̶ ̶f̶o̶l̶l̶o̶w̶s̶:̶
Addendum
Tanaike's solution is also viable.