Integrate Google Sheets into Your Scala Application in 4 Steps
By Lucas Gray on May 18, 2015
Getting started integrating Google Sheets into your Scala application can seem daunting. From picking the right dependencies
for your use case, to enabling OAuth 2.0, to dealing with the crufty Java APIs, there are plenty of rabbit holes and gotchas.
Fortunately, we’ve handled them so you don’t have to!
This is for creating and deleting Sheets. You may be able to omit if using existing sheets.
Are You Using Jetty?
Chances are, you’re using Jetty server to serve up your favorite framework, or you’re using another set of packages like
hadoop that also bundle jetty.
The GData API (com.google.gdata:core) brings in really old (6.1.26) jetty libraries
from when it was under the org.mortbay namespace. This means Ivy won’t notice the conflict if you’re using the new
org.eclipse.jetty libs too.
Exclude these old libraries to avoid jetty transitive dependency mismatches:
Now that you have the dependencies loaded in your project, it’s time to understand Google OAuth 2.0.
2. Google OAuth 2.0
Google requires a one-time OAuth handshake from your user if you intend to act on the user’s behalf. There
are a few good diagrams on Google’s documentation.
Once the user gives their consent, the app obtains a one-time token it can trade for an access token and refresh token.
The access token and refresh token are used after that to access the API as the user.
We downloaded our google secrets file and placed it inside our source directory. It looked like this:
Next we can read the Json object into a case class using Jackson.
Making the One-Time Authorization Calls
For the OAuth calls, we elected to call the proper endpoints directly using the great
We open the page this code creates in a new tab for the user:
Once the user obtains the token and gives it to the application, we trade it in for the access token and refresh token.
The tricky bit about Google OAuth is that the token periodically expires. If the token is expired, the application
must provide the user’s refresh token to obtain a new access token. Subsequent calls will use the new access token.
Now we have a newly refreshed GoogleCredential to store off for the user.
3. Create Your First Worksheet
The Google Drive API (google-api-services-drive) allows for creation and deletion of a user’s Sheets. This is Google’s
newer API but unfortunately they have not migrated over any of the calls for manipulating the Sheets.
Creating a Sheet with the Google Drive API is fairly straightforward. We elected to use the Java API instead of wire
protocol because the Java API seemed modern and easy to use.
4. Write Sample Data
Writing data using the outdata GData API involves a few steps. Most API calls operate on a WorksheetEntry, so in order
to do much we will need to get a SpreadsheetEntry and find the WorksheetEntry we want. After that, we can
append/overwrite cells, resize or rename the sheet, bulk load data, and more. We used the Java API for GData
because the wire protocol was XML and seemed pretty complicated.
Before we can get the WorksheetEntry, we need to wire up the GData API.
Once we have the SpreadsheetService, we can get the WorksheetEntry by name. The Spreadsheet ID can be found sandwiched
in the middle of the URL for the sheet. The best way to explain is just to show an example –- in the URL
the Sheet ID is 1DxY-rVfijAAw-nnT3_4rDKziBqzNzuGtYL-jsfVZ5pc.
Asking for the Spreadsheet by ID and then getting the worksheet we want isn’t too bad using the GData API.
Finally, we are at the point we can manipulate the Worksheet! Here is a simple example of altering a cell.
Congratulations! You’ve successfully integrated Google Sheets! Now you can look into truncating rows, updates, and
bulk loading cells using the WorksheetEntry as a springboard. Check out the examples at the bottom of Google’s
for more details.
You can view and run all examples in this blog post on github here.
Share this post:
Still Writing SQL in a Terminal?
Beekeeper is a free and full featured SQL IDE with query autocomplete, error detection, dataset previews, and result visualization. We support SparkSQL, Hive, Postgres and more!