{"id":1070,"date":"2017-03-12T22:41:51","date_gmt":"2017-03-12T22:41:51","guid":{"rendered":"http:\/\/intelligentonlinetools.com\/blog\/?p=1070"},"modified":"2017-11-30T02:08:55","modified_gmt":"2017-11-30T02:08:55","slug":"how-to-write-to-a-google-sheet-with-a-python-script","status":"publish","type":"post","link":"http:\/\/intelligentonlinetools.com\/blog\/2017\/03\/12\/how-to-write-to-a-google-sheet-with-a-python-script\/","title":{"rendered":"How to Write to a Google Sheet with a Python Script"},"content":{"rendered":"

My post How to Write to a Google Spreadsheet with a Perl Script<\/a> that was published some time ago is still getting a lot of visitors. This is not surprising as cloud computing is a fast-growing business. Below is the chart of number of searches for phrase “Google Sheet” from Google Trends site. The usage of Google Sheet looks increasing over the years. <\/p>\n

\"\"
“Google Sheet” trend, source Google Trends<\/figcaption><\/figure>\n

So now I decided to look how to use python for Google Sheet.<\/p>\n

My interest to this topic also come from the idea to have Adsense and Google Analytics data on the Google Drive and do analysis in python. Keeping files on Google Drive provides access to data from different computers or devices and allows sharing with other people.<\/p>\n

Setup<\/strong>
\nI found good post [2] that helped me quickly setup everything that is needed on Google Drive. <\/p>\n

Despite of good instructions I run into the following minor issues:
\nThe file on Google Drive should be shared with the email in secret key file. But this email was not the same email that I use for usual login to Google site. Google created some new email, that is starting with project name. Only when I shared the Google Sheet with that new email it started to work.<\/p>\n

The sheet name in the below statement for opening file, for some reason did not take upper case. The actual sheet name was “S<\/strong>heet1″ but the statement below worked with “s<\/strong>heet1″ only ( s in low case):
\nsheet = client.open(“filename”).sheet1<\/p>\n

Reading and Updating Google Sheet<\/strong>
\nHere is the small example of python code showing how to read or update Google Sheet.<\/p>\n

\r\nsheet.update_cell(1, 2, \"text 34\")\r\n\r\nfor i in range (2):\r\n    sheet.update_cell(1, i+3, i)\r\n\r\n# Returns a list of Cell objects from a specified range.\r\nar=sheet.range('A1:D1') \r\nfor obj in ar:\r\n    print (obj.value)\r\n<\/code><\/pre>\n
Once you get understanding how to do basic operation you can write more complicated code as you need. There are many benefits in using files in the cloud.<\/p>\n
Full python source code<\/strong><\/p>\n
\r\n# -*- coding: utf-8 -*-\r\nimport gspread\r\nfrom oauth2client.service_account import ServiceAccountCredentials\r\n\r\n\r\nscope = ['https:\/\/spreadsheets.google.com\/feeds']\r\ncreds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)\r\nclient = gspread.authorize(creds)\r\n\r\nsheet = client.open(\"File name\").sheet1\r\nsheet.update_cell(1, 2, \"text 34\")\r\n\r\nfor i in range (2):\r\n    sheet.update_cell(1, i+3, i)\r\n\r\n### Returns a list of Cell objects from a specified range.\r\nar=sheet.range('A1:D1') \r\nfor obj in ar:nprint (obj.value)\r\n<\/code><\/pre>\n
Accessing through Web<\/strong>
\nWe can use the same code to access Google Sheet through web.  Here is the test example with flask. Python script is navigating through 4 cells, counting the sum and displaying it on the web.  To access Google Sheet we used the same code as before.
\nBelow you can find screenshot of web page and Google Sheet.
\nTo run this example you need:
\n install flask
\n put this python file and client_secret.json file into the same flask folder
\n run this python script from command line
\n run web browser as on the screenshot.<\/p>\n
\"\"
Retrieving the content of Google Sheet through web page with python and  flask<\/figcaption><\/figure>\n
Below is the python computer code.<\/p>\n
\r\nfrom flask import Flask\r\napp = Flask(__name__)\r\n\r\n@app.route('\/')\r\ndef index():\r\n  return 'Hello World!'\r\n\r\n\r\n@app.route('\/user\/')\r\ndef user(name):\r\n\r\n   import gspread\r\n   from oauth2client.service_account import ServiceAccountCredentials\r\n   scope = ['https:\/\/spreadsheets.google.com\/feeds']\r\n   creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)\r\n   client = gspread.authorize(creds)\r\n\r\n   sheet = client.open(\"INSERT_REPORT_NAME_HERE\").sheet1\r\n   ar=sheet.range('A1:D1')\r\n   sum=0 \r\n   for obj in ar:\r\n       sum = sum + int(obj.value)\r\n   HTML_string='Hello, {}! Total = {}'\r\n   return HTML_string.format(name , str(sum))  \r\n\r\nif __name__ == '__main__':\r\n   app.run(debug=True)\r\n<\/code><\/pre>\n
References<\/strong>
\n1. How to Write to a Google Spreadsheet with a Perl Script<\/a>
\n2. Google Spreadsheets and Python<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
My post How to Write to a Google Spreadsheet with a Perl Script that was published some time ago is still getting a lot of visitors. This is not surprising as cloud computing is a fast-growing business. Below is the chart of number of searches for phrase “Google Sheet” from Google Trends site. The usage … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":[]},"categories":[10],"tags":[],"jetpack_publicize_connections":[],"yoast_head":"\nHow to Write to a Google Sheet with a Python Script - Machine Learning Applications<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/intelligentonlinetools.com\/blog\/2017\/03\/12\/how-to-write-to-a-google-sheet-with-a-python-script\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Write to a Google Sheet with a Python Script - Machine Learning Applications\" \/>\n<meta property=\"og:description\" content=\"My post How to Write to a Google Spreadsheet with a Perl Script that was published some time ago is still getting a lot of visitors. This is not surprising as cloud computing is a fast-growing business. Below is the chart of number of searches for phrase “Google Sheet” from Google Trends site. The usage ... Read more\" \/>\n<meta property=\"og:url\" content=\"http:\/\/intelligentonlinetools.com\/blog\/2017\/03\/12\/how-to-write-to-a-google-sheet-with-a-python-script\/\" \/>\n<meta property=\"og:site_name\" content=\"Machine Learning Applications\" \/>\n<meta property=\"article:published_time\" content=\"2017-03-12T22:41:51+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-11-30T02:08:55+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/intelligentonlinetools.com\/blog\/wp-content\/uploads\/2017\/03\/Google-Sheet-trend-from-google-trend-300x87.png\" \/>\n<meta name=\"author\" content=\"owygs156\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"owygs156\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/2017\/03\/12\/how-to-write-to-a-google-sheet-with-a-python-script\/\",\"url\":\"http:\/\/intelligentonlinetools.com\/blog\/2017\/03\/12\/how-to-write-to-a-google-sheet-with-a-python-script\/\",\"name\":\"How to Write to a Google Sheet with a Python Script - Machine Learning Applications\",\"isPartOf\":{\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/#website\"},\"datePublished\":\"2017-03-12T22:41:51+00:00\",\"dateModified\":\"2017-11-30T02:08:55+00:00\",\"author\":{\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/7a886dc5eb9758369af2f6d2cb342478\"},\"breadcrumb\":{\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/2017\/03\/12\/how-to-write-to-a-google-sheet-with-a-python-script\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/intelligentonlinetools.com\/blog\/2017\/03\/12\/how-to-write-to-a-google-sheet-with-a-python-script\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/2017\/03\/12\/how-to-write-to-a-google-sheet-with-a-python-script\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/intelligentonlinetools.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Write to a Google Sheet with a Python Script\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/#website\",\"url\":\"http:\/\/intelligentonlinetools.com\/blog\/\",\"name\":\"Machine Learning Applications\",\"description\":\"Artificial intelligence, data mining and machine learning for building web based tools and services.\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/intelligentonlinetools.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/7a886dc5eb9758369af2f6d2cb342478\",\"name\":\"owygs156\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/2.gravatar.com\/avatar\/b351def598609cb4c0b5bca26497c7e5?s=96&d=mm&r=g\",\"contentUrl\":\"http:\/\/2.gravatar.com\/avatar\/b351def598609cb4c0b5bca26497c7e5?s=96&d=mm&r=g\",\"caption\":\"owygs156\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Write to a Google Sheet with a Python Script - Machine Learning Applications","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":"http:\/\/intelligentonlinetools.com\/blog\/2017\/03\/12\/how-to-write-to-a-google-sheet-with-a-python-script\/","og_locale":"en_US","og_type":"article","og_title":"How to Write to a Google Sheet with a Python Script - Machine Learning Applications","og_description":"My post How to Write to a Google Spreadsheet with a Perl Script that was published some time ago is still getting a lot of visitors. This is not surprising as cloud computing is a fast-growing business. Below is the chart of number of searches for phrase “Google Sheet” from Google Trends site. The usage ... Read more","og_url":"http:\/\/intelligentonlinetools.com\/blog\/2017\/03\/12\/how-to-write-to-a-google-sheet-with-a-python-script\/","og_site_name":"Machine Learning Applications","article_published_time":"2017-03-12T22:41:51+00:00","article_modified_time":"2017-11-30T02:08:55+00:00","og_image":[{"url":"http:\/\/intelligentonlinetools.com\/blog\/wp-content\/uploads\/2017\/03\/Google-Sheet-trend-from-google-trend-300x87.png"}],"author":"owygs156","twitter_card":"summary_large_image","twitter_misc":{"Written by":"owygs156","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"http:\/\/intelligentonlinetools.com\/blog\/2017\/03\/12\/how-to-write-to-a-google-sheet-with-a-python-script\/","url":"http:\/\/intelligentonlinetools.com\/blog\/2017\/03\/12\/how-to-write-to-a-google-sheet-with-a-python-script\/","name":"How to Write to a Google Sheet with a Python Script - Machine Learning Applications","isPartOf":{"@id":"http:\/\/intelligentonlinetools.com\/blog\/#website"},"datePublished":"2017-03-12T22:41:51+00:00","dateModified":"2017-11-30T02:08:55+00:00","author":{"@id":"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/7a886dc5eb9758369af2f6d2cb342478"},"breadcrumb":{"@id":"http:\/\/intelligentonlinetools.com\/blog\/2017\/03\/12\/how-to-write-to-a-google-sheet-with-a-python-script\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/intelligentonlinetools.com\/blog\/2017\/03\/12\/how-to-write-to-a-google-sheet-with-a-python-script\/"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/intelligentonlinetools.com\/blog\/2017\/03\/12\/how-to-write-to-a-google-sheet-with-a-python-script\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/intelligentonlinetools.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to Write to a Google Sheet with a Python Script"}]},{"@type":"WebSite","@id":"http:\/\/intelligentonlinetools.com\/blog\/#website","url":"http:\/\/intelligentonlinetools.com\/blog\/","name":"Machine Learning Applications","description":"Artificial intelligence, data mining and machine learning for building web based tools and services.","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/intelligentonlinetools.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/7a886dc5eb9758369af2f6d2cb342478","name":"owygs156","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/image\/","url":"http:\/\/2.gravatar.com\/avatar\/b351def598609cb4c0b5bca26497c7e5?s=96&d=mm&r=g","contentUrl":"http:\/\/2.gravatar.com\/avatar\/b351def598609cb4c0b5bca26497c7e5?s=96&d=mm&r=g","caption":"owygs156"}}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p7h1IJ-hg","jetpack-related-posts":[{"id":90,"url":"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/how-to-write-to-a-google-spreadsheet-with-a-perl-script\/","url_meta":{"origin":1070,"position":0},"title":"How to Write to a Google Spreadsheet with a Perl Script","date":"February 13, 2016","format":false,"excerpt":"Google Docs is one of many cloud computing document-sharing services and provides an alternative to MS Office applications such as MS Excel. This post will show how to write data to Google Docs Spreadsheet and read data back from the same spreadsheet using perl programming language. Google Sheets is an\u2026","rel":"","context":"In "Perl Scripts"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":383,"url":"http:\/\/intelligentonlinetools.com\/blog\/2016\/07\/03\/getting-the-data-from-the-web-using-php-for-api-using-the-api-with-php\/","url_meta":{"origin":1070,"position":1},"title":"Getting the Data from the Web using PHP or Python for API","date":"July 3, 2016","format":false,"excerpt":"In the previous posts [1],[2] perl was used to get content from the web through Faroo API and Guardian APIs. In this post PHP and Pyhton will be used to get web data using same APIs. PHP has a powerful JSON parsing mechanism, which, because PHP is a dynamic language,\u2026","rel":"","context":"In "API Programming"","img":{"alt_text":"Trend for Python, Perl, PHP","src":"https:\/\/i0.wp.com\/intelligentonlinetools.com\/blog\/wp-content\/uploads\/2016\/07\/trend_for_python_perl_php-300x144.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1131,"url":"http:\/\/intelligentonlinetools.com\/blog\/2017\/04\/18\/extracting-google-adsense-and-google-analytics-data-for-website-analytics\/","url_meta":{"origin":1070,"position":2},"title":"Extracting Google AdSense and Google Analytics Data for Website Analytics","date":"April 18, 2017","format":false,"excerpt":"Recently I decided to get information that is showing for each page of my website Google Analytics account number and all Google AdSense links on this page. Connecting this information with Google Publisher Pages data would be very useful for better analysis and understanding of ads performance. So I created\u2026","rel":"","context":"In "Data Mining"","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/intelligentonlinetools.com\/blog\/wp-content\/uploads\/2017\/04\/extractingGAGS_info-130x300.jpg?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":79,"url":"http:\/\/intelligentonlinetools.com\/blog\/2016\/02\/13\/adding-stock-market-data-to-a-google-spreadsheet-with-a-perl-script\/","url_meta":{"origin":1070,"position":3},"title":"Adding Stock Market Data to a Google Spreadsheet with a Perl Script","date":"February 13, 2016","format":false,"excerpt":"In the previous post it was shown how to download stock data data prices into Google spreadsheet using perl script. This post will extend this script by considering situation when we just want to add the latest data to the existing spreadsheet with the data downloaded previously. For example first\u2026","rel":"","context":"In "Stock data analysis"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1356,"url":"http:\/\/intelligentonlinetools.com\/blog\/2017\/07\/21\/application-for-machine-learning-for-analyzing-blog-text-and-google-analytics-data\/","url_meta":{"origin":1070,"position":4},"title":"Application for Machine Learning for Analyzing Blog Text and Google Analytics Data","date":"July 21, 2017","format":false,"excerpt":"In the previous post we looked how to download data from WordPress blog. [1] So now we can have blog data. We can get also web metrics data from Google Analytics such us the number of views, time on the page. How do we connect post text data with metrics\u2026","rel":"","context":"In "Artificial Intelligence"","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/intelligentonlinetools.com\/blog\/wp-content\/uploads\/2017\/07\/blog-post-analytics-300x99.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":510,"url":"http:\/\/intelligentonlinetools.com\/blog\/2016\/08\/19\/getting-data-from-wikipedia-using-python\/","url_meta":{"origin":1070,"position":5},"title":"Getting Data From Wikipedia Using Python","date":"August 19, 2016","format":false,"excerpt":"Recently I come across python package Wikipedia which is a Python library that makes it easy to access and parse data from Wikipedia. Using this library you can search Wikipedia, get article summaries, get data like links and images from a page, and more. Wikipedia wraps the MediaWiki API so\u2026","rel":"","context":"In "API Programming"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/posts\/1070"}],"collection":[{"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/comments?post=1070"}],"version-history":[{"count":20,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/posts\/1070\/revisions"}],"predecessor-version":[{"id":1564,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/posts\/1070\/revisions\/1564"}],"wp:attachment":[{"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/media?parent=1070"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/categories?post=1070"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/tags?post=1070"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}