Interacting with Google Sheets
27 min read
30 May 2025 (created at 28 May 2025)
No application can function without data, and sometimes your data lives in a spreadsheet. Let's see what can we do to change this sorry state of affairs and how can we move it into an actual database.
Why would you want to do that? Sometimes you just can't do the things you want to do inside Google Sheets, sometimes it's some external data you have no control over, or it might be that you just grew out of Sheets and want to build an application to make using the data easier.
In this article we'll be tackling the last case - we want to display some of our data inside a web application, so we'll be using Phoenix Framework and the Google Api Sheets together with the Google Api Drive packages.
You can also check out the example repository if you want to see the finished code.
What we'll be building?
In this article, we'll be generating and importing an example spreadsheet in Google Sheets, based on my own real world data and problems that I've been dealing with while practicing for a tournament in the Eve Online MMO.
The spreadsheet we're using as a practice tracker has multiple sheets that are named with dates of practices and who we were practicing against (some of them have a ! at start of their name, which means we should ignore that particular sheet). Inside each sheet we have player names and what spaceship types they have flown in that practice and looks roughly like this:
Out of this spreadsheet we want to extract two sets of data - first of all, we want to have attendance statistics, so that we know how many practices a player taken part in total, and when (and against who) was the last practice they took part in.
The second data set is going to show us what ships the players have flown the most - in the real world application we can also filter and search this data.
In the end we want to achieve something like this:
Phoenix application
Of course we need an application to display our data and a database to import it to. Since there's been a million other articles about this, we'll assume you know how to create a basic Phoenix app. Our application is completely standard, so we can focus on the most interesting and least written about bits, which are the Google API libraries. Of course to do that, we need to actually put them in our project.
To interact with the Google APIs, we need Goth and the two API packages. We're also adding the Timex library, since we want to handle and format dates and it makes it much easier. For generating data we'll be using Faker. Finally we'll use Dotenvy to handle our Google API key, so we can add all these to the mix.exs file:
{:goth, "~> 1.4"},
{:google_api_sheets, "~> 0.34.0"},
{:google_api_drive, "~> 0.32.0"},
{:timex, "~> 3.7"},
{:faker, "~> 0.19.0-alpha.1"},
{:dotenvy, "~> 1.1"}We need to create two schemas to put our data in: Attendances and FlownShips, which both will be sharing the Stats context. These are pretty basic, so we can just generate them with Phoenix generators and do some minor edits to get rid of all the extra code that isn't required to list them. Since we want to show stats that aren't available in Sheets, let's modify the two functions in the lib/google_sheets/stats.ex:
For the attendance stats, we're mostly interested in how many practices has the player taken part in and how many matches in those practices they have done. We'll also pick out the last practice date and name that the player has been in. The one thing that's not immediately clear here is the ARRAY_AGG fragment, which basically makes a list of all our available sheet names, sorts them by the date they contain and returns the first one, since we want just the latest one.
def list_attendances do
query =
from a in Attendance,
group_by: [a.name],
select: %GoogleSheets.Stats.AttendanceView{
id: a.name,
name: a.name,
matches: sum(a.times_flown),
practices: count(a.times_flown),
last_practice_date: max(a.practice_date),
last_practice_name:
fragment(
"(ARRAY_AGG(? order by make_date(extract(year from current_date)::int, extract(month from to_date(?, 'dd Mon'))::int, extract(day from to_date(?, 'dd Mon'))::int) DESC))[1]",
a.sheet_name,
a.sheet_name,
a.sheet_name
)
},
order_by: [desc: sum(a.times_flown)]
Repo.all(query)
endThis version of ship stats is even easier, since it's just a regular GROUP BY query that shows us how many times a given ship has been used. In a real application this should also allow filtering by player name and ship name, but for the sake of this article I've decided to stick with the simpler version.
def list_ship_stats do
query =
from s in FlownShip,
group_by: [s.ship_name],
select: %GoogleSheets.Stats.ShipView{
id: s.ship_name,
ship_name: s.ship_name,
times_flown: count(s.ship_name)
},
order_by: [desc: count(s.ship_name)]
Repo.all(query)
endGoogle Sheets setup
Before we start working on the sheets themselves, we need to do some preparation. To access the Google APIs, we need a service key, which we can create in the Google Cloud Console in the Service accounts section. One tricky thing to remember when creating it, is to add both Drive and Sheets to this key, because this is a separate account, so we need to be able to use the Drive API to share the created sheet with our Gmail or Google Workspace account.
We'll get this key out of the console in form of a json file, which we can put into a GSE_GCP_CREDENTIALS environment variable in our .env file.
GSE_GCP_CREDENTIALS='{
"type": "service_account",
(...rest of the json file...)
"universe_domain": "googleapis.com"
}'This can then get loaded by Dotenvy in the config/runtime.exs file:
import Dotenvy
source!([
Path.absname(".env"),
System.get_env()
])
config :google_sheets, gcp_credentials: env!("GSE_GCP_CREDENTIALS", :string!)An in the lib/google_sheets/application.ex we need to do some extra configuration for the API client, Goth, by providing it with our API key and the relevant scopes to use:
def start(_type, _args) do
credentials = Application.get_env(:google_sheets, :gcp_credentials) |> Jason.decode!()
scopes = [
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/drive"
]
source = {:service_account, credentials, scopes: scopes}
children = [
GoogleSheets.Repo,
{Phoenix.PubSub, name: GoogleSheets.PubSub},
{Finch, name: GoogleSheets.Finch},
{Goth, name: GoogleSheets.Goth, source: source},
GoogleSheetsWeb.Endpoint
]
opts = [strategy: :one_for_one, name: GoogleSheets.Supervisor]
Supervisor.start_link(children, opts)
endExporter module
With the API properly set up, we can move on to the most interesting part of the application and see how we can create a sheet for ourselves and fill it with some generated example data. First let's walk through the lib/google_sheets/exporters/exporter.ex file, which you can find on the github repository.
We'll start with the aliases for the API modules and defining a process callback. We'll also define some module variables to help make the code more readable later on.
defmodule GoogleSheets.Exporters.Exporter do
alias GoogleApi.Sheets.V4.Api.Spreadsheets
alias GoogleApi.Drive.V3.Api.Permissions
@callback process(String.t(), String.t()) :: {atom(), String.t()}
@empty_row %{
values: []
}
@title_row %{
values: [
%{userEnteredValue: %{stringValue: "Thumbs"}},
%{},
%{},
%{userEnteredValue: %{stringValue: "Our Comps:"}},
%{userEnteredValue: %{stringValue: "Set 1:"}},
%{},
%{userEnteredValue: %{stringValue: "Set 2:"}},
%{},
%{userEnteredValue: %{stringValue: "Set 3:"}},
%{},
%{userEnteredValue: %{stringValue: "Set 4:"}},
%{},
%{},
%{userEnteredValue: %{stringValue: "Ship Feedback Notes"}}
]
}
@set_name_formulas %{
1 => "=F1",
2 => "=H1",
3 => "=J1",
4 => "=L1"
}
@matches [
["Match 1:"],
["Match 2:"],
["Match 3:"]
]
The connect() function is also pretty simple - it takes the key we configured above and returns a connection to the Google Sheets API.
def connect() do
{:ok, token} = Goth.fetch(GoogleSheets.Goth)
GoogleApi.Sheets.V4.Connection.new(token.token)
endThe create_sheet function takes this connection and our title and email:
def create_sheet(conn, title, email) do
Then if we're able to successfully both create the spreadsheet with some initial data, and share it with the given email (which is required to make the spreadsheet show up in the account's Google Drive)...
with {:ok, %{spreadsheetId: spreadsheet_id} = spreadsheet} <-
Spreadsheets.sheets_spreadsheets_create(conn, create_sheet_body(title)),
{:ok, _permission} <-
Permissions.drive_permissions_create(conn, spreadsheet_id,
body: %{type: "user", role: "writer", emailAddress: email}
) do
...we can proceed with adding some extra data and extra formatting for the initial sheet. We use these insert_data functions here, because it's much easier to do it here than define these extra values in the module variables, and the formatting needs to be done in a separate step, because we need to have an existing sheet (and it's numerical ID) to use the APIs that allow formatting.
insert_data(conn, spreadsheet_id, "'! Practice Template'!K5", @matches)
insert_data(conn, spreadsheet_id, "'! Practice Template'!K18", @matches)
insert_data(conn, spreadsheet_id, "'! Practice Template'!K31", @matches)
insert_data(conn, spreadsheet_id, "'! Practice Template'!K44", @matches)
sheet = Enum.at(spreadsheet.sheets, 0)
create_attendance_counter(conn, spreadsheet_id, sheet.properties.sheetId)
{:ok, %{spreadsheet: spreadsheet_id, template_sheet: sheet.properties.sheetId}}Or if we failed at the API operations, we want to know what went wrong.
else
{:error, error} ->
IO.inspect(error, label: "error while creating spreadsheet:")
end
endNext let's look at the create_sheet_body private function, which defines our whole spreadsheet and the initial sheet. This is used in the create_sheet function above and is passed to the API call:
Spreadsheets.sheets_spreadsheets_create(conn, create_sheet_body(title))If we skipped this, Google Sheets would add the default empty Sheet 1, and we don't need that.
First we define the properties, which sets the title of the whole spreadsheet.
defp create_sheet_body(title) do
[
body: %{
properties: %{
title: title,
timeZone: "Europe/Paris"
},Next we start defining our initial sheet. We'll give it a name that starts with a "!", so that it's skipped on import and we'll freeze the title row, so it's easier to look at it in the web application.
sheets: [
%{
properties: %{
title: "! Practice Template",
gridProperties: %{
frozenRowCount: 1
}
},Next we can put in the actual data we want this sheet to have. We'll put in the title row and since we usually run 4 matches in a practice evening, we'll put two rows of titles for each match separated by some empty rows - for the sake of this example we'll use 6 players in a match.
data: %{
rowData: [
@title_row,
@empty_row,
set_number_row(1),
set_titles_row(1),
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
set_number_row(2),
set_titles_row(2),
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
set_number_row(3),
set_titles_row(3),
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
@empty_row,
set_number_row(4),
set_titles_row(4)
]
}
}
]
}
]
endLet's also take a look at the insert_data(conn, spreadsheet_id, "'! Practice Template'!K5", @matches) calls and how they work. They're relatively simple and all the insert_data function does is it passes the range (formatted as a typical spreadsheet range you would normally use everywhere, so in this example we have 'sheet name'!cell_address) and the actual data (formatted as an array of arrays of values) to the API call.
The good thing about this is that we only need to pass the starting cell and the API will automatically fill however many cells we have values for.
def insert_data(conn, spreadsheet_id, range, values) do
with {:ok, _} <- Spreadsheets.sheets_spreadsheets_values_update(conn, spreadsheet_id, range, valueInputOption: "raw", body: %{values: values}) do
:ok
else
{:error, error} ->
IO.inspect(error)
end
endThe next thing our create_sheet function does is adding conditional formatting and formulas to our sheet. We use them to list players that have signed up for a practice and to quickly see in the sheet how many matches they've already been assigned to.
First let's deal with the counting formula. As you can see from the snippet below, the formula itself is pretty simple - it just counts how many times the player name from cell A2 is listed in columns F and G, as those are our pilot and alternate pilot columns. How we define it here is a bit more complex though - we'll be using the repeatCell request, which works like you'd enter the formula into one cell and then dragged it down to expand it into more cells, so it will automatically replace the A2 reference with other cells.
defp create_attendance_counter(conn, spreadsheet_id, sheet_id) do
requests = [
%{
repeatCell: %{
cell: %{userEnteredValue: %{formulaValue: "=countif('! Practice Template'!F:G,A2)"}},
fields: "*",To define where we're dragging this initial cell to, we need to pass a range and sadly the API requires us to use this weird format instead of a string representation. It uses column and row indexes starting with 0, and a numerical sheet ID rather than its name (which we retrieved earlier in the create_sheet function).
The range shown below is equivalent to "'! Practice Template'!A2:A22".
range: %{
sheetId: sheet_id,
startColumnIndex: 1,
startRowIndex: 1,
endRowIndex: 21,
endColumnIndex: 2
}
}
},Next we'll add some conditional formatting to our practice counters - this will make the background of these cells be red if a player didn't take part in the practice yet and green if they've been in at least 3 matches. Again, we're using the weird range definition explained above in these calls.
%{
addConditionalFormatRule: %{
rule: %{
ranges: [%{
sheetId: sheet_id,
startColumnIndex: 1,
startRowIndex: 1,
endRowIndex: 21,
endColumnIndex: 2
}],
booleanRule: %{
format: %{
backgroundColor: %{
red: 0.91764706,
green: 0.6,
blue: 0.6
}
},
condition: %{
type: "NUMBER_LESS",
values: [
%{userEnteredValue: "1"}
],
}
}
}
}
},
%{
addConditionalFormatRule: %{
rule: %{
ranges: [%{
sheetId: sheet_id,
startColumnIndex: 1,
startRowIndex: 1,
endRowIndex: 21,
endColumnIndex: 2
}],
booleanRule: %{
format: %{
backgroundColor: %{
red: 0.7176471,
green: 0.88235295,
blue: 0.8039216
}
},
condition: %{
type: "NUMBER_GREATER",
values: [
%{userEnteredValue: "2"}
],
}
}
}
}
}
]All of these requests are then passed onto the API to execute as a batch update.
update_cells(conn, spreadsheet_id, requests)
endFilling in the data
To actually use our practice sheet we need some data to ingest. As mentioned before, it's supposed to all live in separate (but with a similar template) sheets in our spreadsheets, so let's use Faker to generate some names and copy our template sheet a few times.
This is done in the lib/google_sheets/exporters/practice_tracker.ex, which implements the process callback we declared before.
First, let's generate some names. Superheroes will work well for our player names, and the Elixir version of Faker doesn't have many sci-fi generators, so let's use Pokemons for the ships.
def process(title, email) do
all_players = Enum.map(1..10, fn _ -> Faker.Superhero.name() end)
all_ships = Enum.map(1..20, fn _ -> Faker.Pokemon.name() end)
Then we can connect to the API and call the create_sheet function from the Exporter module.
conn = connect()
{:ok, %{spreadsheet: spreadsheet_id, template_sheet: sheet_id}} = create_sheet(conn, title, email)Next we're doing a bit of manipulation to get from a list of planet names to make up the sheet title with a date included.
Enum.map(1..3, fn _ ->
Faker.StarWars.planet()
end)
|> Enum.with_index(20)
|> Enum.reverse
|> Enum.each(fn {name, index} ->
title = "#{index} March - #{name}"Both copy_sheet and rename_sheet are defined in the Exporter module and just invoke the relevant API calls.
{:ok, %{sheetId: copied_sheet_id}} = copy_sheet(conn, spreadsheet_id, sheet_id)
rename_sheet(conn, spreadsheet_id, copied_sheet_id, title)Next we'll just use the insert_data function on the newly copied sheet and insert a random selection of players and ship names into the sheet.
thumbs = all_players |> Enum.map(fn name -> [name] end)
insert_data(conn, spreadsheet_id, "'#{title}'!A2", thumbs)
Enum.each(1..4, fn set_num ->
set_players = all_players |> Enum.shuffle() |> Enum.take(6) |> Enum.map(fn name -> [name] end)
insert_data(conn, spreadsheet_id, "'#{title}'!#{@set_player_fields[set_num]}", set_players)
set_ships = all_ships |> Enum.shuffle() |> Enum.take(6) |> Enum.map(fn name -> [name] end)
insert_data(conn, spreadsheet_id, "'#{title}'!#{@set_ship_fields[set_num]}", set_ships)
end)
end)
{:ok, spreadsheet_id}
endActually running the exporter
Now that we have an exporter, we need a way to execute it. We could just run it from an IEx console, but the easier way is to use a Mix task instead. We can add a lib/mix/tasks/export.practice.ex file and it's really simple:
defmodule Mix.Tasks.Export.Practice do
@shortdoc "generate practice data and save it to google sheets"
@moduledoc """
Console task for exporting the practice data.
"""
use Mix.Task
@impl Mix.Task
def run(args) do
{parsed, _, _} = OptionParser.parse(args, strict: [email: :string, title: :string])
Mix.Task.run("app.start")
{:ok, spreadsheet_id} = GoogleSheets.Exporters.PracticeTracker.process(parsed[:title], parsed[:email])
IO.puts("You can use the following spreadsheet id for importing: #{spreadsheet_id}")
end
endThis can then be run in a terminal like so, obviously using your own gmail address so that the file is shared with you correctly:
mix export.practice --title "Test Tracker" --email "your_gmail_email@gmail"Remember that spreadsheet id that the task outputs, as we'll be using it shortly. You can also get it from the web address of the spreadsheet if you happen to lose it.
Importing the generated data
Now that we have generated some data to work with, let's see how we can get it to our database.
Similar to the Exporter module, we'll start by looking through the lib/importers/importer.ex. The beginning is practically the same - we're defining the process callback and connecting to the API.
defmodule GoogleSheets.Importers.Importer do
alias GoogleApi.Sheets.V4.Api.Spreadsheets
@callback process(spreadsheet_id :: String.t(), year :: integer()) ::
atom()
def connect() do
{:ok, token} = Goth.fetch(GoogleSheets.Goth)
GoogleApi.Sheets.V4.Connection.new(token.token)
end
Connecting to a spreadsheet is also pretty straightforward - we pass the spreadsheet ID we got from the exporter Mix task output. We can also define some helper functions to help extracting the sheet names and converting them to dates.
def sheets(conn, spreadsheet_id) do
{:ok, response} = Spreadsheets.sheets_spreadsheets_get(conn, spreadsheet_id)
response.sheets
end
def get_practice_names(sheets) do
sheets
|> Enum.map(fn sheet -> sheet.properties.title end)
|> Enum.reject(fn title -> String.starts_with?(title, "!") end)
end
def practice_date(sheet_name, year) do
sheet_name
|> String.split(" -")
|> List.first()
|> Timex.parse!("{D} {Mfull}")
|> Timex.set(year: year)
|> Timex.to_date()
endFinally the last function returns a range of data from the spreadsheet cells.
def get_data(conn, spreadsheet_id, range) do
{:ok, response} =
Spreadsheets.sheets_spreadsheets_values_get(conn, spreadsheet_id, range)
response
end
endImporting attendance data
In comparison to the exporter, the attendance importer is really simple and lives in the lib/importers/attendance.ex. Once again, we're declaring our callback function and connect to the API, and then for each valid sheet we're extracting the data we're interested in. Once that's done, we're just inserting the data straight to the database through the Repo module.
defmodule GoogleSheets.Importers.Attendance do
import GoogleSheets.Importers.Importer
@behaviour GoogleSheets.Importers.Importer
@impl true
def process(spreadsheet_id, year) do
conn = connect()
results =
conn
|> sheets(spreadsheet_id)
|> get_practice_names()
|> Enum.flat_map(fn name ->
name
|> get_practice_data(conn, spreadsheet_id, year)
end)
GoogleSheets.Repo.insert_all(GoogleSheets.Stats.Attendance, results)
:ok
end
The data extraction itself is also not very complex - once we get the data from the range we're interested in, we can just make a list of maps. Since we're using insert_all, we need to make sure they have the correct timestamps, since we don't get them automatically.
defp data_range(sheet_name), do: "'#{sheet_name}'!A2:B"
defp get_practice_data(sheet_name, conn, spreadsheet_id, year) do
range = data_range(sheet_name)
date = practice_date(sheet_name, year)
response = get_data(conn, spreadsheet_id, range)
now = DateTime.utc_now() |> DateTime.truncate(:second)
response.values
|> Enum.reject(&Enum.empty?/1)
|> Enum.reject(fn row -> List.first(row) == "" end)
|> Enum.map(fn row ->
%{
name: List.first(row),
times_flown: row |> List.last() |> String.to_integer(),
practice_date: date,
sheet_name: sheet_name,
inserted_at: now,
updated_at: now
}
end)
end
endMix task for the importer.
And to mirror what we did with the exporter, let's also have another task to import the attendance data.
defmodule Mix.Tasks.Import.Attendance do
@shortdoc "import attendance from google sheets"
@moduledoc """
Console task for importing the attendance data.
"""
use Mix.Task
@impl Mix.Task
def run(args) do
{parsed, _, _} = OptionParser.parse(args, strict: [sheet: :string, year: :integer])
Mix.Task.run("app.start")
GoogleSheets.Importers.Attendance.process(parsed[:sheet], parsed[:year])
end
end
This can be executed with from the terminal and will import the attendance data.
mix import.attendance --year 2025 --sheet "my-spreadsheet-id"Second importer and mix task
In the github repository there's also a second importer class, lib/importers/ship_stats.ex, together with a second mix task in lib/mix/tasks/import.ships.ex. These are pretty much the same code as importing attendance - with only obvious difference is that we're interested in ship names rather than just player names, so we can safely skip explaining them.
Once you run the second task with:
mix import.ships --year 2025 --sheet "my-spreadsheet-id"We are pretty much finished and we achieved what we set out to do - we've succesfully connected to the Google APIs and learned how to both create and extract data from Google Sheets and import them to our application's database.
What's next?
Of course, for the sake of the article this is just a simplified example. As I've mentioned before, in a real application we would probably need to have extra things like filtering the data, handling things like being able to have different tournaments or handling a tournament that happens to span different years.
I wanted to focus on the Google APIs here, because I feel like using them in Elixir is both a very undocumented and very useful thing. You can always find more things to do with them on the Google Documentation, which then translates pretty straighforwardly into calling appropriate methods from the Google API Sheets hex package.
FAQ
What is the purpose of integrating Google Sheets with an Elixir application?
Integrating Google Sheets with an Elixir application allows developers to access, manipulate, and display spreadsheet data within a Phoenix web app. This is particularly useful when transitioning from manual spreadsheet workflows to more robust, database-driven solutions, enhancing data accessibility and user interaction.
Which Elixir libraries are essential for interacting with Google Sheets?
Key libraries include:
Gothfor handling Google authentication.GoogleApi.SheetsandGoogleApi.Drivefor accessing Sheets and Drive APIs.Timexfor date and time manipulation.Fakerfor generating sample data.Dotenvyfor managing environment variables securely.
How does the application authenticate with Google APIs?
Authentication is managed using the Goth library, which utilizes service account credentials. These credentials are securely stored and loaded using Dotenvy, ensuring that sensitive information like API keys remains protected.
What data structures are used to store spreadsheet information?
The application defines two primary schemas:
Attendance: Tracks player participation in practice sessions.FlownShip: Records the types of ships flown by players during practices. These schemas are part of a sharedStatscontext, facilitating organized data management and retrieval.
How is data extracted from Google Sheets and imported into the application?
The application connects to Google Sheets using the Sheets API, reads relevant data (such as player names and ship types), and processes it to populate the Attendance and FlownShip schemas. It includes logic to handle specific naming conventions, like ignoring sheets prefixed with an exclamation mark.
What analytical features does the application provide?
The application offers insights such as:
- Total number of practices attended by each player.
- Number of matches participated in.
- Date and name of the most recent practice session.
- Frequency of specific ships flown by players. These analytics are derived using Ecto queries and SQL fragments for efficient data aggregation.
How does the application handle date parsing and formatting?
Date operations are managed using the Timex library, which simplifies parsing dates from sheet names and formatting them for display. This ensures accurate tracking of practice sessions over time.
Can this integration be adapted for other use cases beyond gaming?
Absolutely. While the example focuses on tracking practice sessions in a gaming context, the integration approach is applicable to various domains, such as attendance tracking, inventory management, or any scenario where data is initially maintained in Google Sheets.
Where can I find the complete source code for this integration?
The full implementation is available in the example repository linked within the article, providing a practical reference for developers looking to implement similar integrations.
