Google Sheets API V4添加行.NET [英] Google Sheets API V4 Add Row .NET

问题描述

Google文档页面上显示

The Google documentation page says

Sheets API v4确实提供了一个AppendCells请求，该请求可与电子表格.batchUpdate方法一起使用，以将一行数据附加到工作表(并根据需要同时更新单元格属性和格式).

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).

即使添加RowData有效负载很繁琐，这也确实适用于添加新行.但是，这不允许设置ValueInputOption.

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也说

但是，通常通常更简单的方法是简单地确定要添加的行的A1表示法，然后发出电子表格(sheets).values.update请求以覆盖该行.在这种情况下，指定行中的任何数据都将被覆盖.

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.

现在这适用于更新现有行上的数据-包括ValueInputOption.但是，当我使用它来追加新行时(即提供下一行的范围)，服务器将返回503错误.我一定有一个把戏吗?

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?

推荐答案

我应该已经按照评论者的建议在问题中发布了代码.但是，我将其发布为此答案的一部分-这显然是次优的解决方案，但符合我的目的.

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.

这是我最后得到的代码(省略了OAuth和服务实例化)

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

简而言之，我正在调用AppendCells来扩展网格"的大小，然后更新由此创建的空白行中的值.如果您尝试更新新行的值，则服务器将返回服务不可用"错误.当我尝试更新现有行中的值(values.batchUpdate)并在同一请求中添加新行时，我发现了问题.在这种情况下，错误消息提到超出网格"更新.

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.

这篇关于Google Sheets API V4添加行.NET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！