Elixir Getting Started with Google Sheets API and Service Accounts

Create app

mix_commands.sh


mix new google_sheets_example
cd google_sheets_example && mix ecto.create
git init && git add --all && git commit -m "initial commit"

Create Credentials

Go to Google Developer Console > Create credentials > Service account key.
Add the downloaded credentials directory path to an environment variable:
set_credentials.sh
```
   export GOOGLE_APPLICATION_CREDENTIALS=service_account.json
   
```

Add dependencies

To use Google Sheets API V4, you need two dependencies:

Add them to your mix.exs file:

mix.exs


...
{:goth, "~> 1.1"}
{:google_api_sheets, "~> 0.11.0"}
...

Run the mix deps.get command:

mix_commands.sh


mix deps.get

We are now ready to start making API calls to Google Sheets API.

Create a connection

Use the Goth library to request a token with spreadsheet auth scope and create a connection:

connection.ex


{:ok, token} = Goth.Token.for_scope("https://www.googleapis.com/auth/spreadsheets")
conn = GoogleApi.Sheets.V4.Connection.new(token.token)

Get Spreadsheet

To retrieve basic info about a spreadsheet:

sheet_info.ex


def sheet_info(spreadsheet_id) do
  {:ok, token} = Goth.Token.for_scope("https://www.googleapis.com/auth/spreadsheets")
  conn = GoogleApi.Sheets.V4.Connection.new(token.token)

  {:ok, response} = GoogleApi.Sheets.V4.Api.Spreadsheets.sheets_spreadsheets_get(conn, spreadsheet_id)
end

List Majors

Inspired by the Node.js Quickstart, list majors from a Google Sheet:

list_majors.ex


def list_majors() do
  {:ok, token} = Goth.Token.for_scope("https://www.googleapis.com/auth/spreadsheets")
  conn = GoogleApi.Sheets.V4.Connection.new(token.token)

  spreadsheet_id = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
  range = "Class Data!A2:E"

  {:ok, response} = GoogleApi.Sheets.V4.Api.Spreadsheets.sheets_spreadsheets_values_get(conn, spreadsheet_id, range)
  values = response.values

  Enum.map(values, fn row ->
    name = Enum.fetch(row, 0)
    major = Enum.fetch(row, 4)
    IO.inspect(name)
    IO.inspect(major)
  end)
end

Create Sheet

To create a new spreadsheet:

create_sheet.ex


def create_sheet() do
  {:ok, token} = Goth.Token.for_scope("https://www.googleapis.com/auth/spreadsheets")
  conn = GoogleApi.Sheets.V4.Connection.new(token.token)
  {:ok, response} = GoogleApi.Sheets.V4.Api.Spreadsheets.sheets_spreadsheets_create(conn)
end

A note on service accounts

A service account uses its own email address, so you must add your personal email account as a user to spreadsheets created with the service account. See this article for more details.

Running the code

To run the code, enter the Elixir command-line prompt and call the functions:

commands.sh


iex -S mix
GoogleSheetsExample.list_majors

That's all for now

This guide provides a starting point. Possible improvements include extracting token and connection logic into a reusable function, using OAuth 2.0, and building an API for CRUD operations. For more details, see this article.

Cheers and happy coding!

Launch
Your Project
in 6 Weeks,
Not 6 Months

Quality, speed, and price.
Choose all three.

Fill out the form below to get started.