I'm having quota issues with the Sheets API provided by Google (using node.js). I am already using batch requests and have had my write quota increased to 900 per minute via a written request, but I still this error (question at the bottom after explanation):
What I am trying to do is generate a sports league schedule for a chosen amount of weeks (spreadsheets). Each week has 3 separate games (sheets). The user would first just choose how many weeks:
When the user clicks "Yes" for confirming the schedule, spreadsheets are created for whatever the chosen amount is:
Each spreadsheet gets 3 sheets created for the 3 different time slots and the team roster data is copied into the sheets:
Edit 3: I'm using the following code to try and generate these spreadsheets:
// 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 shows that I am not using that many write requests by the way:
Note 2: And I have had my quota limit increased:
Question 1: Am I correctly using batchUpdates, or am I missing a concept that can streamline this code further?
Question 2: My calculations are that I am using 245 write calls to the sheets api, but the "API/Service Details" console is showing 31. Am I missing some concept or miscalculating this somehow? How is my quota being exceeded here? Are the nested 5 arrays a single batchUpdate or 5 batchUpdates? This would add hundreds of write calls to the sheets api if the latter applies.
API Write Calculations:
Create 105 sheets (35 weeks * 3 games) 105 write calls
Add 5 data ranges to each of the 105 sheets 105 write calls (or is this 105 * 5 ?)
Delete "Sheet1" from all 35 sheets using: 35 write calls
Edit: Per request, this is what a week looks like (I cut the length of 'data' , which is normally 10 people for readability. I'd also like to note that I am not having errors with writing these ranges if I choose 1 or 2 weeks instead of 35.
week:
[
{
teamA: {
data: [
[ 'Robert', 'Manning', '[email protected]', 'C', '45' ],
[ 'Adrian', 'Martin', '[email protected]', 'RW', '5' ],
],
name: 'Green'
},
teamB: {
data: [
[ 'Isaac', 'Payne', '[email protected]', 'C', '11' ],
[ 'Alan', 'Lewis', '[email protected]', 'RW', '13' ],
],
name: 'Orange'
},
time: '4:30'
},
{
teamA: {
data: [
[ 'Stewart', 'Taylor', '[email protected]', 'RW', '56' ],
[ 'Lucas', 'Davies', '[email protected]', 'RW', '85' ],
],
name: 'Yellow'
},
teamB: {
data: [
[ 'Dylan', 'Baker', '[email protected]', 'C', '11' ],
[ 'Edward', 'Dowd', '[email protected]', 'D', '65' ],
],
name: 'Black'
},
time: '6:00'
},
{
teamA: {
data: [
[ 'Gavin', 'Knox', '[email protected]', 'C', '45' ],
[ 'Paul', 'Wallace', '[email protected]', 'RW', '5' ],
],
name: 'Red'
},
teamB: {
data: [
['Andrew','Sanderson','[email protected]','C','11'],
['Stewart','MacLeod','[email protected]','RW','13'],
],
name: 'Teal'
},
time: '7:30'
}
]
Edit 2:
I took bits of code out to restrict the scope of the question to API calls, so things like sheetName definitions were omitted, but here it is:
CodePudding user response：
I believe your goal is as follows.
- You want to reduce the number of requests of Sheets API by modifying your script.
In this case, how about the following modification?
In this modification, 2 API calls are used in the loop of for (const week of req.body.schedule) {,,,}.
Modified script:
for (const week of req.body.schedule) {
i ;
let fileMetadata = {
name: "Week-" i,
mimeType: "application/vnd.google-apps.spreadsheet",
parents: [seasonResultsFolderId],
};
let result = await drive.files.create({auth: jwtClient, resource: fileMetadata});
let sheetId = result.data.id;
const sheetNames = week.map((game) =>
game.time.toString().replace(":", ".").trim()
);
// Add sheets and delete 1st tab.
await sheets.spreadsheets.batchUpdate({
spreadsheetId: sheetId,
resource: {
requests: [
...sheetNames.map((title) => ({
addSheet: {
properties: { title },
},
})),
{ deleteSheet: { sheetId: 0 } },
],
},
});
// Put values to each sheet.
const data = week.map((game, i) => {
game.teamA.data.forEach((player) => {
player.push(game.teamA.name);
});
game.teamB.data.forEach((player) => {
player.push(game.teamB.name);
});
return [
{
range: "'" sheetNames[i] "'!A1:I1",
values: [
[
"First",
"Last",
"Email",
"Position",
"Number",
"Team",
"Goals",
"Assists",
"Penalties",
],
],
},
{
range: "'" sheetNames[i] "'!K1:L1",
values: [["Team", "Shots"]],
},
{
range: "'" sheetNames[i] "'!K2:L3",
values: [
[game.teamA.name, ""],
[game.teamB.name, ""],
],
},
{
range: "'" sheetNames[i] "'!A2:F12",
values: game.teamA.data,
},
{
range: "'" sheetNames[i] "'!A14:F24",
values: game.teamB.data,
},
];
});
await sheets.spreadsheets.values.batchUpdate({
spreadsheetId: sheetId,
resource: { valueInputOption: "RAW", data },
});
}
- In your situation, at Method: spreadsheets.batchUpdate, all requests for adding sheets and deleting a sheet can be included in one request.
- In your situation, at Method: spreadsheets.values.batchUpdate, all requests for putting values to each sheet can be included in one request.
Note:
When Sheets API is used in a loop, an error might occur because of the continuous requests. When this script is run, such error occurs, please put the script for waiting in the loop.
If you want to reduce the number of requests of Drive API, the batch requests might be able to be used. Ref