How to use Google Sheets with Crystal
Learn how to connect, read, and write Google Sheets data using the Crystal programming language.
Google Sheets is a great tool for organizing and sharing data, and thanks to the Google Sheets API, you can automate and interact with your spreadsheets programmatically. While most tutorials use languages like Python or JavaScript, this post will show you how to do it with Crystal—a fast, Ruby-like language.
We’ll go through how to connect to the API, read and write data, and handle authentication using a service account. If you’re curious about using Crystal for practical projects or just want to script Google Sheets in a new way, you’re in the right place.
Setting Up Google Cloud and API Access
Creating a new project
Access google cloud console and open the new project form:
Fill in the project name (the organization is optional), and then click create
After the new project is created, you will redirected to the home page. The next step is to select the active project.
Enable Google sheets API
Open the context menu, to see all the products and then click on APIs & Services
Click on “Enable APIs and Services”
In the search bar, type “sheets api”, and once found, click on it.
Click on “Enable”
Create service account
Using a service account is suitable when you’re building automated applications or backends. It doesn’t require interactive logins and it works well for Google sheets that you own.
In the same view of “APIs & Services”, click on “Credentials” from the sidebar and from the dropdown “Create credentials”, select “Service account”
In the form, fill in the service name, you can edit the service account id if you want. Click on “Create and continue”.
Under “Grant this service account access”, you can skip unless your service needs specific roles. Click “Done”.
From the credentials view, click on the new created account.
Click on the “Keys” tab and from the dropdown menu “Add key”, select “Create new key”
Choose JSON and click “Create”. This will download a .json key file — keep it safe, as it contains private credentials.
Share Your Google Sheet with the Service Account
Open the target Google Sheet in your browser.
Click “Share”.
Share it with the service account email address (found in the JSON file under “client_email”; it should be the same email value from the credentials view).
Uncheck Notify people.
Give at least Editor access if your script will write data.
Project setup
For reference, here is the template for the Google sheets that I’m using .
Let’s start by generating a new Crystal app:
crystal init app g_sheets_api
To manage the authentication with the google account service, we will be using this google-auth shard.
Add the shard to g_sheets_api/shard.yml file like so:
dependencies:
google-auth:
github: cyangle/google-auth
version: ~> 0.1.1
and then run shard install
For the sake of simplicity, let’s put the credentials file in the project root with the name creds.json. Important: don’t commit the file to your repo. To prevent that, let’s update the .gitignore to include the file:
/docs/
/lib/
/bin/
/.shards/
*.dwarf
creds.json # the new line
It’s time to test the Google setup and try to get the access token. Update src/g_sheets_api.cr to:
require "google-auth"
module GSheetsApi
VERSION = "0.1.0"
cred_path = File.expand_path("../creds.json", __DIR__)
cred = GoogleAuth::FileCredential.new(
file_path: cred_path,
scopes: "https://www.googleapis.com/auth/spreadsheets", # String | Array(String)
user_agent: "crystal/client",
)
token = cred.get_token
p token.access_token
end
Run it with crystal run src/g_sheets_api.cr and it should print the access token in the console. Once that confirmed, let’s proceed with accessing a google sheet. For this tutorial, I prepared some data about books, and I provided a public sheet so you can copy to your drive to follow along, link and the Github repo for the code, the sheet should look like this:
Note the highlighted area in the screenshots, the sheet_id is available from the URL with this format https://docs.google.com/spreadsheets/d/:sheet_id/edit?gid=0#gid=0, and the sheet name is books, by default the value for the sheet name is Sheet1.
Let’s try to fetch the data and see how it goes. We are using the A1 notation for the range, the specific cells in a spreadsheet that you want to read from, write to, or otherwise manipulate. Here is a set of examples from the documentation as a reference.
require "google-auth"
module GSheetsApi
VERSION = "0.1.0"
cred_path = File.expand_path("../creds.json", __DIR__)
cred = GoogleAuth::FileCredential.new(
file_path: cred_path,
scopes: "https://www.googleapis.com/auth/spreadsheets", # String | Array(String)
user_agent: "crystal/client",
)
token = cred.get_token
access_token = token.access_token
sheet_id = "enter the sheet ID from the URL"
sheet_name = "books"
range = "#{sheet_name}!A1:D6" # The r
sheet_url = "https://sheets.googleapis.com/v4/spreadsheets/#{sheet_id}/values/#{URI.encode_path(range)}"
sheet_data = HTTP::Client.get(sheet_url,
headers: HTTP::Headers{
"Authorization" => "Bearer #{access_token}",
})
puts sheet_data.body
end
Running the changes with crystal run src/g_sheets_api.cr should output the following payload:
{
"range": "books!A1:D6",
"majorDimension": "ROWS",
"values": [
["Title", "Author", "Publication year", "Read?"],
["To Kill a Mockingbird", "Harper Lee", "1960", "FALSE"],
["1984", "George Orwell", "1949", "FALSE"],
["The Great Gatsby", "F. Scott Fitzgerald", "1925", "FALSE"],
["The Hobbit", "J.R.R. Tolkien", "1937", "FALSE"],
["Brave New World", "Aldous Huxley", "1932", "FALSE"]
["Only title"]
]
}
Let’s add a wrapper around the API and a class to make working with Google Sheets easier. We’ll create a SheetsApiClient class to handle API requests, and a Book class to help parse and manage book data from the sheet. Below are examples of how to use these classes to read, add, update, and delete rows in your Google Sheet.
# src/book.cr
# This class will be useful for parsing the response payload
class Book
property row_index : Int32
property title : String
property author : String?
property publication_year : Int32?
property read : Bool?
def initialize(@row_index, @title, @author, @publication_year, @read = false)
end
def to_payload
[title, author, publication_year, read]
end
end
# src/sheets_api_client.cr
require "./book"
class SheetsApiClient
getter sheet_id : String
getter start_col : String
getter end_col : String
getter sheet_name : String
def initialize(@sheet_id : String, @start_col : String, @end_col : String, @sheet_name : String)
end
def all_rows : Array(Book)
sheet_url = "https://sheets.googleapis.com/v4/spreadsheets/#{@sheet_id}/values/#{URI.encode_path(default_range)}"
response = HTTP::Client.get(sheet_url, headers: auth_headers)
unless response.status_code == 200
puts "Failed to fetch data: #{response.status_code} - #{response.body}"
return [] of Book
end
parse_books(response.body)
end
def update_row(row_index : Int32, values : Array(Bool | Int32 | String | Nil)) : HTTP::Client::Response?
update_range = range(row_index)
url = "https://sheets.googleapis.com/v4/spreadsheets/#{@sheet_id}/values/#{URI.encode_path(update_range)}?valueInputOption=USER_ENTERED"
body = {
"range" => update_range,
"majorDimension" => "ROWS",
"values" => [values],
}.to_json
response = HTTP::Client.put(url, headers: auth_headers_with_json, body: body)
unless response.status_code == 200
puts "Failed to update row: #{response.status_code} - #{response.body}"
return nil
end
response
end
def add_row(values : Array(Bool | Int32 | String | Nil)) : HTTP::Client::Response?
url = "https://sheets.googleapis.com/v4/spreadsheets/#{@sheet_id}/values/#{URI.encode_path(default_range)}:append?valueInputOption=USER_ENTERED"
body = {
"range" => default_range,
"majorDimension" => "ROWS",
"values" => [values],
}.to_json
response = HTTP::Client.post(url, headers: auth_headers_with_json, body: body)
unless response.status_code == 200
puts "Failed adding row: #{response.status_code} - #{response.body}"
return nil
end
response
end
# This method will delete the row without keeping a placeholder
# an alternative is to clear the row values instead.
def delete_row(row_index : Int32) : HTTP::Client::Response?
url = "https://sheets.googleapis.com/v4/spreadsheets/#{@sheet_id}:batchUpdate"
start_index = row_index - 1
s_gid = sheet_gid
unless s_gid
puts "Sheet ID not found for sheet name: #{sheet_name}"
return nil
end
body = {
"requests" => [
{
"deleteDimension" => {
"range" => {
"sheetId" => sheet_gid,
"dimension" => "ROWS",
"startIndex" => start_index,
"endIndex" => start_index + 1,
},
},
},
],
}.to_json
response = HTTP::Client.post(url, headers: auth_headers_with_json, body: body)
unless response.status_code == 200
puts "Failed to delete row: #{response.status_code} - #{response.body}"
return nil
end
response
end
private def default_range : String
"#{sheet_name}!#{start_col}1:#{end_col}1000"
end
private def range(row_index : Int32) : String
"#{sheet_name}!#{start_col}#{row_index}:#{end_col}#{row_index}"
end
private def access_token : String
cred_path = File.expand_path("../creds.json", __DIR__)
cred = GoogleAuth::FileCredential.new(
file_path: cred_path,
scopes: "https://www.googleapis.com/auth/spreadsheets",
user_agent: "crystal/client",
)
cred.get_token.access_token
end
private def auth_headers : HTTP::Headers
HTTP::Headers{
"Authorization" => "Bearer #{access_token}",
}
end
private def auth_headers_with_json : HTTP::Headers
HTTP::Headers{
"Authorization" => "Bearer #{access_token}",
"Content-Type" => "application/json",
}
end
private def parse_books(payload : String) : Array(Book)
data = JSON.parse(payload)
values = data["values"].as_a
header = values[0].as_a.map(&.as_s)
books = [] of Book
values[1..].each_with_index do |row, index|
row_a = row.as_a
row_index = index + 2
title = row_a[0]?.try(&.as_s)
unless title
puts "Skipping row #{row_index} due to missing title"
next
end
author = row_a[1]?.try(&.as_s)
publication_year = row_a[2]?.try { |v| v.to_s.to_i? }
read = row_a[3]?.try { |v| v.to_s.downcase == "true" }
book = Book.new(row_index, title, author, publication_year, read)
books << book
end
books
end
private def sheet_gid : Int32?
url = "https://sheets.googleapis.com/v4/spreadsheets/#{sheet_id}"
response = HTTP::Client.get(url, headers: auth_headers)
return nil unless response.status_code == 200
data = JSON.parse(response.body)
sheets = data["sheets"]?.try(&.as_a)
return nil unless sheets
sheet = sheets.find { |s| s["properties"]?.try(&.["title"]?.try(&.as_s)) == sheet_name }
return nil unless sheet
properties = sheet["properties"]?.try(&.as_h)
sheet_id = properties.try(&.["sheetId"]?.try(&.as_i))
sheet_id
end
end
And finally on the main part:
# src/g_sheets_api.cr
require "google-auth"
require "./book"
require "./sheets_api_client"
module GSheetsApi
VERSION = "0.1.0"
sheet_id = "13DLw6dqN6Hab258k8FdjUnOpuwX0FazTPz0OTKX-YT8"
sheet_name = "books"
client = SheetsApiClient.new(
sheet_id: sheet_id,
sheet_name: sheet_name,
start_col: "A",
end_col: "D",
)
new_book = Book.new(
row_index: 6, # it doesn't matter, it will be appended after the last row
title: "Into Programming",
author: "Jane Doe",
publication_year: 2024,
read: false,
)
response = client.add_row(new_book.to_payload)
if response
p response
puts "Book added successfully!"
else
puts "Failed to add book."
end
# Fetch all books
books = client.all_rows
if books.empty?
puts "No books found or error occurred."
else
puts "Books in sheet:"
books.each { |book| puts "#{book.title} by #{book.author} (#{book.publication_year}) - Read: #{book.read}" }
end
# Update the first book if exists
if books.size > 0
first_book = books.first
first_book.read = true
update_response = client.update_row(first_book.row_index, first_book.to_payload)
if update_response
puts "First book marked as read."
else
puts "Failed to update the first book."
end
end
# Delete the last book if exists
if books.size > 0
second_book = books[1]
delete_response = client.delete_row(second_book.row_index)
if delete_response
puts "Last book deleted."
else
puts "Failed to delete the last book."
end
end
end
Conclusion
Integrating Google Sheets with Crystal is straightforward once you have authentication and API requests set up. With a simple API client and a data model like Book, you can automate reading and writing spreadsheet data for your projects. Experiment with these tools to build your own workflows and extend the functionality as needed!
