Automatic Google Sheets CSV Export

Google Sheets is a popular way to organize data for card game projects. It turns out there is a very simple way to automatically get the data from Sheets onto your computer so Squib can make use of it.

The first step is to make your Sheet viewable by anyone with a link so we can download the data in Ruby.

Click this button on the top right of your sheet:

Then set sharing to anyone can view:

There will be a URL below this setting. Copy this URL down for later.

NOTE This means your data is technically readable by anyone with a link, so be careful!

Next we are going to download the data in our Ruby code:

require 'open-uri'
 
google_sheet_id = "insert_id_here"
sheet_gid = "0"
 
buffer = open("https://docs.google.com/spreadsheets/d/#{google_sheet_id}/export?format=csv&gid=#{sheet_gid}").read
numCards = CSV.parse(buffer).length - 1 #CSV counts the header row; we only want the number of cards.
File.open("my_data.csv", 'wb') do |file|
    file << buffer
end

The last step is to fill in the google_sheet_id field and optionally, the sheet_gid field.

Remember that URL we copied earlier? It will be in this format:

https://docs.google.com/spreadsheets/d/long_id_here/edit?usp=sharing

Copy the long_id_here part into the google_sheet_id field in the Ruby code.

The sheet_gid is only needed if your Sheet has multiple sheets. If this is the case, select the sheet you want to work with in the Google Sheet and note the URL displayed in your browser:

https://docs.google.com/spreadsheets/d/long_id_here/edit#gid=long_number_here

Copy the long_number_here part into the sheet_gid field in the Ruby code and you will download that specific sheet.

At this point, if you run your code, you will see a new file in your working directory called "my_data.csv".

This is a normal CSV file that Squib can work with like normal.

Now you can edit your Google Sheet and then simply run Squib to update your cards without any intermediate step. Enjoy!