{"id":2667,"date":"2011-01-03T14:00:32","date_gmt":"2011-01-03T14:00:32","guid":{"rendered":"http:\/\/osmeusapontamentos.com\/?p=2667"},"modified":"2011-01-03T14:00:32","modified_gmt":"2011-01-03T14:00:32","slug":"getting-scraped-parlamento-pt-data-into-google-spreadsheet","status":"publish","type":"post","link":"https:\/\/osmeusapontamentos.com\/index.php\/2011\/01\/03\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\/","title":{"rendered":"Getting scraped parlamento.pt data into google spreadsheet"},"content":{"rendered":"<p>In this opendata project we&#8217;re trying to make it easy for non-techie folks to collaborate and contribute to, and since spreadsheets are still the best way for them to get an idea of what data we already have and do some experiments with them (charts and other visualizations) I decided I should try to <a href=\"https:\/\/osmeusapontamentos.com\/index.php\/2010\/12\/30\/scraping-parlamento-pt\/\">connect the couchdb database I created to google spreadsheets<\/a>.<\/p>\n<p>Like I&#8217;ve said in the previous post I&#8217;m now using couchdb to store the scraped data. This is because I get for free revisions and also because of the way it exposes data to world using JSON.<\/p>\n<p>For now I&#8217;m using couchone and you can <a href=\"http:\/\/parlamentopt.couchone.com\/_utils\/database.html?registointeresses\">see here one of the databases<\/a>.<br \/>\nI&#8217;ve created a view to retrieve the information of one member of parliament that I can call using the following url http:\/\/parlamentopt.couchone.com\/registointeresses\/_design\/viewer\/_view\/by_mpid?key=3<br \/>\n<img decoding=\"async\" src=\"\/img\/opendata_json.png\" alt=\"\" \/><\/p>\n<p>One of the nice things with JSON is that I can slap it into a google spreadsheet almost without any effort.<\/p>\n<p>In fact if the JSON is simple enough (with no hierarchies) using the importData built-in function of the google spreadsheet I get a nice one attribute \/ one column mapping that works just fine.<br \/>\nBut the JSON returned from couchdb is not that straightforward so I had to built a custom function inside of google spreadsheet to do it.<\/p>\n<p>Using <a href=\"http:\/\/code.google.com\/googleapps\/appsscript\/class_utilities.html#jsonParse\">Utilities.jsonParse<\/a>, <a href=\"http:\/\/code.google.com\/googleapps\/appsscript\/class_urlfetchapp.html\">UrlFetchApp<\/a> and some methods from the <a href=\"http:\/\/code.google.com\/googleapps\/appsscript\/service_spreadsheet.html\">Spreasheet Service<\/a> I ended up with this code that does what I wanted.<br \/>\n<img decoding=\"async\" src=\"\/img\/opendata_googleFunction.png\" alt=\"\" \/><\/p>\n<p>And if I wan&#8217;t to I can always <a href=\"http:\/\/code.google.com\/googleapps\/appsscript\/articles\/docslist_tutorial.html#section3\">export it from Google Spreadsheet to CSV<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this opendata project we&#8217;re trying to make it easy for non-techie folks to collaborate and contribute to, and since spreadsheets are still the best way for them to get an idea of what data we already have and do some experiments with them (charts and other visualizations) I decided I should try to connect [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[168,280,592],"class_list":["post-2667","post","type-post","status-publish","format-standard","hentry","category-ler-ver-ouvir-passear","tag-couchdb","tag-google-spreadsheet","tag-scraping"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.6 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Getting scraped parlamento.pt data into google spreadsheet - Os Meus Apontamentos<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/osmeusapontamentos.com\/index.php\/2011\/01\/03\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\/\" \/>\n<meta property=\"og:locale\" content=\"pt_PT\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Getting scraped parlamento.pt data into google spreadsheet - Os Meus Apontamentos\" \/>\n<meta property=\"og:description\" content=\"In this opendata project we&#8217;re trying to make it easy for non-techie folks to collaborate and contribute to, and since spreadsheets are still the best way for them to get an idea of what data we already have and do some experiments with them (charts and other visualizations) I decided I should try to connect [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/osmeusapontamentos.com\/index.php\/2011\/01\/03\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\/\" \/>\n<meta property=\"og:site_name\" content=\"Os Meus Apontamentos\" \/>\n<meta property=\"article:published_time\" content=\"2011-01-03T14:00:32+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/osmeusapontamentos.com\/img\/opendata_json.png\" \/>\n<meta name=\"author\" content=\"Vitor Silva\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@vitorsilva\" \/>\n<meta name=\"twitter:site\" content=\"@vitorsilva\" \/>\n<meta name=\"twitter:label1\" content=\"Escrito por\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vitor Silva\" \/>\n\t<meta name=\"twitter:label2\" content=\"Tempo estimado de leitura\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minuto\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/osmeusapontamentos.com\\\/index.php\\\/2011\\\/01\\\/03\\\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/osmeusapontamentos.com\\\/index.php\\\/2011\\\/01\\\/03\\\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\\\/\"},\"author\":{\"name\":\"Vitor Silva\",\"@id\":\"https:\\\/\\\/osmeusapontamentos.com\\\/#\\\/schema\\\/person\\\/d508df9c3ffc8b4e64a18dbf0ba18dd8\"},\"headline\":\"Getting scraped parlamento.pt data into google spreadsheet\",\"datePublished\":\"2011-01-03T14:00:32+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/osmeusapontamentos.com\\\/index.php\\\/2011\\\/01\\\/03\\\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\\\/\"},\"wordCount\":270,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/osmeusapontamentos.com\\\/#\\\/schema\\\/person\\\/d508df9c3ffc8b4e64a18dbf0ba18dd8\"},\"keywords\":[\"couchdb\",\"google spreadsheet\",\"scraping\"],\"articleSection\":[\"Ler\\\/ Ver\\\/ Ouvir\\\/ Passear\"],\"inLanguage\":\"pt-PT\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/osmeusapontamentos.com\\\/index.php\\\/2011\\\/01\\\/03\\\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/osmeusapontamentos.com\\\/index.php\\\/2011\\\/01\\\/03\\\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\\\/\",\"url\":\"https:\\\/\\\/osmeusapontamentos.com\\\/index.php\\\/2011\\\/01\\\/03\\\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\\\/\",\"name\":\"Getting scraped parlamento.pt data into google spreadsheet - Os Meus Apontamentos\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/osmeusapontamentos.com\\\/#website\"},\"datePublished\":\"2011-01-03T14:00:32+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/osmeusapontamentos.com\\\/index.php\\\/2011\\\/01\\\/03\\\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\\\/#breadcrumb\"},\"inLanguage\":\"pt-PT\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/osmeusapontamentos.com\\\/index.php\\\/2011\\\/01\\\/03\\\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/osmeusapontamentos.com\\\/index.php\\\/2011\\\/01\\\/03\\\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"In\u00edcio\",\"item\":\"https:\\\/\\\/osmeusapontamentos.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Getting scraped parlamento.pt data into google spreadsheet\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/osmeusapontamentos.com\\\/#website\",\"url\":\"https:\\\/\\\/osmeusapontamentos.com\\\/\",\"name\":\"Os Meus Apontamentos\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\\\/\\\/osmeusapontamentos.com\\\/#\\\/schema\\\/person\\\/d508df9c3ffc8b4e64a18dbf0ba18dd8\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/osmeusapontamentos.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"pt-PT\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/osmeusapontamentos.com\\\/#\\\/schema\\\/person\\\/d508df9c3ffc8b4e64a18dbf0ba18dd8\",\"name\":\"Vitor Silva\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"pt-PT\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f81f58ad909e8a213ab0a690f6ed65e5c0e0e2274bf35ac49ff31d7988d483ce?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f81f58ad909e8a213ab0a690f6ed65e5c0e0e2274bf35ac49ff31d7988d483ce?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f81f58ad909e8a213ab0a690f6ed65e5c0e0e2274bf35ac49ff31d7988d483ce?s=96&d=mm&r=g\",\"caption\":\"Vitor Silva\"},\"logo\":{\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f81f58ad909e8a213ab0a690f6ed65e5c0e0e2274bf35ac49ff31d7988d483ce?s=96&d=mm&r=g\"},\"sameAs\":[\"https:\\\/\\\/www.linkedin.com\\\/in\\\/vitormrsilva\",\"https:\\\/\\\/x.com\\\/vitorsilva\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Getting scraped parlamento.pt data into google spreadsheet - Os Meus Apontamentos","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/osmeusapontamentos.com\/index.php\/2011\/01\/03\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\/","og_locale":"pt_PT","og_type":"article","og_title":"Getting scraped parlamento.pt data into google spreadsheet - Os Meus Apontamentos","og_description":"In this opendata project we&#8217;re trying to make it easy for non-techie folks to collaborate and contribute to, and since spreadsheets are still the best way for them to get an idea of what data we already have and do some experiments with them (charts and other visualizations) I decided I should try to connect [&hellip;]","og_url":"https:\/\/osmeusapontamentos.com\/index.php\/2011\/01\/03\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\/","og_site_name":"Os Meus Apontamentos","article_published_time":"2011-01-03T14:00:32+00:00","og_image":[{"url":"https:\/\/osmeusapontamentos.com\/img\/opendata_json.png","type":"","width":"","height":""}],"author":"Vitor Silva","twitter_card":"summary_large_image","twitter_creator":"@vitorsilva","twitter_site":"@vitorsilva","twitter_misc":{"Escrito por":"Vitor Silva","Tempo estimado de leitura":"1 minuto"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/osmeusapontamentos.com\/index.php\/2011\/01\/03\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\/#article","isPartOf":{"@id":"https:\/\/osmeusapontamentos.com\/index.php\/2011\/01\/03\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\/"},"author":{"name":"Vitor Silva","@id":"https:\/\/osmeusapontamentos.com\/#\/schema\/person\/d508df9c3ffc8b4e64a18dbf0ba18dd8"},"headline":"Getting scraped parlamento.pt data into google spreadsheet","datePublished":"2011-01-03T14:00:32+00:00","mainEntityOfPage":{"@id":"https:\/\/osmeusapontamentos.com\/index.php\/2011\/01\/03\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\/"},"wordCount":270,"commentCount":0,"publisher":{"@id":"https:\/\/osmeusapontamentos.com\/#\/schema\/person\/d508df9c3ffc8b4e64a18dbf0ba18dd8"},"keywords":["couchdb","google spreadsheet","scraping"],"articleSection":["Ler\/ Ver\/ Ouvir\/ Passear"],"inLanguage":"pt-PT","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/osmeusapontamentos.com\/index.php\/2011\/01\/03\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/osmeusapontamentos.com\/index.php\/2011\/01\/03\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\/","url":"https:\/\/osmeusapontamentos.com\/index.php\/2011\/01\/03\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\/","name":"Getting scraped parlamento.pt data into google spreadsheet - Os Meus Apontamentos","isPartOf":{"@id":"https:\/\/osmeusapontamentos.com\/#website"},"datePublished":"2011-01-03T14:00:32+00:00","breadcrumb":{"@id":"https:\/\/osmeusapontamentos.com\/index.php\/2011\/01\/03\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\/#breadcrumb"},"inLanguage":"pt-PT","potentialAction":[{"@type":"ReadAction","target":["https:\/\/osmeusapontamentos.com\/index.php\/2011\/01\/03\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/osmeusapontamentos.com\/index.php\/2011\/01\/03\/getting-scraped-parlamento-pt-data-into-google-spreadsheet\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"In\u00edcio","item":"https:\/\/osmeusapontamentos.com\/"},{"@type":"ListItem","position":2,"name":"Getting scraped parlamento.pt data into google spreadsheet"}]},{"@type":"WebSite","@id":"https:\/\/osmeusapontamentos.com\/#website","url":"https:\/\/osmeusapontamentos.com\/","name":"Os Meus Apontamentos","description":"","publisher":{"@id":"https:\/\/osmeusapontamentos.com\/#\/schema\/person\/d508df9c3ffc8b4e64a18dbf0ba18dd8"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/osmeusapontamentos.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"pt-PT"},{"@type":["Person","Organization"],"@id":"https:\/\/osmeusapontamentos.com\/#\/schema\/person\/d508df9c3ffc8b4e64a18dbf0ba18dd8","name":"Vitor Silva","image":{"@type":"ImageObject","inLanguage":"pt-PT","@id":"https:\/\/secure.gravatar.com\/avatar\/f81f58ad909e8a213ab0a690f6ed65e5c0e0e2274bf35ac49ff31d7988d483ce?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f81f58ad909e8a213ab0a690f6ed65e5c0e0e2274bf35ac49ff31d7988d483ce?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f81f58ad909e8a213ab0a690f6ed65e5c0e0e2274bf35ac49ff31d7988d483ce?s=96&d=mm&r=g","caption":"Vitor Silva"},"logo":{"@id":"https:\/\/secure.gravatar.com\/avatar\/f81f58ad909e8a213ab0a690f6ed65e5c0e0e2274bf35ac49ff31d7988d483ce?s=96&d=mm&r=g"},"sameAs":["https:\/\/www.linkedin.com\/in\/vitormrsilva","https:\/\/x.com\/vitorsilva"]}]}},"_links":{"self":[{"href":"https:\/\/osmeusapontamentos.com\/index.php\/wp-json\/wp\/v2\/posts\/2667","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/osmeusapontamentos.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/osmeusapontamentos.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/osmeusapontamentos.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/osmeusapontamentos.com\/index.php\/wp-json\/wp\/v2\/comments?post=2667"}],"version-history":[{"count":0,"href":"https:\/\/osmeusapontamentos.com\/index.php\/wp-json\/wp\/v2\/posts\/2667\/revisions"}],"wp:attachment":[{"href":"https:\/\/osmeusapontamentos.com\/index.php\/wp-json\/wp\/v2\/media?parent=2667"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/osmeusapontamentos.com\/index.php\/wp-json\/wp\/v2\/categories?post=2667"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/osmeusapontamentos.com\/index.php\/wp-json\/wp\/v2\/tags?post=2667"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}