Automatically generate CSV files from a HSDS Google spreadsheet

To import data from a Human Services Data Specification (HSDS) spreadsheet, it is necessary to save the content out as a series of CSV files. The content of these files is described in Populating the Postgres database from OpenReferral compliant CSV files.

If you're using our Google Spreadsheets template, one way to generate the various CSV files is to make a copy of our template, then save each tab as a CSV file. A faster way is to use a script that automatically generates the CSV files from your Google Spreadsheet. Follow the steps below to use the script.

Steps

Spreadsheet preparation

• Open our Google Spreadsheet template in your browser.
• Sign in to your own Google account.
• From the Google Spreadsheets menu, select File -> Make a copy..., change the name if you want, then click OK.
• Copy the URL for your spreadsheet in your browser's URL bar
• Click on the Share button at the top right of your spreadsheet
• Click the Advanced link in the bottom right
• Under Who has access, click on the Change... link
• Click the middle radio button that corresponds to On - Anyone with the link
• Click the Save button, then click the Done button

Script preparation and execution

• Save the script below into a file called Rakefile in any folder on your computer.
• From inside the folder that contains your Rakefile, run rake ohana:csv[<link-to-Google-sheet>]. For example, if the URL of your spreadsheet is https://docs.google.com/spreadsheets/d/12HwS_GgreVKQcq_7UJ6njYDO8JnUrR0PuNp024MwJ9Q/edit#gid=595985385, you would run this command:

rake ohana:csv[https://docs.google.com/spreadsheets/d/12HwS_GgreVKQcq_7UJ6njYDO8JnUrR0PuNp024MwJ9Q/edit#gid=595985385]

When the script is done, you will find all your CSV files inside a folder called csv.

The Script

require 'net/http'

url_suffix = '/export?format=csv&gid='

TABS = {
  'organizations' => '0',
  'locations' => '515769274',
  'addresses' => '487062027',
  'mail_addresses' => '778957621',
  'contacts' => '817757409',
  'phones' => '245919930',
  'services' => '1942172348',
  'programs' => '1007817140',
  'regular_schedules' => '1099615328',
  'holiday_schedules' => '1983571683',
}


namespace :ohana do
  task :csv, :remote_path do |t, args|
    doc_path = args[:remote_path].rpartition('/')[0]

    # uri = URI(args[:remote_path])
    Dir.mkdir 'csv' if not File.exist?('csv')
    TABS.each do |k, tab|
      uri = URI(doc_path + url_suffix + tab)
      data = Net::HTTP.get(uri)
      out_path = File.join Dir.pwd, 'csv', k + '.csv'
      f = File.open out_path, 'w'
      f.write data
      f.close
    end
  end
end