Exception when adding row to google table

Question

I followed the entire API for a list of documents (to create a distribution) and a spreadsheet (to create a worksheet and add rows). However, I can add the worksheet to the spread-descendants after creating it, but when I try to add a line, I get an excpetion error: The request failed: https://spreadsheets.google.com/feeds/list/tj0pIc6qpEB2LtZY9mwfT- A / od6 / private / full

I mentioned all OAuth areas and the necessary credentials, but could not resolve this exception. The rest of the things work just fine, like creating a google spreadsheet and adding a worksheet. I just copy the google code.

// setUp the confirguration for OAuth 2.0 string clientID = "********.apps.googleusercontent.com"; string clientSecret = "*******************"; string scope = "https://docs.google.com/feeds/ https://docs.googleusercontent.com/ https://spreadsheets.google.com/feeds/"; string redirectURI = "urn:***:wg:oauth:2.0:oob"; // setup the OAuth 2.0 object OAuth2Parameters parameters = new OAuth2Parameters(); // to hold all the parameters parameters.ClientId = clientID; // setup the clientID parameters.ClientSecret = clientSecret; // setup the clientSecret parameters.RedirectUri=redirectURI; // setup the redirectURI //setup the authurization URL parameters.Scope = scope; // set the scope string authorizationURL = OAuthUtil.CreateOAuth2AuthorizationUrl(parameters); Console.WriteLine(authorizationURL); Console.WriteLine("Please visit the URL above to authorize your OAuth " + "request token. Once that is complete, type in your access code to " + "continue..."); parameters.AccessCode = Console.ReadLine(); // get the access token OAuthUtil.GetAccessToken(parameters); string accessToken = parameters.AccessToken; Console.WriteLine("Cosole Access token " + accessToken); //make Auth Request to Google GOAuth2RequestFactory factory = new GOAuth2RequestFactory(null, "SampleSpreadSheetApp-V1", parameters); // DocumentsService service = new DocumentsService("SampleSpreadSheetApp-V1"); service.RequestFactory = factory; //--------------------------------------------------------------------- GOAuth2RequestFactory requestFactory = new GOAuth2RequestFactory(null, "MySpreadsheetIntegration-v1", parameters); SpreadsheetsService service = new SpreadsheetsService("MySpreadsheetIntegration-v1"); service.RequestFactory = requestFactory; SpreadsheetQuery query = new SpreadsheetQuery(); SpreadsheetFeed feed = service.Query(query); if (feed.Entries.Count == 0) { Console.WriteLine("no spreadsheets present here"); } // TODO: Choose a spreadsheet more intelligently based on your // app needs. SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0]; Console.WriteLine(spreadsheet.Title.Text); // Get the first worksheet of the first spreadsheet. // TODO: Choose a worksheet more intelligently based on your // app needs. WorksheetFeed wsFeed = spreadsheet.Worksheets; WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0]; if (wsFeed.Entries.Count == 0) { Console.WriteLine("no worksheets present here"); } // Define the URL to request the list feed of the worksheet. AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null); // Fetch the list feed of the worksheet. ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString()); ListFeed listFeed = service.Query(listQuery); // Create a local representation of the new row. ListEntry row = new ListEntry(); row.Elements.Add(new ListEntry.Custom() { LocalName = "firstname", Value = "Joe" }); row.Elements.Add(new ListEntry.Custom() { LocalName = "lastname", Value = "Smith" }); row.Elements.Add(new ListEntry.Custom() { LocalName = "age", Value = "26" }); row.Elements.Add(new ListEntry.Custom() { LocalName = "height", Value = "176" }); // Send the new row to the API for insertion. service.Insert(listFeed, row); 

Answer 1

I also struggled with this, and I found the following:

Each column has a name indicated on the first row. In the Google API example, the first column is the "first name" and is listed in cell A1 of the existing sheet.

When you add lines (for example, in the sample code that you pasted above), the "LocalName" property must exactly match. In addition, spaces in the column name are removed in the lower case of the API (God knows why?), Therefore, if you define the name of your column as “Name”, the API will reduce the case to “name” and try to match it. Therefore, when you add a row, you need to define a lowercase column name in your code, without spaces.

To be safe, create a worksheet with one column and set the cell of the first row as "name". Now run the sample code with your authentication and try adding a line with only one entry, for example:

 ListEntry row = new ListEntry(); row.Elements.Add(new ListEntry.Custom() { LocalName = "name", Value = "John" }); 

And it won't work

 row.Elements.Add(new ListEntry.Custom() { LocalName = "Name", Value = "John" }); 

Answer 2

You need to first create / provide a title bar for the sheet:

  CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink); CellFeed cellFeed = service.Query(cellQuery); CellEntry cellEntry = new CellEntry(1, 1, "firstname"); cellFeed.Insert(cellEntry); cellEntry = new CellEntry(1, 2, "lastname"); cellFeed.Insert(cellEntry); cellEntry = new CellEntry(1, 3, "age"); cellFeed.Insert(cellEntry); cellEntry = new CellEntry(1, 4, "height"); cellFeed.Insert(cellEntry); 

Answer 3

As stated in the documentation, the list feed contains some suggestions on how the data is placed in a spreadsheet. In particular, the list feed treats the first row of the sheet as a header row:

https://developers.google.com/google-apps/spreadsheets/#working_with_list-based_feeds

In your code you are trying to add a string with four values: first name, last name, age and height. If the first line of the worksheet does not define these headers, your request will be invalid and your code will throw an exception.

Please make sure that your code matches the structure of the spreadsheet or uses a cellular channel if you need to dynamically determine the contents of the title bar.

Answer 4

After banging my head about it for hours, I found that the Google API had an error. If there are blank lines at the bottom of the sheet, adding a line works fine, and the API returns an appropriate response. If, however, there are no blank lines at the bottom of the sheet, your data is appropriately and correctly added to the new line at the bottom of the worksheet, BUT Google, however, returns an HTTP 400 error containing the text "Blank lines cannot be written. "
You can get around this error by catching the error, noting that the error is of the "Empty Lines" type, and if so, use the listfeed query to retrieve the row you just added. If this request is successful, a new line has been added correctly, and error 400 can be ignored.
I would give you my code, but I work in PHP, so it is probably not useful.