exception during adding a row to google spreadsheet

4
votes

I have followed the entire API for document list (for creating spreadhseet) and spreadsheet (for creating worksheet and adding rows). However, I am able to add a worksheet to a spreadhseet after its creation, but when i try to add the row, i get the error excpetion: Execution of request failed: https://spreadsheets.google.com/feeds/list/tj0pIc6qpEB2LtZY9mwfT-A/od6/private/full

I have mentioned all the OAuth scopes and required credentials, but unable to solve this exception. The rest of things are working fine like creation of google spreadsheet and adding a worksheet too. I am just copy pasting 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's 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's 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);
4
c#google-drive-apigoogle-docs-apigoogle-spreadsheet-apigoogle-cloud-storage
If you drill down in the exception details you'll find the complete error message, telling you why the request failed. As an alternative, try capturing the HTTP traffic with Fiddler and check the details of the response – Claudio Cherubino
In the inner exception dropdown,it says {"The remote server returned an error: (400) Bad Request."} as response and StatusCode as System.Net.HttpStatusCode.BadRequest. Is this google server error or i am missing something? – Vacca
I even tried with a row delete and row update after manually writing on spreadsheets, but only Add row feature on spreadsheet API is not working, I dont think their seems to be anything wrong in my code becuase row update,delete is working fine. response string is : We're sorry, a server error occurred. Please wait a bit and try reloading your spreadsheet. – – Vacca
May I am have any support from google admins who are part of this api, is this API a commercial/beta one or in a alpha state? Even in google spreadsheet api forum link this problem is posted by other users but we have no support for problems with this api. – Vacca
did you get any solution? – PreguntonCojoneroCabrón

4 Answers

9
votes

I also struggled with this and I've found the following:

Each column has a name that is specified in the first row. In Google API example the first column is 'firstname' and it is indicated in cell A1 of an existing worksheet.

When you're adding rows (like in the example code you pasted above) the 'LocalName' property must exactly match. Also the API lower-cases and removes spaces from the column name (God knows why??), so if you define your column name as 'Name' the API will lower case it to 'name' and try to match to it. So when you add a row you need to define in your code the column name in lower-case, no spaces.

Just to be on the safe side create a worksheet with one column and set the first row cell to be 'name'. Now run the example code with your authentication and try to add a row with only one entry, like so:

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

And this will not work

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

You need to create/ensure the header row for the worksheet first:

        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);
1
votes

As reported in the documentation, the list feed makes some assumptions about how the data is laid out in the spreadsheet. In particular, the list feed treats the first row of the worksheet 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 row with four values: firstname, lastname, age and height. If the first row of the worksheet doesn't define those headers, your request will be invalid and your code throw an exception.

Please make sure that your code matches the structure of the spreadsheet or use the Cell feed if you need to dynamically determine the contents of the header row.

1
votes

After banging my head on this for hours, I discovered that the Google API appears to have a bug. If there are blank rows at the bottom of the worksheet, adding a row works fine, and the API returns an appropriate response. If, however, there are no blank rows at the bottom of the worksheet, your data is duly and correctly added to a new row at the bottom of the worksheet, BUT Google nonetheless returns an HTTP 400 error containing the text "Blank rows cannot be written."
You can get around this bug by catching the error, checking that the error is of the "Blank rows" variety and, if so, using a listfeed query to retrieve the row you just added. If that query is successful, then the new row was added correctly and the 400 error can be ignored.
I'd give you my code, but I'm working in PHP, so probably not helpful.