I am integrating a Google Spreadsheet with an online equipment rental package called “Rentventory.” The spreadsheet was already in use by the time I got to the project, and it proved handy. There aren’t a ton of items to be managed, a hundred or so, and the users have done all sorts of nice things to make the important bits of information stand out.
The biggest problem, however, is that it’s way tricky to reserve equipment and make sure you don’t collide with someone else. That’s where the rentventory package comes in. However, I really don’t want to have to manually enter – ever – my rental inventory, especially when it’s small, manageable, and very easy to manipulate with a Google Spreadsheet. Enter the Google API.
Java seemed a natural choice since it’s a language I can use across all my platforms: Business Basic (in the form of BBj), Web-based, and MySQL. Oh, and credit card authorizations. Can’t forget that.
Long story short for this post is that the Google Spreadsheet API gave me a fit. Using the WorksheetEntry class, I was told my sheet had 173 rows. However, when I went to retrieve those rows via a ListFeed, I only got 2 back! First, here’s the code that got the worksheet information. (j is just me looping through the sheets; ignore for now).
WorksheetEntry worksheet = worksheets.get(j); String title = worksheet.getTitle().getPlainText(); int rowCount = worksheet.getRowCount(); int colCount = worksheet.getColCount(); System.out.println("\t" + title + "- rows:" + rowCount + " cols: " + colCount);
Result: 173 rows. Perfect. Now, I want to parse through the rows. But here, rows.size() came back as 2!
URL listFeedUrl = worksheet.getListFeedUrl(); ListFeed rowFeed = myService.getFeed(listFeedUrl, ListFeed.class); List<ListEntry>rows = rowFeed.getEntries(); System.out.println("Rows: "+rows.size()+"\n");
Guess what? The problem was empty spreadsheet rows. A-yep. Nothing was entered in row 3 of the sheet, so the ListFeed was just done. Poof. I put in “__” (2 underscores) in each of the empty lines, and presto! I got through all the rows.