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!

Updated June 22 2015

Example code repo is available on github!

The 4 Steps

  1. Add the dependencies to your project
  2. Set up Google OAuth 2.0 integration
  3. Create your first worksheet
  4. Write “Hello World” to a cell on that sheet

1. Dependencies

Depending on your use case, you may need to include two or three dependencies.

  • Required: "com.google.gdata" % "core" % "1.47.1"
    • This is the core Sheet manipulation library.
  • Required: "com.google.apis" % "google-api-services-oauth2" % "v2-rev83-1.19.1"
    • This is for authenticating and authorizing Sheets requests.
  • Optional: "com.google.apis" % "google-api-services-drive" % "v2-rev160-1.19.1"
    • 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:

libraryDependencies ++= Seq(
  //your other project dependencies..

  "com.google.gdata" % "core" % "1.47.1",
  "com.google.apis" % "google-api-services-oauth2" % "v2-rev83-1.19.1",
  "com.google.apis" % "google-api-services-drive" % "v2-rev160-1.19.1"
).map(
  _.exclude("org.mortbay.jetty", "jetty") 
   .exclude("org.mortbay.jetty", "servlet-api") 
   .exclude("org.mortbay.jetty", "jetty-util")
)   

Now that you have the dependencies loaded in your project, it’s time to understand Google OAuth 2.0.

Google OAuth

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.

Setting Up Configs

For our use case, we set up OAuth 2.0 for Installed Applications. Your use case may vary if you are developing a mobile or server application.

We downloaded our google secrets file and placed it inside our source directory. It looked like this:

{
    "clientId": "client-id",
    "clientSecret": "client-secret",
    "redirectUrl": "urn:ietf:wg:oauth:2.0:oob",
    "scopes": [
      "https://www.googleapis.com/auth/drive",
      "https://spreadsheets.google.com/feeds"
    ]
}

Next we can read the Json object into a case class using Jackson.

object Json {
  def mapper = {
    val m = new ObjectMapper() with ScalaObjectMapper
    m.registerModule(DefaultScalaModule)
    m
  }

  def fromJson[T](url: URL)(implicit m: Manifest[T]): T = {
    mapper.readValue[T](url)
  }
}

case class GoogleApp(
  clientId: String,
  clientSecret: String,
  redirectUrl: String,
  scopes: List[String]
)
  
lazy val loadApp = Json.fromJson[GoogleApp](this.getClass.getResource("google_secrets.json"))

Making the One-Time Authorization Calls

For the OAuth calls, we elected to call the proper endpoints directly using the great scala-uri library.

We open the page this code creates in a new tab for the user:

val authUrlBase = "https://accounts.google.com/o/oauth2/auth"

def userUri(user: User): String = {
  val creds = loadApp //see above
  val url = authUrlBase ?
    ("response_type" -> "code") &
    ("client_id"-> creds.clientId) &
    ("redirect_uri" -> creds.redirectUrl) &
    ("scope" -> creds.scopes.mkString(" ")) &
    ("state" -> "beekeeper!") &
    ("login_hint" -> user.email) &
    ("include_granted_scopes" -> "true")
  url.toString
}

Once the user obtains the token and gives it to the application, we trade it in for the access token and refresh token.

val tokenUrlBase = "https://www.googleapis.com/oauth2/v3/token"

case class GoogleCredential(
  id: Option[Long],
  userId: Long,
  accessToken: String,
  refreshToken: String
)

def buildCredential(code: String, owner: User): GoogleCredential = {
  val creds = loadApp //see above
  val response: HttpResponse[String] = Http(tokenUrlBase).postForm(Seq(
    "code" -> code,
    "client_id" -> creds.clientId,
    "client_secret" -> creds.clientSecret,
    "redirect_uri" -> creds.redirectUrl,
    "grant_type" -> "authorization_code",
    "access_type" -> "offline"
  )).asString

  val tokenData = response.code match {
    case 200 => Json.fromJson[TokenResponse](response.body, true)
    case _ => throw new Exception("OAuth Failed with code %d: %s".format(response.code, response.body))
  }

}

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.

val tokenUrlBase = "https://www.googleapis.com/oauth2/v3/token"

case class RefreshTokenResponse(
  accessToken: String,
  expiresIn: Int,
  tokenType: String
)

def refreshCredential(googleCredential: GoogleCredential): GoogleCredential = {
  val creds = loadApp //see above
  val response: HttpResponse[String] = Http(tokenUrlBase).postForm(Seq(
    "refresh_token" -> googleCredential.refreshToken,
    "client_id" -> creds.clientId,
    "client_secret" -> creds.clientSecret,
    "grant_type" -> "refresh_token"
  )).asString

  val tokenData = response.code match {
    case 200 => Json.fromJson[RefreshTokenResponse](response.body, true)
    case _ => throw new Exception("OAuth Failed with code %d: %s".format(response.code, response.body))
  }

  googleCredential.copy(accessToken = tokenData.accessToken)
}

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.

lazy val app = Json.fromJson[GoogleApp](this.getClass.getResource(file))

case class DriveResource(
  link: String,
  title: String,
  thumbnail: String
)

def googleDriveApiForToken(accessToken: String): Drive = {

  val clientId = app.clientId
  val clientSecret = app.clientSecret
  val httpTransport = new NetHttpTransport
  val jsonFactory = new JacksonFactory

  //Build the Google credentials and make the Drive ready to interact
  val credential = new GoogleCredential.Builder()
    .setJsonFactory(jsonFactory)
    .setTransport(httpTransport)
    .setClientSecrets(clientId, clientSecret)
    .build()
  credential.setAccessToken(accessToken)
  //Create a new authorized API client
  new Drive.Builder(httpTransport, jsonFactory, credential).build()
}

def createSpreadsheetOnDrive(accessToken: String, nameOfFile: String) = {

  val service = googleDriveApiForToken(accessToken)
  val body = new File
  body.setMimeType("application/vnd.google-apps.spreadsheet")
  val docType = "spreadsheet"
  body.setTitle(nameOfFile)
  val file = service.files.insert(body).execute
  DriveResource(file.getAlternateLink, file.getTitle(), file.getThumbnailLink())
}

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.

lazy val app = Json.fromJson[GoogleApp](this.getClass.getResource(file))

def gDataApiForToken(accessToken: String) = {
  val service = new SpreadsheetService("app-name")

  val clientId = app.clientId
  val clientSecret = app.clientSecret
  val httpTransport = new NetHttpTransport
  val jsonFactory = new JacksonFactory

  val credential = new GoogleCredential.Builder()
    .setJsonFactory(jsonFactory)
    .setTransport(httpTransport)
    .setClientSecrets(clientId, clientSecret)
    .build()
  credential.setAccessToken(accessToken)

  service.setHeader("Authorization", "Bearer " + accessToken)

  service.setOAuth2Credentials(credential)

  service
}

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

https://docs.google.com/a/beekeeperdata.com/spreadsheets/d/1DxY-rVfijAAw-nnT3_4rDKziBqzNzuGtYL-jsfVZ5pc/edit,

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.

import scala.collection.JavaConversions._

private val sheetsFeedBase = "https://spreadsheets.google.com/feeds/spreadsheets/"

def getWorksheetByName(creds: models.GoogleCredential, fileId: String, worksheetName: String) = {

  val service = gDataApiForToken(creds.accessToken) //see above

  val metafeedUrl = new URL(sheetsFeedBase+fileId)

  val spreadsheet = service.getEntry(metafeedUrl, classOf[SpreadsheetEntry])

  spreadsheet.getWorksheets.find(_.getTitle.getPlainText.equals(worksheetName)).head
}

Finally, we are at the point we can manipulate the Worksheet! Here is a simple example of altering a cell.

def testWrite(creds: models.GoogleCredential, worksheetEntry: WorksheetEntry, whatToWrite: String) = {

  val service = gDataApiForToken(creds.accessToken) //see above

  val cellFeedUrl = worksheetEntry.getCellFeedUrl()
  val cellFeed = service.getFeed(cellFeedUrl, classOf[CellFeed])

  val cellEntry = new CellEntry(1, 1, whatToWrite)
  cellFeed.insert(cellEntry)
}

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 documentation for more details.

You can view and run all examples in this blog post on github here.

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!

Create your account »

Lucas Gray bio photo

Lucas Gray

Lead Software Engineer. Runner. ETL jockey.

Email Twitter Github Stackoverflow