This method will help you to download data from Google Sheet as CSV to Rails public folder (or any other folder) and parse it as array of hashes.
This is pure Ruby implementation. No gems. Just a single file.
Data in the Google Sheet is updating live automatically.
It's working on Heroku.
- Copy the following code and save it as
google_sheet.rb
to/app/models/
(if you are using Rails)
class GoogleSheet
require 'open-uri'
require 'csv'
def self.get url
csv_file = "#{Rails.root}/public/data.csv"
# save csv file from url
open(url) {|f| File.open(csv_file,"wb") {|file| file.write f.read}}
data = []
# parse each csv row
CSV.open(csv_file, headers: true).each{|row| data << row.to_hash}
return data
end
end
- Usage:
url = "https://docs.google.com/spreadsheets/d/e/2PACX-1vSf0VkQ9iCihfZqlNDg04GTso_seU8Pom53YtPjGc6PrUlJ1bY9b6WHARo2MQFJnLbrt4P7-PlnNm4_/pub?gid=0&single=true&output=csv"
data = GoogleSheet.get url
- Open your Google Sheet e.g. this one
- File -> Share -> Publish to the web
- Entire Document -> Sheet1
- Web page -> Comma-separated values (.csv)
- Share
- Copy the url that should look like this: https://docs.google.com/spreadsheets/d/e/2PACX-1vSf0VkQ9iCihfZqlNDg04GTso_seU8Pom53YtPjGc6PrUlJ1bY9b6WHARo2MQFJnLbrt4P7-PlnNm4_/pub?gid=0&single=true&output=csv
- If you are not using Ruby on Rails change csv file path to your:
csv_file = "#{Rails.root}/public/data.csv"
- if your CSV doesn't have headers change
headers: false
- If you want to have JSON output use
render json: data
in your controller (Ruby on Rails only).