我对谷歌(使用node.js)提供的Sheets API有配额问题.我已经在使用批处理请求，并已通过书面请求将我的写入配额增加到每分钟900个，但我仍然存在此错误(解释后的问题位于底部):
我想做的是为选定的周数(Electron 表格)生成一份体育联赛时间表.每周有3场独立的比赛(表).用户首先只需 Select 几周:
当用户单击"是"确认计划时，将为所选金额创建Electron 表格:
每个Electron 表格将为3个不同的时段创建3张工作表，团队排班数据将复制到工作表中:
Edit 3:我正在使用以下代码try 生成这些Electron 表格:
// Create Result Sheets
exports.createGoogleResultsSheets = async (req,res) => {
console.log("createGoogleResultsSheets");
const season = req.body.teamData[0].stats[0].season;
// console.log(season);
const resultSeasonFolders = await getChildFiles(resultParentFolderID);
// console.log(resultSeasonFolders);
const exists = propertyExists(resultSeasonFolders,'name',season);
// Create season folder if it does not exist yet and get its id
let seasonResultsFolderId = null;
if (exists) {
// console.log("Season Exists");
const result = resultSeasonFolders.filter(folder=>folder.name == season).map(folder=>folder.id);
seasonResultsFolderId = result[0];
} else {
// console.log("Season does not exist");
// Create Season Folder
let fileMetadata = {
'name': season,
'mimeType': 'application/vnd.google-apps.folder',
'parents' : [resultParentFolderID]
};
const result = await drive.files.create({auth: jwtClient, resource: fileMetadata});
// console.log(result);
seasonResultsFolderId = result.data.id;
}
// console.log("Folder ID:");
// console.log(seasonResultsFolderId);
var i = 0;
for (const week of req.body.schedule) {
//console.log(util.inspect(week, false, null, true));
i++;
// create sheet for each week
let fileMetadata = {
'name': "Week-"+i,
'mimeType': 'application/vnd.google-apps.spreadsheet',
'parents' : [seasonResultsFolderId]
};
let result = await drive.files.create({auth: jwtClient, resource: fileMetadata});
let spreadsheetId = result.data.id;
for (const game of week) {
game.teamA.data.forEach(player => {
player.push(game.teamA.name);
});
game.teamB.data.forEach(player => {
player.push(game.teamB.name);
});
// Can't have sheet names containing : if we want to use .append()
let sheetName = game.time.toString().replace(':', '.').trim();
// add each game to weeks spreadsheet
await sheets.spreadsheets.batchUpdate ({
spreadsheetId : spreadsheetId,
resource: {requests: [
{addSheet: {properties: {title: sheetName }}}
]}
});
console.log("Spreadsheet Id:");
console.log(spreadsheetId);
// console.log(sheetName);
// let sheetId = await getSheetId(spreadsheetId,sheetName);
// // format cells
// let formatResources = {
// spreadsheetId: sheetId,
// resource: {
// requests: [
// {
// repeatCell: {
// range: {
// sheetId: sheetId,
// startRowIndex: 0,
// },
// cell: {
// userEnteredFormat: {
// textFormat: {
// bold: true,
// },
// },
// },
// fields: "userEnteredFormat.textFormat",
// },
// },
// ],
// },
// };
//await sheets.spreadsheets.batchUpdate(formatResources);
// add data to each game sheet
let resources = {
spreadsheetId: spreadsheetId,
resource:{
valueInputOption: "RAW",
data:[
{
range: "'" + sheetName + "'!A1:I1",
values: [['First','Last','Email','Position','Number','Team','Goals','Assists','Penalties']]
},
{
range: "'" + sheetName + "'!K1:L1",
values: [['Team','Shots']]
},
{
range: "'" + sheetName + "'!K2:L3",
values: [[game.teamA.data.name,''],[game.teamB.data.name,'']]
},
{
range: "'" + sheetName + "'!A2:F12",
values: game.teamA.data
},
{
range: "'" + sheetName + "'!A14:F24",
values: game.teamB.data
}
]
}
};
await sheets.spreadsheets.values.batchUpdate(resources);
}
// delete "Sheet1" (gid=0) from every spreadsheet
await sheets.spreadsheets.batchUpdate({
spreadsheetId: spreadsheetId,
resource: { requests: [
{deleteSheet : {sheetId :0}}
]}
});
}
};
Note: Google Sheets API/Service Details显示我没有使用那么多写请求:
Note 2:，我的配额限额增加了:
Question 1:我是否正确地使用了batchUpdates，或者我是否缺少一个可以进一步简化此代码的概念？
Question 2:我的计算是，我使用了245个对sheets api的写调用，但"api/服务详细信息"控制台显示了31个.我是不是遗漏了一些概念，还是算错了？怎么会超过我的配额？嵌套的5个数组是单个batchUpdate还是5个batchUpdate？如果后者适用，这将向sheets api添加数百个写调用.
API Write Calculations:
-
制作105张(35周*3场)
-
将5个数据范围添加到105张表格中的每一张
-
使用以下方法从所有35张图纸中删除"Sheet1":
编辑:根据请求，这是一周的情况(我缩短了"数据"的长度，为了可读性，通常是10人.我还想指出，如果我 Select 1周或2周而不是35周，我在写这些范围时没有错误.
week:
[
{
teamA: {
data: [
[ 'Robert', 'Manning', 'robert.manning@email.com', 'C', '45' ],
[ 'Adrian', 'Martin', 'adrian.martin@email.com', 'RW', '5' ],
],
name: 'Green'
},
teamB: {
data: [
[ 'Isaac', 'Payne', 'isaac.payne@email.com', 'C', '11' ],
[ 'Alan', 'Lewis', 'alan.lewis@email.com', 'RW', '13' ],
],
name: 'Orange'
},
time: '4:30'
},
{
teamA: {
data: [
[ 'Stewart', 'Taylor', 'stewart.taylor@email.com', 'RW', '56' ],
[ 'Lucas', 'Davies', 'lucas.davies@email.com', 'RW', '85' ],
],
name: 'Yellow'
},
teamB: {
data: [
[ 'Dylan', 'Baker', 'dylan.baker@email.com', 'C', '11' ],
[ 'Edward', 'Dowd', 'edward.dowd@email.com', 'D', '65' ],
],
name: 'Black'
},
time: '6:00'
},
{
teamA: {
data: [
[ 'Gavin', 'Knox', 'gavin.knox@email.com', 'C', '45' ],
[ 'Paul', 'Wallace', 'paul.wallace@email.com', 'RW', '5' ],
],
name: 'Red'
},
teamB: {
data: [
['Andrew','Sanderson','andrew.sanderson@email.com','C','11'],
['Stewart','MacLeod','stewart.macleod@email.com','RW','13'],
],
name: 'Teal'
},
time: '7:30'
}
]
编辑2:
