Adding new sheet to existing spreadsheet via Google sheets api v4

Question

I've been facing an issue within adding a new sheet (tab) to existing spreadsheet.

Before the main issue, I will try to explain why I need to do such a thing and maybe there will be other solution. I'm trying to upload spreadsheet with several sheets. When sheets have smaller size, everything is ok. But when I do the "service.spreadsheets().create(spreadsheet).execute()" request with really big sheets (like two sheets with 40k cells), I get normal response, but created spreadsheet contains only empty "Untitled document" with empty tab. That's first thing which bothers me, why I don't receive something like "your insert is too big" or something like that.

So I would like to create spreadsheet, insert first tab (as a smaller request) and then add another tab (sheet) and so.. But what I only found over the stack and google documentation is "BatchUpdateSpreadsheetRequest". But this request doesn't allow me to add already created sheet, it just create new empty sheet, which is really annoying.

Do I miss some API call? Also I found over the documentation and stack some limits, but there is no clear info about how big can be requests with sheets (I've seen all the 400k rows, and what you can found here), but that didn't help a lot.

Can someone provide me info how to "split" spreadsheet creation into creating more smaller request so the created spreadsheet will contain all the data?

Thanks

Answer 1

The V4 API currently has a limit of 10MB of data per request, though I don't think we advertise this fact in the documentation anywhere right now.

To workaround, you can use multiple different requests in a BatchUpdateSpreadsheetRequest -- an AddSheetRequest as you mentioned, plus UpdateCellsRequest, or some number of other requests. Check out the guide that details what requests deal with what portions of the spreadsheet.

If you have specific portions of the spreadsheet you're curious about how to set, please follow up.