{"id":2383,"date":"2021-05-25T17:51:35","date_gmt":"2021-05-25T16:51:35","guid":{"rendered":"https:\/\/www.jurecuhalev.com\/blog\/?p=2383"},"modified":"2021-05-29T20:21:52","modified_gmt":"2021-05-29T19:21:52","slug":"google-sheets-in-python-with-gspread","status":"publish","type":"post","link":"https:\/\/www.jurecuhalev.com\/blog\/google-sheets-in-python-with-gspread\/","title":{"rendered":"Google Sheets in Python with gspread"},"content":{"rendered":"\n<p>As I build more back-office web interfaces I notice that users feel most comfortable in an Excel-like interface. That&#8217;s why it&#8217;s now so common to find data being edited and exchanged Google Sheets.<\/p>\n\n\n\n<p>This got me wondering &#8211; how do I access, manipulate and write to Sheets from Python. I like the answer that I found &#8211; a library called&nbsp;<em><a href=\"https:\/\/docs.gspread.org\/en\/latest\/\">gspread<\/a><\/em>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Basic usage<\/h2>\n\n\n\n<p>A high-level overview of how you use it:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Create a service account in Google Developer Console<\/li><li>Share your Google Sheet to that special email address<\/li><li>You can now access it from python with&nbsp;<em>gspread<\/em>&nbsp;and related libraries<\/li><\/ol>\n\n\n\n<p><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"575\" src=\"https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.005-edited.png\" alt=\"\" class=\"wp-image-2385\" srcset=\"https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.005-edited.png 1024w, https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.005-edited-550x309.png 550w, https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.005-edited-768x431.png 768w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Making it even better with Pandas<\/h2>\n\n\n\n<p>While it&#8217;s great to have low-level access in Python it&#8217;s much more convenient if I can manipulate the data inside Pandas DataFrame. That way I don&#8217;t have to think about data structures and how to correctly represent data in each cell or row.<\/p>\n\n\n\n<p>To do this I found two libraries:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><a href=\"https:\/\/pypi.org\/project\/gspread-dataframe\/\">gspread-dataframe<\/a><\/li><li><a href=\"https:\/\/pypi.org\/project\/gspread-pandas\/\">gspread-pandas<\/a><\/li><\/ul>\n\n\n\n<p>They&#8217;re both similar and you essentially just import a snippet:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"575\" src=\"https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.008-edited.png\" alt=\"\" class=\"wp-image-2387\" srcset=\"https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.008-edited.png 1024w, https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.008-edited-550x309.png 550w, https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.008-edited-768x431.png 768w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>For a larger project, I used gspread-pandas (just because I found it first) and it gives you quite a lot of control over how and when you update the data.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Sheets and Data Validation<\/h2>\n\n\n\n<p>Sheets allow you to define data validation checks on specific cells. You can validate against a predefined list of items or reference a range of cells. This allowed me to build an elaborate export of data from an API and provide users a way to quickly review the data and also possibly use Sheets to update the data.<\/p>\n\n\n\n<p>To manipulate Sheets data validation from Python you can use <a href=\"https:\/\/pypi.org\/project\/gspread-formatting\/\" data-type=\"URL\" data-id=\"https:\/\/pypi.org\/project\/gspread-formatting\/\">gspread-formatting<\/a> library.<\/p>\n\n\n\n<figure class=\"wp-block-gallery columns-1 is-cropped wp-block-gallery-1 is-layout-flex wp-block-gallery-is-layout-flex\"><ul class=\"blocks-gallery-grid\"><li class=\"blocks-gallery-item\"><figure><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"768\" src=\"https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.010.png\" alt=\"\" data-id=\"2388\" data-full-url=\"https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.010.png\" data-link=\"https:\/\/www.jurecuhalev.com\/blog\/?attachment_id=2388\" class=\"wp-image-2388\" srcset=\"https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.010.png 1024w, https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.010-550x413.png 550w, https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.010-768x576.png 768w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure><\/li><li class=\"blocks-gallery-item\"><figure><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"768\" src=\"https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.011.png\" alt=\"\" data-id=\"2389\" data-full-url=\"https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.011.png\" data-link=\"https:\/\/www.jurecuhalev.com\/blog\/?attachment_id=2389\" class=\"wp-image-2389\" srcset=\"https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.011.png 1024w, https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.011-550x413.png 550w, https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.011-768x576.png 768w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure><\/li><li class=\"blocks-gallery-item\"><figure><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"768\" src=\"https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.012.png\" alt=\"\" data-id=\"2390\" data-full-url=\"https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.012.png\" data-link=\"https:\/\/www.jurecuhalev.com\/blog\/?attachment_id=2390\" class=\"wp-image-2390\" srcset=\"https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.012.png 1024w, https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.012-550x413.png 550w, https:\/\/www.jurecuhalev.com\/blog\/wp-content\/uploads\/2021\/05\/GSpreadPyLjubljana.012-768x576.png 768w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure><\/li><\/ul><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Video and Slides of Talk on this topic<\/h2>\n\n\n\n<p>If you&#8217;d like to see slides from my talk on this Subject at the Python Ljubljana meetup group I&#8217;ve embedded them below.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-4-3 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"DragonPy Meetup May 2021 Jure \u010cuhalev Google Sheets in Python with GSpread\" width=\"500\" height=\"375\" src=\"https:\/\/www.youtube.com\/embed\/Qk5L07M72Lc?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<figure class=\"wp-block-embed is-type-rich is-provider-slideshare wp-block-embed-slideshare wp-embed-aspect-1-1 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Google Sheets in Python with gspread\" src=\"https:\/\/www.slideshare.net\/slideshow\/embed_code\/key\/EN3Lyb9cstQ7b6\" width=\"427\" height=\"356\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\" style=\"border:1px solid #CCC; border-width:1px; margin-bottom:5px; max-width: 100%;\" allowfullscreen> <\/iframe> <div style=\"margin-bottom:5px\"> <strong> <a href=\"https:\/\/www.slideshare.net\/gandalfar\/google-sheets-in-python-with-gspread\" title=\"Google Sheets in Python with gspread\" target=\"_blank\">Google Sheets in Python with gspread<\/a> <\/strong> from <strong><a href=\"https:\/\/www.slideshare.net\/gandalfar\" target=\"_blank\">Jure Cuhalev<\/a><\/strong> <\/div>\n<\/div><\/figure>\n\n\n\n<div class=\"wp-block-group has-light-brown-background-color has-background\"><div class=\"wp-block-group__inner-container is-layout-flow wp-block-group-is-layout-flow\">\n<h2 class=\"wp-block-heading\">Was this useful for you?<\/h2>\n\n\n\n<p>If this is useful to you, please leave a comment or <a href=\"mailto:jure@cuhalev.com\">send me an email<\/a>. I&#8217;ll be happy to write a more detailed tutorials and support you.<\/p>\n<\/div><\/div>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>As I build more back-office web interfaces I notice that users feel most comfortable in an Excel-like interface. That&#8217;s why it&#8217;s now so common to find data being edited and exchanged Google Sheets. This got me wondering &#8211; how do I access, manipulate and write to Sheets from Python. I like the answer that I [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[16],"tags":[],"class_list":["post-2383","post","type-post","status-publish","format-standard","hentry","category-ideas"],"acf":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.jurecuhalev.com\/blog\/wp-json\/wp\/v2\/posts\/2383","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.jurecuhalev.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jurecuhalev.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jurecuhalev.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jurecuhalev.com\/blog\/wp-json\/wp\/v2\/comments?post=2383"}],"version-history":[{"count":6,"href":"https:\/\/www.jurecuhalev.com\/blog\/wp-json\/wp\/v2\/posts\/2383\/revisions"}],"predecessor-version":[{"id":2402,"href":"https:\/\/www.jurecuhalev.com\/blog\/wp-json\/wp\/v2\/posts\/2383\/revisions\/2402"}],"wp:attachment":[{"href":"https:\/\/www.jurecuhalev.com\/blog\/wp-json\/wp\/v2\/media?parent=2383"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jurecuhalev.com\/blog\/wp-json\/wp\/v2\/categories?post=2383"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jurecuhalev.com\/blog\/wp-json\/wp\/v2\/tags?post=2383"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}