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', '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'
}
]
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:
