Writing more than 1000 rows into Google Sheet

Question

I have to write more than 1000 rows into a Google sheet, therefore I need to increase the number of rows (I got the Google.Apis.Requests.RequestError when I attempted to write 4640 rows).

Searching the Stack Overflow website resulted in information telling me that I needed to use UpdateSheetPropertiesRequest or InsertDimensionRequest and create a new request that could be in the same Spreadsheets.BatchUpdate call.

In line with a Python example on Stack Overflow, I set the properties of the InsertDimensionRequest instance and included them in my existing code (= publicly available C# code by Ian Preston).

The code then looks like this:

public void AddCells(GoogleSheetParameters googleSheetParameters, List rows)
        {
            var requests = new BatchUpdateSpreadsheetRequest { Requests = new List() }; //Existing code

            int sheetId = GetSheetId(_sheetsService, _spreadsheetId, googleSheetParameters.SheetName); //Existing code

            InsertDimensionRequest insertDimensionRequest = new InsertDimensionRequest(); //Added code
            
            insertDimensionRequest.Range.SheetId = sheetId; //Added code
            insertDimensionRequest.Range.Dimension = "ROWS"; //Added code
            insertDimensionRequest.Range.StartIndex = 999; //Added code
            insertDimensionRequest.Range.EndIndex = 6999; //Added code
            insertDimensionRequest.InheritFromBefore = false; //Added code
            
            var request = new Request { UpdateCells = new UpdateCellsRequest { Start = gc, Fields = "*" } };  //Existing code
            //some code here

            var request1 = new Request { ???????? }; //code to be added - how should the request look like?

            requests.Requests.Add(request);  //Existing code
            requests.Requests.Add(request1); //Added code
         
        }

But I do not know how a new request can be created in C#.

My question is: How to create such an request (named as request1 in my code)?

user_8910438 · Answer

Meanwhile, I have managed to figure out an answer.

DimensionRange dr = new DimensionRange
            {
                SheetId = sheetId,
                Dimension = "ROWS",
                StartIndex = 999,
                EndIndex = 6999 // adding extra 6000 rows
            };

var request1 = new Request { InsertDimension = new InsertDimensionRequest { Range = dr, InheritFromBefore = false } };

And then (the order of the requests matters):

requests.Requests.Add(request1);
requests.Requests.Add(request);

EDIT 01-06-2022: The entire code using the aforementioned snippet can be downloaded from GitHub - see the GoogleSheets project there.

It is also possible to use userEnteredValue. This clears the sheet and, for me inexplicably, sets the number of rows to an unknow value, but my 4640 rows were accepted.

EDIT 31-05-2022: If you know more about the usage of userEnteredValue, feel free to elaborate on this matter.

GridRange dr2 = new GridRange
            {
                SheetId = sheetId
            };

var request2 = new Request { UpdateCells = new UpdateCellsRequest { Range = dr2, Fields = "userEnteredValue" } };

And then (the order of the requests probably also matters):

requests.Requests.Add(request2);
requests.Requests.Add(request);

Writing more than 1000 rows into Google Sheet

1 Answers