Advanced Usage

Ok, now let’s try to do something more advanced and fun, let’s do some magic :)

We start with creating classes Product and ProductManager.

No magic yet — one thing to notice ProductManager has init method that fetches spreadsheet and gets products worksheet.

The init method has to be completed at least once before any operations with the worksheet. I’ll add invocation of this method to all of the ProductManager methods.

Now, let’s add getAll method that returns all products in the worksheet.

Ok, now it’s something — we got a list of all products, but is it magical yet?

I’d say no… It’s hard to read and not easy to extend. It uses fixed indexes of data and if we were to add a new column in our table, it’d be a nightmare to correct all of the methods that we would have written in our ProductManager.

So, let’s try to do something magical this time :)

Let’s add method toGsheets and factory fromGsheets to our class Product.

Now that we have them we can use mapping that built in gsheets to make things better. Let’s refactor getAll method.

Now it’s magical, isn’t it?

What happens here - allRows method fetches all rows (starting from row #2 by default, but it can be changed if needed) and maps them to row #1 (by default, but it can be changed if needed). As a result we get List of Map<String, String> that represent products, then we map them to products using fromGsheets factory.

If we were to change our table, we would only have to update our class Product.

Moving forward, let’s add getById method.

Looks cool, huh?

What happens here — we are fetching the row by its key (the key for the row is the value in column A) and then we map it to row #1, as a result we get Map representation of a Product, then we create product from this map.

If there’s no such id in the products table, we get null. Awesome!

Now, let’s add methods to get/update price of a product.

No explanation needed, I guess? It’s clear, but maybe one thing can be confusing here — what is eager argument and why it’s false in this case. The eager tells whether the keys should be automatically added to the table, if any of them is absent (by default eager is true). In this case we don’t want to insert id if it’s missing or in other words set the price for the product that doesn’t exist.

What’s next? Now we need methods that we can use to insert new products or update existing ones. As a matter of fact we can add one method that will cover both of this cases. Here is the snippet.

What happens here. In the insert method we turn product into map and insert values of this map into row with key equal to product’s id. If there is no row with such key it will be added (this is default behavior and can be overridden by passing eager:false). The order of values in the inserting map doesn’t matter cause gsheets library will sort them according to the order of the keys in the first row.

You’ve probably noticed that i pass appendMissing:true, it doesn’t really change anything in this particular case, but if inserting map contains entries with keys that are absent in the first row, it will add them automatically into the working table (by default appendMissing is false).

Now, there’s one more feature that we should have in our ProductManager, it’s deleting products.

Nice and simple!

That’s it for this tutorial, even though there are many other methods and features of gsheets library, for example if you try to insert values that are out of the current worksheet bounds, it won’t throw the exception, it will simply expand worksheet’s bounds prior to insertion. You can read about it and other features in the documentation.

Thanks! Over and out :)