Tag Archives: google spreadsheets

Google Spreadsheets API Row Counts Discrepancy

Background

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.

The Problem

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");

The Fix

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.

Google Apps Spreadsheets and Magento: (Nearly) Perfect Together

A quick dash this evening on a pretty amazing – well, discovery isn’t quite the right word: I knew Google Apps Spreadsheets were supposed to work this way. My superstitious code mind just didn’t think it really would. But guess what?

It did and does.

I’d written a program to export product from our warehousing system into a cvs file suitable for consumption by Magento. Problem was, there were lots of little twists and turns that needed every so subtle adjustment. But sheesh, I couldn’t just send Jason off on 2,925 items. Or really even split them very effectively between me, Dave and Jason, because scores of items were OK as-is. Plus we had questions amongst ourselves about what was good and what wasn’t.

Enter – or rather upload – to Google Apps spreadsheets. Yup, 3 people editing the same sheet at the same time, real time, with a handy chat feature (and GoogleTalk as well, if only Jason and Dave also had a microphone and webcam!).

Whipped through everything in no time flat.

OK, I know it’s supposed to work that way. But still: what a tremendous relief to have other people see and understand that you don’t need to send spreadsheets flying through the Internet!

Oh, and it’s nice you can all work on it at the same time, too.