Generate CSV files from a Google Docs spreadsheet - Githubissues

codeforamerica / ohana-api

The open source API directory of community social services.

http://ohana-api-demo.herokuapp.com/api

BSD 3-Clause "New" or "Revised" License

185 stars 344 forks source link

Generate CSV files from a Google Docs spreadsheet #281

Closed pmackay closed 9 years ago

pmackay commented 9 years ago

Is there any tool or guidance on creating a set of CSV files from a GDocs spreadsheet such as the suggested template?

https://docs.google.com/spreadsheets/d/1qkysLj5MAAvaysuac_9H0hOvTAb1aJEMhUVJAECmj2c/edit#gid=595985385

monfresh commented 9 years ago

I've added instructions to the template. Let me know if that answers your question.

pmackay commented 9 years ago

Hi, sounds good. Also I've been playing around a bit and knocked up this Rakefile to generate the files:

require 'net/http'

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

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

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

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

end

Should be fine if people copy your template sheet (to maintain the IDs of the tabs).

monfresh commented 9 years ago

That looks helpful! Could you create a new Wiki page with that script and instructions for running it, assuming you've tested that it works with a copy of our template?

pmackay commented 9 years ago

I could - might it be useful to integrate directly as a rake task?

monfresh commented 9 years ago

Sure, as long as it includes tests and is written in an object-oriented manner, similar to our import scripts. I might not have the time to review your pull request in a timely manner, so I thought it would be good to at least have a Wiki in the meantime.

pmackay commented 9 years ago

OK I've setup https://github.com/codeforamerica/ohana-api/wiki/Creating-CSV-files-from-an-OpenReferral-spreadsheet. Comments welcome.

monfresh commented 9 years ago

The script is not working for me. The content of all the generated CSV files looks like this:

HTML>
<HEAD>
<TITLE>Moved Temporarily</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
<H1>Moved Temporarily</H1>
The document has moved <A HREF="https://docs.google.com/spreadsheets/d/export/edit?format=csv&amp;gid=487062027">here</A>.
</BODY>
</HTML>

My rake command looks like this:

rake ohana:csv[https://docs.google.com/spreadsheets/d/12HwS_GgreVKQcq_7UJ6njYDO8JnUrR0PuNp024MwJ9Q]

monfresh commented 9 years ago

It's because my sheet was set to private. The instructions should mention that you need to change the access settings to allow anyone with the link to view the spreadsheet.

monfresh commented 9 years ago

I've updated the Wiki article with more detailed instructions. Closing this now. Thanks for this contribution! Here's the updated article: https://github.com/codeforamerica/ohana-api/wiki/Automatically-generate-CSV-files-from-an-OpenReferral-Google-spreadsheet