Google Sheets and Haskell

This week, I’m playing with some web programming in Haskell. I don’t have much experience with accessing web services in my day job so I decided learn about them by making a little flash-card app which accesses the Google Sheets API to retrieve flash cards.

Haskell OAuth2?

Although the Google Sheets API doesn’t have official support for Haskell, it is built upon the OAuth2 API, as described in Using OAuth 2.0 for Web Server Applications.

Haskell has an interface to OAuth 2.0 called hoauth2. Unfortunately, the documentation for hoauth2 is so sparse that I couldn’t figure out how to use it (the only documentation they have is a single web-app built with the WAI framework). Since I want to make a command line application and not an app which you interact with in a browser, I used Haskell’s HTTP client package and Google’s authorization URLs directly.

Making a Google API project

Next I’ll cover what you need to do to enable the Google API for your project by describing how I enabled Sheets for my flash card application.

First open https://console.developers.google.com/ and create a new project:

0_create.png

1_new_project.png

You should see your project name appear in the upper left of the page:

2_title.png

Click on Enable APIs and Services:

3_enable_apis.png

Search for the API you want to enable:

4_sheets_api.png

Click Enable to enable the API for your project:

5_enable.png

Next, you’ll have to create credentials for your project:

6_credentials.png

Add credentials to your project. I’m creating a CLI tool that accesses application data:

7_add_credentials

Name your OAuth 2.0 client:

8_id.png

Set up the consent screen:

9_consent.png

Finally, note down your client ID and download the credentials file:

10_get_credentials.png

The file will be called client_id.json. This will be the token you’ll use to verify your app with the Google API.

OAuth Authorization

Google has a great explanation for how to use OAuth2 with Mobile and Desktop Applications. Unfortunately, they don’t have a Haskell API, so we need to modify their suggestions to work with the http-client.

First, we need to import the HTTP client modules and ByteString, which is used to read from a HTTP message:

import qualified Data.ByteString.Char8 as C

import Network.HTTP.Client
import Network.HTTP.Client.TLS
import Network.HTTP.Types.Status

In addition, some requests must use the Data.Text format for strings:

import qualified Data.Text as T

We’ll also need to request permission from the user to access their sheets. This is done by opening a web browser with a page which the user can use to generate a token to access their account. We can open a web browser using the Web.Browser library:

import Web.Browser

Finally, we need a JSON parser to decode GET messages. I used the Aeson library for this:

import Data.Aeson
import Data.Aeson.Types
import qualified Data.Map as M

The main function of this application is runFlashCardsMaybe:

runFlashCardsMaybe :: MaybeT IO ()
runFlashCardsMaybe = do lift $ putStrLn "Running flash cards"
                        args <- lift $ getArgs
                        if length args < 4
                        then lift $ putStrLn "Usage: GoogleSheetsDemo-exe <client_id> <client_secret> <spreadsheet_id> <rows_to_read>"
                        else let clientID = args !! 0
                                 clientSecret = args !! 1
                                 spreadSheetID = args !! 2
                                 rowsToRead = args !! 3
                             in do connection <- setupConnection clientID clientSecret
                                   flashCards <- getFlashCards spreadSheetID rowsToRead connection
                                   doFlashCards flashCards

MaybeT is a Monad Transformer, which means that it adds Maybe functionality to the IO Monad. I haven’t covered Monad Transformers yet in my blog, but for now, you can think of them as a multi-layered Monad, similar to Maybe IO.

The function gets the arguments passed via the CLI for the clientID and clientSecret which we got in the previous step, the user’s spreadsheet ID and the number of rows to read from the spreadsheet.

There are three steps to the application, setupConnection, getFlashCards, and doFlashCards, in that order.

The first part of connecting to Google API is setting up a connection. Here’s the function I used to connect:

setupConnection :: String -> String -> MaybeT IO Connection
setupConnection clientID clientSecret
    = do manager <- lift $ newManager tlsManagerSettings
         lift $ openBrowser ("https://accounts.google.com/o/oauth2/v2/auth?" ++
                             "scope=https://www.googleapis.com/auth/spreadsheets&" ++
                             "response_type=code&" ++
                             "state=security_token%3D138r5719ru3e1%26url%3Doauth2.example.com/token&" ++
                             "redirect_uri=urn:ietf:wg:oauth:2.0:oob&" ++
                             "client_id=" ++ clientID)
         lift $ putStrLn "Please enter authorization code:"
         lift $ hFlush stdout
         authCode <- lift $ getLine
         initialRequest <- lift $ parseRequest "https://www.googleapis.com/oauth2/v4/token"
         let pairs = fmap (\(x, y) -> (C.pack x, C.pack y))
                          [("code", authCode),
                           ("client_id", clientID),
                           ("client_secret", clientSecret),
                           ("redirect_uri", "urn:ietf:wg:oauth:2.0:oob"),
                           ("grant_type", "authorization_code")]
             request = urlEncodedBody pairs initialRequest
         response <- lift $ httpLbs request manager
         if responseStatus response == status200
         then do let body = responseBody response
                 do bodyData <- MaybeT $ return $ (decode body :: Maybe AuthResponse)
                    MaybeT $ return $ createConnection manager bodyData
         else MaybeT $ return $ Nothing

setupConnection takes the clientID and clientSecret that we made previously as arguments. There’s a lot going on here, so let’s break it down. First you have to make a connection manager, in this case we want a manager that supports TLS:

manager <- lift $ newManager tlsManagerSettings

The reason we have to call lift $ newManager is because the function evaluates to a MaybeT IO metatype. lift transports the newManager function from the IO monad into the MaybeT IO monad.

The next step is to ask the user for an access token by opening a standard URL in a web browser. This URL can be found in OAuth 2.0 for Mobile & Desktop Apps under the Sample Authorization URLs heading as the copy-paste sample:

lift $ openBrowser ("https://accounts.google.com/o/oauth2/v2/auth?" ++
                    "scope=https://www.googleapis.com/auth/spreadsheets&" ++
                    "response_type=code&" ++
                    "state=security_token%3D138r5719ru3e1%26url%3Doauth2.example.com/token&" ++
                    "redirect_uri=urn:ietf:wg:oauth:2.0:oob&" ++
                    "client_id=" ++ clientID)

Other methods of authorization are available for web and mobile applications.

The user’s browser will open a page like this:

installedresult

Next we request the authorization code from the user:

lift $ putStrLn "Please enter authorization code:"
lift $ hFlush stdout
authCode <- lift $ getLine

Then we need to build a request for an authorization token from Google’s OAuth2 server:

initialRequest <- lift $ parseRequest "https://www.googleapis.com/oauth2/v4/token"
let pairs = fmap (\(x, y) -> (C.pack x, C.pack y))
            [("code", authCode),
             ("client_id", clientID),
             ("client_secret", clientSecret),
             ("redirect_uri", "urn:ietf:wg:oauth:2.0:oob"),
             ("grant_type", "authorization_code")]
    request = urlEncodedBody pairs initialRequest

The request consists of a set of key value pairs encoded as ByteStrings. C.pack converts a String to a ByteString, so we can map a tuple conversion lambda over the list of key value pairs to create an appropriate GET request. The request is parsed by urlEncodedBody, which is a function in the HTTP client library.

Then we call the request using the httpLbs function with the TLS manager and check the response:

response <- lift $ httpLbs request manager
if responseStatus response == status200
then do let body = responseBody response
        do bodyData <- MaybeT $ return $ (decode body :: Maybe AuthResponse)
           MaybeT $ return $ createConnection manager bodyData
else MaybeT $ return $ Nothing

If the response is 200 OK, we need to parse the response message. I used the Aeson library to decode the response with the type AuthResponse:

data AuthResponse = AuthResponse {accessToken :: T.Text,
                                  tokenType :: T.Text,
                                  expiresIn :: Int,
                                  refreshToken :: T.Text}

instance FromJSON AuthResponse where
    parseJSON (Object v) = AuthResponse
                           <$> v .: T.pack "access_token"
                           <*> v .: T.pack "token_type"
                           <*> v .: T.pack "expires_in"
                           <*> v .: T.pack "refresh_token"
    parseJSON invalid = typeMismatch "AuthResponse" invalid

Once we have the accessToken, we can create an authorized connection to Google Sheets:

data Connection = Connection Manager AuthResponse

createConnection :: Manager -> AuthResponse -> Maybe Connection
createConnection manager authResponse = Just $ Connection manager authResponse

The next step after getting an authorized connection to Google Sheets is to get the flash cards out of the sheet:

getFlashCards :: String -> String -> Connection -> MaybeT IO [[T.Text]]
getFlashCards spreadSheetID rowsToRead (Connection manager (AuthResponse {accessToken = thisAccessToken,
                                                                          tokenType = thisTokenType,
                                                                          expiresIn = thisExpiresIn,
                                                                          refreshToken = thisRefreshToken}))
    = do rowsRequest <- parseRequest ("GET https://sheets.googleapis.com/v4/spreadsheets/" ++
                                      spreadSheetID ++
                                      "/values/Sheet1!A1:B" ++ rowsToRead ++ "?access_token=" ++
                                      (T.unpack thisAccessToken))
         rowsResponse <- lift $ httpLbs rowsRequest manager
         maybeRowsResponse <- return (decode (responseBody rowsResponse) :: Maybe RowsResponse)
         MaybeT $ return $ fmap getValues maybeRowsResponse

Again, there’s a lot going on here, so let’s break it down one function call at a time. First, we need to get the rows out of the sheet. This is achieved using a GET command, as specified in Reading and Writing Values using the Google Sheets API under the Reading a single range heading:

rowsRequest <- parseRequest ("GET https://sheets.googleapis.com/v4/spreadsheets/" ++
                             spreadSheetID ++
                             "/values/Sheet1!A1:B" ++ rowsToRead ++ "?access_token=" ++
                             (T.unpack thisAccessToken))

The message requests a set of values from A1 to BN where N is the number of rows to read. The A column contains the front of the flash card and the B column contains the back of the flash card. We also have to pass the access token using “?access_token=” ++ (T.unpack thisAccessToken). T.unpack converts a Data.Text string to a String.

Next, we send the request using the httpLbs function and the TLS manager:

rowsResponse <- lift $ httpLbs rowsRequest manager

After this, we need to parse the response body, which contains the rows which were read from the user’s spreadsheet:

maybeRowsResponse <- return (decode (responseBody rowsResponse) :: Maybe RowsResponse)
MaybeT $ return $ fmap getValues maybeRowsResponse

Again, we use the Aeson library to parse the response. The RowsResponse type contains the data for the rows in its values field:

data RowsResponse = RowsResponse {range :: T.Text,
                                  majorDimension :: T.Text,
                                  values :: [[T.Text]]}

instance FromJSON RowsResponse where
    parseJSON (Object v) = RowsResponse
                           <$> v .: T.pack "range"
                           <*> v .: T.pack "majorDimension"
                           <*> v .: T.pack "values"
    parseJSON invalid = typeMismatch "RowsResponse" invalid

getValues :: RowsResponse -> [[T.Text]]
getValues (RowsResponse {values = thisValues}) = thisValues

Once the values are parsed into a [[T.Text]] type, it’s simple to run an interactive flash card test on the command line by printing the front of the “flash card”, making the user press enter after they make a guess about what is on the back, and then showing the back of the “flash card”:

doFlashCards :: [[T.Text]] -> MaybeT IO ()
doFlashCards [] = lift $ return ()
doFlashCards (row : rows)
    = do lift $ putStrLn $ T.unpack (row !! 0)
         lift $ hFlush stdout
         lift getLine
         lift $ putStrLn $ T.unpack (row !! 1)
         lift $ hFlush stdout
         lift getLine
         doFlashCards rows

The source code for this post is available at Google Sheets Demo.

Resources:

Haskell HTTP Client Documentation

OAuth 2.0 for Mobile and Desktop Applications in the Google API

Introduction to the Google Sheets API

Reading and Writing Values using the Google Sheets API

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s