Search
Online Courses
Pocket Guides
Last Update: 05.06.2018. By Jens in Spring Boot | Kotlin
In this tutorial, we are going to build a simple web application using Spring Boot which will render the content of a Google Sheet spreadsheet in a web page and also display them on a Google Map.
The tutorial initially ran on my Learnletter in May 2018.
New to Spring Boot? Check out my pocket guide for getting started.
Create a new Spring Boot project either using start.spring.io or a wizard in the IDE of your choice. Include web, thymeleaf and the devtools. Choose whatever build system and language you prefer.
I used Gradle and coded in Kotlin.
My version can be found on GitHub: https://github.com/azarai/sheets2page-tutorial.
The first thing we need for accessing any Google APIs is a Google account and an API project setup. I assume you already got Google, so we stick to the API stuff.
There are three ways to authenticate with the Google APIs:
Next step, is to set up a Google API project. So, let’s do it.
Now, we are ready to use the API. Gooogle provides a Java SDK for the Sheets API and we can use it by simply adding these dependencies:
Gradle style:
compile 'com.google.api-client:google-api-client:1.23.0'
compile 'com.google.oauth-client:google-oauth-client-jetty:1.23.0'
compile 'com.google.apis:google-api-services-sheets:v4-rev516-1.23.0'
I keep the sample application simple. So it is one Controller ( PageController) and two data classes (Site and Item). And a template, of course.
Site contains a name and a slogan. Item is a representation of a data row; the actual things we maintain on our list. The sample has a collection of monsters, with a name, short description, an image and a location. You can create your own sheet or just copy mine.
The Sheet must be set to public (Share button top right), so anybody knowing the URL can read it.
In the PageController create a get method and accept the sheets id as a path variable and also add ModelAndView as a method parameter and return a ModelAndView.
@GetMapping("/site/{sheetId}")
fun getSite(@PathVariable("sheetId") sheetId: String?, model: ModelAndView): ModelAndView {
For accessing the sheets API, we need to obtain an instance of Sheets
val sheetsService = Sheets.Builder(
HTTP_TRANSPORT,
JSON_FACTORY,
null
).setSheetsRequestInitializer(SheetsRequestInitializer(googleKey)).build()
HTTP_TRANSPORT and the JSON_FACTORY are both part of the google API and handle the actual connection/transformation. We can simply get one by calling some factory methods like:
GoogleNetHttpTransport.newTrustedTransport()
JacksonFactory.getDefaultInstance()
The third method parameter is needed for Oauth2 handling, however, we do not use it, so we just pass null. To set our API key, we set a SheetsRequestInitializer, which will automatically add the key to each request. googleKey is a field member and connected with @Value to a property in my application.properties file. Getting it from the environment is an alternative.
Once we got the Sheets instance, we can make requests to the API.
val response: BatchGetValuesResponse = sheetsService.spreadsheets().values()
.batchGet(sheetId).setRanges(listOf("items!A3:Z", "siteinfo!A3:Z"))
.execute();
val values = response.getValueRanges();
We do so by building the request in a fluent like API.
The sheetId, who guessed it, is the id of the spreadsheet. It’s the part between https://docs.google.com/spreadsheets/d/ and the /edit part of the Google sheets document URL. As a result, we’ll get a response object and can use getValueRanges to access the actual values.
If we are only querying a single table aka sheet inside our spreadsheet, we could use the get method instead of the batchGet.
val response: BatchGetValuesResponse = sheetsService.spreadsheets().values()
.batchGet(sheetId).setRanges(listOf("items!A3:Z", "siteinfo!A3:Z"))
.execute();
val values = response.getValueRanges();
What we basically do here is retrieving the sheets named items and siteinfo and on each sheet we are interested in the columns A to Z and all rows starting at row number 3.
As a response, we get a BatchGetValuesResponse object. It merely contains a list of ValueRange objects and inside those the rows and columns as nested lists. It is not the most elegant thing, but still straightforward.
Let’s parse the items.
First, we need to get our sheet from the ValueRange list. The ValueRange object includes the range it belongs to. We get it via getRange(). I use the sheet name and check if the range starts with it, if yes, it is the right sheet. In case, we had overlapping prefix names, we should include ! (it is the delimiter between Sheetname and row-column range.
private fun getSheet(name: String, values: List<ValueRange>): ValueRange? {
if (!values.isEmpty()) {
for (vr in values) {
//println(vr.getRange())
if (vr.getRange().startsWith(name)) {
return vr
}
}
}
return null
}
Next, I put the items parsing in its own method like:
private fun buildItems(values: List<ValueRange>): List<Item> {
val result: MutableList<Item> = mutableListOf()
val vr = getSheet("items", values)
for (row in vr!!.getValues()) {
result.add(Item(
row.getOrNull(0) as String?,
row.getOrNull(1) as String?,
row.getOrNull(3) as String?,
row.getOrNull(5) as String?,
row.getOrNull(6) as String?
))
}
return result
}
We get the values of the items sheet and go through it row by row. For each row, we create an instance of Item by using the column values. Selected by the index. getOrNull is a Kotlin method and either returns the value at this index or null if the index does not exist. For example, we got three columns and 2 rows. The first row has all columns filled, the second only the first one. Google API would now return the first row with 3 values, for each column one, but for the second just a single one. The others are missing. If we blindly access index 3, we would get a ArrayIndexOutOfBoundException while processing row two. getOrNull saves us the headache of manually checking the array size.
For such a simple spreadsheet, it is valid to reference the columns by the index. If it is larger, I’d either move the index into constants or depending on the type of data, create a mapper for the object, which maps using column headers and objects field names. However, it pretty much depends on the project, how often new spreadsheets are connected and if they follow a common principle. Maybe, there is already a java solution for that. I did not check it.
As with any spreadsheet parsing I encountered in the dev dungeons, a user can break a lot if they suddenly add a new column at a random place (not at the end) or change cell types or whatever. So, being fault tolerance is excellent:-)
We’ll parse the siteinfo sheet a bit differently. The first column contains the name of the variable (at index 0) and the second column (at index 1) provides the actual value.
private fun buildSiteMeta(values: List<ValueRange>): Site {
var sitename: String = ""
var slogan: String = ""
var owner: String =""
val vr = getSheet("siteinfo", values)
for (row in vr!!.getValues()) {
val fieldValue: String = row.get(0).toString()
if ("name" == fieldValue) {
sitename = row.get(1) as String
}
if ("slogan" == fieldValue) {
slogan = row.get(1) as String
}
if ("owner" == fieldValue) {
owner = row.get(1) as String
}
}
return Site(sitename, slogan, owner)
}
Instead of simple if statements, we could also use when the Kotlin version of switch or even if else. However, I did not at the time of writing the tutorial on the Learnletter.
What’s left is sticking it together in a single Controller method. Mine looks like:
@GetMapping("/site/{sheetId}")
fun getSite(@PathVariable("sheetId") sheetId: String?, model: ModelAndView): ModelAndView {
val sheetsService = Sheets.Builder(
HTTP_TRANSPORT,
JSON_FACTORY,
null
).setSheetsRequestInitializer(SheetsRequestInitializer(googleKey)).build()
val response: BatchGetValuesResponse = sheetsService.spreadsheets().values()
.batchGet(sheetId).setRanges(listOf("items!A3:Z", "siteinfo!A3:Z"))
.execute();
val values = response.getValueRanges();
model.addObject("items", buildItems(values))
model.addObject("site", buildSiteMeta(values))
model.addObject("mapsKey", googleKey)
model.setViewName("site")
return model
}
We store the items, site and our api key for Google Maps for the view and forward it to the view named site.
We use Thymeleaf for the view, so by default, we create our view as site.html under the templates folder.
You can find my version on GitHub. I omit it here because it adds too much noise. It is straightforward and uses Bulma as the CSS framework.
We’ll cover here the Javascript part for rendering the map. It’s showing a neat feature of Thymeleaf too.
<script th:inline="javascript">
function initMap() {
var map = new google.maps.Map(document.getElementById('map'), {
zoom: 4
});
var data = /*[[${items}]]*/;
var bounds = new google.maps.LatLngBounds();
var infowindow = new google.maps.InfoWindow();
for(var i=0; i< data.length;i++) {
if(data[i].lat !== null && data[i].lat !== "") {
var marker = new google.maps.Marker({
position: {lat: parseFloat(data[i].lat), lng: parseFloat(data[i].lng)},
map: map,
title: data[i].name
});
var infoContent = '<div id="content">'+
'<h1 id="firstHeading" class="firstHeading">' + data[i].name + '</h1>'+
'<p><img src="' + data[i].image + '"><br>' + data[i].description + '</p>'+
'</div>';
google.maps.event.addListener(marker,'click', (function(marker,content){
return function() {
infowindow.setContent(content);
infowindow.open(map,marker);
};
})(marker,infoContent));
var loc = new google.maps.LatLng(marker.position.lat(), marker.position.lng());
bounds.extend(loc);
}
}
map.fitBounds(bounds);
map.panToBounds(bounds);
}
</script>
<script async defer
th:src="|https://maps.googleapis.com/maps/api/js?key=${mapsKey}&callback=initMap|">
</script>
We start at the back of the code block. The last script tag loads the google maps, here we pass our API key along and reference a callback named initMap. This one is called by Google Maps API when the script is loaded.
The initMap function is declared in the script tag above. Mostly, we iterate over all of our items, check if they have geo coordinates and if so, we add a marker to the map. We also set up an info window, which will be shown once we click on a marker and last, but not least, we calc the bounds for setting the viewport of the map.
There are two interesting lines though.
First, the script tag uses a Thymeleaf:
html
<script th:inline="javascript">
This enables Thymeleaf syntax parsing on the script block in the form of inline scripting. This allows us to create javascript variables from Thymeleaf variables like:
var data = /*[[${items}]]*/;
var data = is Javascript and the part thereafter is Thymeleaf. The double brackets [[ syntax will output the value of the variable items as escaped Javascript. So, if you run the app the part in the generated page will look like:
var data = [{"name":"Monster","shortDescription":"cute monster","description":"","image":"https:\/\/d30y9cdsu7xlg0.cloudfront.net\/png\/824399-200.png","location":"Zoo Frankfurt, Germany","50.1163393","lng":"8.699472"}];
Cool, isn’t it?
We could even use the th prefix here and iterate of the list in Java or using conditions. The only downside is, it is getting hard to read. Things that look like comments are actually Thymeleaf and being executed. But, as always, every tech has its pros and cons.
We do not always need a database. Sometimes a little spreadsheet is all we need. Accessing Google Sheet is , and with Spring Boot it is a breeze to code a simple tutorial like this.
Have fun and show what you build.