{"id":2445,"date":"2020-05-16T13:00:00","date_gmt":"2020-05-16T12:00:00","guid":{"rendered":"https:\/\/chewett.co.uk\/blog\/?p=2445"},"modified":"2020-05-08T12:19:53","modified_gmt":"2020-05-08T11:19:53","slug":"how-to-use-an-api-to-download-a-google-docs-sheets-spreadsheet-as-csv","status":"publish","type":"post","link":"https:\/\/chewett.co.uk\/blog\/2445\/how-to-use-an-api-to-download-a-google-docs-sheets-spreadsheet-as-csv\/","title":{"rendered":"How to use an API to Download a Google Docs Sheets Spreadsheet as CSV"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"678\" height=\"254\" data-attachment-id=\"2452\" data-permalink=\"https:\/\/chewett.co.uk\/blog\/2445\/how-to-use-an-api-to-download-a-google-docs-sheets-spreadsheet-as-csv\/downloading_googledocs_sheets\/\" data-orig-file=\"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2020\/05\/downloading_googledocs_sheets.jpg?fit=800%2C300&amp;ssl=1\" data-orig-size=\"800,300\" data-comments-opened=\"1\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"downloading_googledocs_sheets\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2020\/05\/downloading_googledocs_sheets.jpg?fit=300%2C113&amp;ssl=1\" data-large-file=\"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2020\/05\/downloading_googledocs_sheets.jpg?fit=678%2C254&amp;ssl=1\" src=\"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2020\/05\/downloading_googledocs_sheets.jpg?resize=678%2C254&#038;ssl=1\" alt=\"\" class=\"wp-image-2452\" srcset=\"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2020\/05\/downloading_googledocs_sheets.jpg?w=800&amp;ssl=1 800w, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2020\/05\/downloading_googledocs_sheets.jpg?resize=300%2C113&amp;ssl=1 300w, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2020\/05\/downloading_googledocs_sheets.jpg?resize=768%2C288&amp;ssl=1 768w, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2020\/05\/downloading_googledocs_sheets.jpg?resize=50%2C19&amp;ssl=1 50w\" sizes=\"auto, (max-width: 678px) 100vw, 678px\" \/><\/figure>\n\n\n\n<p>This blog talks about how you can use a little known Google Docs Sheets API to download spreadsheets as a CSV.<\/p>\n\n\n\n<!--more-->\n\n\n\n<h2 class=\"wp-block-heading\">Obtaining the documents Sheet ID<\/h2>\n\n\n\n<p>Before the spreadsheet can be downloaded you will first need to find the Sheet ID. This is a string made up of letters and numbers that uniquely identifies the spreadsheet.<\/p>\n\n\n\n<p>For a Google Docs Spreadsheet the URL will be in the form:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">https:\/\/docs.google.com\/spreadsheets\/d\/{SHEET_ID}\/edit<\/pre>\n\n\n\n<p>You will need to take the ID from this to use it with the download API.<\/p>\n\n\n\n<p>For example, the Pokemon Go Nesting data spreadsheet has the following URL<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">https:\/\/docs.google.com\/spreadsheets\/d\/1wlfCjE0gu1lCsNMEfv_81gUpoVdK80367slCBW_JoDU\/edit<\/pre>\n\n\n\n<p>From this we can obtain the sheet ID which is<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">1wlfCjE0gu1lCsNMEfv_81gUpoVdK80367slCBW_JoDU<\/pre>\n\n\n\n<p>The second piece of information that is needed is the name of the sheet you want to download. Since CSV can only hold a single page at a time you need to get the name of the Sheet. This is the name at the bottom of the page.<\/p>\n\n\n\n<p>Now we are able to construct the download link<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Constructing the CSV download link<\/h2>\n\n\n\n<p>Taking the Sheet ID above, we can enter it into the download link below<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">https:\/\/docs.google.com\/spreadsheets\/d\/{SHEET_ID}\/gviz\/tq?tqx=out:csv&amp;sheet={SHEET_NAME}<\/pre>\n\n\n\n<p>This means that if I wish to export the above spreadsheet as CSV, downloading the &#8220;Nest&#8221; sheet I can create the following URL.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">https:\/\/docs.google.com\/spreadsheets\/d\/1wlfCjE0gu1lCsNMEfv_81gUpoVdK80367slCBW_JoDU\/gviz\/tq?tqx=out:csv&amp;sheet=Nest<\/pre>\n\n\n\n<p>Some of the formatting will be lost when the CSV is exported but this allows programmatic conversion of the Google Docs Sheets spreadsheet to CSV.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This blog talks about how you can use a little known Google Docs Sheets API to download spreadsheets as a CSV.<\/p>\n","protected":false},"author":1,"featured_media":2450,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"Today I am writing about how to use an API to Download a #GoogleDocs Sheets Spreadsheet as #CSV","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[5],"tags":[226,376,378,379,377,380],"class_list":["post-2445","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-informational","tag-api","tag-google","tag-google-docs","tag-google-docs-sheets","tag-google-documents","tag-spreadsheet"],"wppr_data":{"cwp_meta_box_check":"No"},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2020\/05\/downloading_googledocs_sheets_posticon.jpg?fit=800%2C800&ssl=1","jetpack_shortlink":"https:\/\/wp.me\/p2toWX-Dr","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":2227,"url":"https:\/\/chewett.co.uk\/blog\/2227\/exporting-points-of-interest-from-ingress\/","url_meta":{"origin":2445,"position":0},"title":"Exporting Points of Interest from Ingress","author":"Chewett","date":"June 15, 2019","format":false,"excerpt":"This blog post talks about how you can export portal data from Ingress using browser addons. What data can you get from Ingress? The Ingress map allows you to see the current state of the world in Ingress. This shows both the state of all the portals and the links\u2026","rel":"","context":"In &quot;Informational&quot;","block_context":{"text":"Informational","link":"https:\/\/chewett.co.uk\/blog\/category\/informational\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2019\/06\/ingress_exporting_poi.jpg?fit=800%2C800&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2019\/06\/ingress_exporting_poi.jpg?fit=800%2C800&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2019\/06\/ingress_exporting_poi.jpg?fit=800%2C800&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2019\/06\/ingress_exporting_poi.jpg?fit=800%2C800&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":1540,"url":"https:\/\/chewett.co.uk\/blog\/1540\/pokemon-go-api-possible-ditto-pokemon\/","url_meta":{"origin":2445,"position":1},"title":"Pokemon Go API \u2013 Possible Ditto Pokemon","author":"Chewett","date":"September 26, 2018","format":false,"excerpt":"This post talks about the newest Pokemon Go API, the possible ditto API at\u00a0pogoapi.net. How to catch Ditto Ditto is a Pokemon that can transform into any Pokemon. In the wild you find him by catching a Pokemon which then transforms into Ditto. You can't tell which Pokemon might turn\u2026","rel":"","context":"In &quot;Software&quot;","block_context":{"text":"Software","link":"https:\/\/chewett.co.uk\/blog\/category\/software\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/09\/pogo_api_possible_ditto-1.jpg?fit=800%2C800&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/09\/pogo_api_possible_ditto-1.jpg?fit=800%2C800&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/09\/pogo_api_possible_ditto-1.jpg?fit=800%2C800&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/09\/pogo_api_possible_ditto-1.jpg?fit=800%2C800&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":2921,"url":"https:\/\/chewett.co.uk\/blog\/2921\/pokemon-go-api-time-limited-shiny-pokemon-api\/","url_meta":{"origin":2445,"position":2},"title":"Pokemon Go API &#8211; Time Limited Shiny Pokemon API","author":"Chewett","date":"May 22, 2021","format":false,"excerpt":"This post talks about the newest Pokemon Go API, the Time Limited Shiny Pokemon API on PoGoAPI.net. What are time limited shiny Pokemon? In Pokemon Go some Pokemon can only be found as shiny during special events or times of the year. Pokemon Go will announce when these are available\u2026","rel":"","context":"In &quot;Software&quot;","block_context":{"text":"Software","link":"https:\/\/chewett.co.uk\/blog\/category\/software\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/05\/pogoapi_timelimited_shiny_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/05\/pogoapi_timelimited_shiny_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/05\/pogoapi_timelimited_shiny_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/05\/pogoapi_timelimited_shiny_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/05\/pogoapi_timelimited_shiny_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":2804,"url":"https:\/\/chewett.co.uk\/blog\/2804\/pokemon-go-api-baby-pokemon-api\/","url_meta":{"origin":2445,"position":3},"title":"Pokemon Go API &#8211; Baby Pokemon API","author":"Chewett","date":"February 13, 2021","format":false,"excerpt":"This post talks about the latest API I have added to PoGoAPI.net, the Baby Pokemon API. What are Baby Pokemon Some Pokemon are only available from eggs and are typically smaller versions of \"grown up\" Pokemon. These are called baby Pokemon and the only way to get them is typically\u2026","rel":"","context":"In &quot;Software&quot;","block_context":{"text":"Software","link":"https:\/\/chewett.co.uk\/blog\/category\/software\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/02\/pogoapi_baby_pokemon_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/02\/pogoapi_baby_pokemon_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/02\/pogoapi_baby_pokemon_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/02\/pogoapi_baby_pokemon_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/02\/pogoapi_baby_pokemon_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":1487,"url":"https:\/\/chewett.co.uk\/blog\/1487\/pokemon-go-api-alolan-pokemon-list\/","url_meta":{"origin":2445,"position":4},"title":"Pokemon Go API \u2013 Alolan Pokemon List","author":"Chewett","date":"September 1, 2018","format":false,"excerpt":"This post talks about the latest API I have added to PoGoAPI.net, the Alolan Pokemon list. What the Alolan Pokemon API can be used for In Generation 7 of the Pokemon video games Alolan Pokemon were released. These are pokemon who canonically evolved in a different area so are subtly\u2026","rel":"","context":"In &quot;Software&quot;","block_context":{"text":"Software","link":"https:\/\/chewett.co.uk\/blog\/category\/software\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/08\/pogo_api_alolan_pokemon-1.jpg?fit=800%2C800&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/08\/pogo_api_alolan_pokemon-1.jpg?fit=800%2C800&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/08\/pogo_api_alolan_pokemon-1.jpg?fit=800%2C800&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/08\/pogo_api_alolan_pokemon-1.jpg?fit=800%2C800&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":2478,"url":"https:\/\/chewett.co.uk\/blog\/2478\/pokemon-go-api-galarian-pokemon-list\/","url_meta":{"origin":2445,"position":5},"title":"Pokemon Go API \u2013 Galarian Pokemon List","author":"Chewett","date":"June 6, 2020","format":false,"excerpt":"This post talks about the latest API I have added to PoGoAPI.net, the Galarian Pokemon list. What the Galarian Pokemon API can be used for In Generation 8 of the Pokemon video games Galarian Pokemon were released. These are Pokemon who canonically evolved in a different area so are subtly\u2026","rel":"","context":"In &quot;Software&quot;","block_context":{"text":"Software","link":"https:\/\/chewett.co.uk\/blog\/category\/software\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2020\/06\/pogo_api_posticon_galarian_pokemon.jpg?fit=800%2C800&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2020\/06\/pogo_api_posticon_galarian_pokemon.jpg?fit=800%2C800&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2020\/06\/pogo_api_posticon_galarian_pokemon.jpg?fit=800%2C800&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2020\/06\/pogo_api_posticon_galarian_pokemon.jpg?fit=800%2C800&ssl=1&resize=700%2C400 2x"},"classes":[]}],"_links":{"self":[{"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/posts\/2445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/comments?post=2445"}],"version-history":[{"count":1,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/posts\/2445\/revisions"}],"predecessor-version":[{"id":2453,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/posts\/2445\/revisions\/2453"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/media\/2450"}],"wp:attachment":[{"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/media?parent=2445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/categories?post=2445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/tags?post=2445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}