Google Sheets API V4 Add Row .NET

Question

The Google documentation page says

The Sheets API v4 does provide an AppendCells request that can be used with the spreadsheets.batchUpdate method to append a row of data to a sheet (and simultaneously update the cell properties and formatting, if desired).

And this does work for adding new rows, even though creating the RowData payload is tedious. However, this does not allow for setting the ValueInputOption.

Google also says

However, it is usually easier to simply determine the A1 notation of the row you wish to add, and then issue a spreadsheets.values.update request to overwrite that row. In this case, any data in the specified row is overwritten.

Now this works for updating data on existing rows - including the ValueInputOption. However, when I use this for appending a new row (i.e. provide a range that is the next row), the server returns a 503 error. There must be a trick I am missing?

Please share the codes which you have done so far and error logs too so we might see where the error is coming from. — Teyam
– Teyam, Commented Jun 5, 2016 at 16:03
Added code below in answer, since I no longer have the exact code from when I was struggling with problem. The code posted below has all the relevant code though — Ian Botham
– Ian Botham, Commented Jun 6, 2016 at 17:11

Ian Botham · Accepted Answer · 2016-06-06 17:06:46Z

I should have posted code in my question as the commenter has suggested. However, I am posting it as part of this answer - which is clearly a sub-optimal solution but serves my purpose.

This is the code I ended up with (omitting the OAuth and service instantiation)

 Public Function AddRows(Values As Object()()) As Boolean

        Try

            'Create dummy rows value payload
            Dim cell2add As New CellData
            Dim cellval As New ExtendedValue
            cellval.NumberValue = 0
            cell2add.UserEnteredValue = cellval
            Dim data2add As New RowData
            data2add.Values = {cell2add}

            Dim rowdataList As New List(Of RowData)
            For i = 0 To UBound(Values)
                rowdataList.Add(data2add)
            Next

            'Add a request to append to the sheet's data (expand grid)
            Dim appendRequest As New AppendCellsRequest
            appendRequest.SheetId = SheetID
            appendRequest.Rows = rowdataList.ToArray           
            appendRequest.Fields = "*"
            Dim request As New Request
            request.AppendCells = appendRequest

            'Execute the request
            Dim bRequest As New BatchUpdateSpreadsheetRequest
            bRequest.Requests = {request}
            Dim bResponse As BatchUpdateSpreadsheetResponse = Service.Spreadsheets.BatchUpdate(bRequest, DataBaseName).Execute()

            'Now update the newly added rows with data
            Dim index As Integer = GetRowCount() - Values.Length + 2 'GetRowCount() calls the service to get sheet metadata and returns the rows of data (excluding the headers)
            Dim vals As New ValueRange
            vals.Values = Values
            Dim urequest As SpreadsheetsResource.ValuesResource.UpdateRequest =
                Service.Spreadsheets.Values.Update(vals, DataBaseName, Name & "!A" & index)
            urequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED
            Dim response As UpdateValuesResponse = urequest.Execute
            Return response.UpdatedRows = Values.Length

        Catch ex As Exception
            Trace.WriteLine(Now.ToLongTimeString & ":" & ex.Message)
            Return False
        End Try

    End Function

In short, I am calling AppendCells to expand the size of the 'grid' and then am updating values in the blank rows thus created. If you try to update values for the new row, the server returns a 'Service not available' error. I found the issue when I experimented with updating values (values.batchUpdate) in existing rows and adding new rows in the same request. The error message in that case spoke of 'beyond the grid' updates.

Thanks for sharing the solution, but I think that the most important part is inside GetRowCount(), which you didn't include

Sam Berlin · Accepted Answer · 2016-06-07 18:56:09Z

1

There is no API for appending rows via the spreadsheets.values collection (the one that supports A1 notation & ValueInputOption). We're tracking that as a feature request internally and will likely add support. In the meantime, the only way to append rows is via the batchUpdate method (though, if you don't need the extras the CellData object offers such as formatting, then it is tedious to do it that way, as you say).

answered Jun 7, 2016 at 18:56

Sam Berlin

3,80316 silver badges25 bronze badges

Collectives™ on Stack Overflow

Google Sheets API V4 Add Row .NET

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related