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

  1. Go to Google Developer Console > Create credentials > Service account key.

  2. 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

Get your project off the ground with Space-Rocket! Fill out the form below to get started.

Space-Rocket pin icon