How do I access read write Google Sheets spreadsheets with Python

Question

I am wondering if you can point me to an example of reading writing to from a google doc spreadsheet using python    I did look at google docs API here https   developers google com google-apps spreadsheets  but not sure if I hit the right link  Also an example will be of great help   What I am trying to do is query spreadsheets based on the different columns more like a SQL query and then do some downstream parsing with the data and put it into another spreadsheet or doc at google docs   Best  -Abhi

User · Answer

Jun-Dec 2016  Most answers here are now out-of-date as  1  GData APIs are the previous generation of Google APIs  and that s why it was hard for  Josh Brown to find that old GData Docs API documentation  While not all GData APIs have been deprecated  all newer Google APIs do not use the Google Data protocol  and 2  Google released a new Google Sheets API  not GData   In order to use the new API  you need to get the Google APIs Client Library for Python  it s as easy as pip install -U google-api-python-client  or pip3 for Python 3   and use the latest Sheets API v4   which is much more powerful  amp  flexible than older API releases   Here s one code sample from the official docs to help get you kickstarted  However  here are slightly longer  more  real-world  examples of using the API you can learn from  videos plus blog posts     Migrating SQL data to a Sheet plus code deep dive post Formatting text using the Sheets API plus code deep dive post Generating slides from spreadsheet data plus code deep dive post Those and others in the Sheets API video library   The latest Sheets API provides features not available in older releases  namely giving developers programmatic access to a Sheet as if you were using the user interface  create frozen rows  perform cell formatting  resizing rows columns  adding pivot tables  creating charts  etc    but NOT as if it was some database that you could perform searches on and get selected rows from  You d basically have to build a querying layer on top of the API that does this  One alternative is to use the Google Charts Visualization API query language  which does support SQL-like querying  You can also query from within the Sheet itself  Be aware that this functionality existed before the v4 API  and that the security model was updated in Aug 2016  To learn more  check my G  reshare to a full write-up from a Google Developer Expert   Also note that the Sheets API is primarily for programmatically accessing spreadsheet operations  amp  functionality as described above  but to perform file-level access such as imports exports  copy  move  rename  etc   use the Google Drive API instead  Examples of using the Drive API    Listing your files in Google Drive and code deep dive post Google Drive  Uploading  amp  Downloading Files plus  Poor man s plain text to PDF converter  code deep dive post     Exporting a Google Sheet as CSV blog post only       - TL DR  upload plain text file to Drive  import convert to Google Docs format  then export that Doc as PDF  Post above uses Drive API v2  this follow-up post describes migrating it to Drive API v3  and here s a developer video combining both  poor man s converter  posts   To learn more about how to use Google APIs with Python in general  check out my blog as well as a variety of Google developer videos  series 1 and series 2  I m producing   ps  As far as Google Docs goes  there isn t a REST API available at this time  so the only way to programmatically access a Doc is by using Google Apps Script  which like Node js is JavaScript outside of the browser  but instead of running on a Node server  these apps run in Google s cloud  also check out my intro video   With Apps Script  you can build a Docs app or an add-on for Docs  and other things like Sheets  amp  Forms    UPDATE Jul 2018  The above  ps   is no longer true  The G Suite developer team pre-announced a new Google Docs REST API at Google Cloud NEXT  18  Developers interested in getting into the early access program for the new API should register at https   developers google com docs    UPDATE Feb 2019  The Docs API launched to preview last July is now available generally to all    read the launch post for more details   UPDATE Nov 2019  In an effort to bring G Suite and GCP APIs more inline with each other  earlier this year  all G Suite code samples were partially integrated with GCP s newer  lower-level not product  Python client libraries  The way auth is done is similar but  currently  requires a tiny bit more code to manage token storage  meaning rather than our libraries manage storage json  you ll store them using pickle  token pickle or whatever name you prefer  instead  or choose your own form of persistent storage  For you readers here  take a look at the updated Python quickstart example

User · Answer

Have a look at GitHub - gspread    I found it to be very easy to use and since you can retrieve a whole column by   first col   worksheet col values 1    and a whole row by   second row   worksheet row values 2    you can more or less build some basic select     where           easily

User · Answer

Take a look at gspread port for api v4 - pygsheets  It should be very easy to use rather than the google client   Sample example  import pygsheets  gc   pygsheets authorize      Open spreadsheet and then workseet sh   gc open  my new ssheet   wks   sh sheet1    Update a cell with value  just to let him know values is updated      wks update cell  A1    Hey yank this numpy array      update the sheet with array wks update cells  A2   my nparray to list       share the sheet with your friend sh share  myFriend gmail com     See the docs here   Author here

User · Answer

I think you re looking at the cell-based feeds section in that API doc page  Then you can just use the PUT  GET requests within your Python script  using either commands getstatusoutput or subprocess

User · Answer

You could have a look at Sheetfu  The following is an example from the README  It gives a super easy syntax to interact with spreadsheets as if it was a database table   from sheetfu import Table  spreadsheet   SpreadsheetApp  path to secret json   open by id   lt insert spreadsheet id here gt    data range   spreadsheet get sheet by name  people   get data range    table   Table data range  backgrounds True   for item in table      if item get field value  name       foo           item set field value  surname    bar                  this set the surname field value     age   item get field value  age       item set field value  age   age   1      item set field background  age     ff0000                 this set the field  age  to red color    Every set functions are batched for speed performance    To send the batch update of every set requests you made    you need to commit the table object as follow  table commit     Disclaimer  I m the author of this library

User · Answer

This thread seems to be quite old   If anyone s still looking  the steps mentioned here   https   github com burnash gspread work very well   import gspread from oauth2client service account import ServiceAccountCredentials import os  os chdir r your path    scope     https   spreadsheets google com feeds         https   www googleapis com auth drive    creds   ServiceAccountCredentials from json keyfile name  client secret json   scope  gc   gspread authorize creds  wks   gc open  Trial Sheet   sheet1 wks update acell  H3    I m here      Make sure to drop your credentials json file in your current directory  Rename it as client secret json   You might run into errors if you don t enable Google Sheet API with your current credentials

User · Answer

The latest google api docs document how to write to a spreadsheet with python but it s a little difficult to navigate to  Here is a link to an example of how to append   The following code is my first successful attempt at appending to a google spreadsheet   import httplib2 import os  from apiclient import discovery import oauth2client from oauth2client import client from oauth2client import tools  try      import argparse     flags   argparse ArgumentParser parents  tools argparser   parse args   except ImportError      flags   None    If modifying these scopes  delete your previously saved credentials   at    credentials sheets googleapis com-python-quickstart json SCOPES    https   www googleapis com auth spreadsheets  CLIENT SECRET FILE    client secret json  APPLICATION NAME    Google Sheets API Python Quickstart    def get credentials           Gets valid user credentials from storage       If nothing has been stored  or if the stored credentials are invalid      the OAuth2 flow is completed to obtain the new credentials       Returns          Credentials  the obtained credential              home dir   os path expanduser          credential dir   os path join home dir    credentials       if not os path exists credential dir           os makedirs credential dir      credential path   os path join credential dir                                      mail to g app json        store   oauth2client file Storage credential path      credentials   store get       if not credentials or credentials invalid          flow   client flow from clientsecrets CLIENT SECRET FILE  SCOPES          flow user agent   APPLICATION NAME         if flags              credentials   tools run flow flow  store  flags          else    Needed only for compatibility with Python 2 6             credentials   tools run flow  store          print  Storing credentials to     credential path      return credentials  def add todo        credentials   get credentials       http   credentials authorize httplib2 Http        discoveryUrl     https   sheets googleapis com  discovery rest                        version v4       service   discovery build  sheets    v4   http http                                discoveryServiceUrl discoveryUrl       spreadsheetId    PUT YOUR SPREADSHEET ID HERE      rangeName    A1 A         https   developers google com sheets guides values appending values     values     values     Hello Saturn           result   service spreadsheets   values   append          spreadsheetId spreadsheetId  range rangeName          valueInputOption  RAW           body values  execute    if   name         main         add todo

User · Answer

I know this thread is old now  but here is some decent documentation on Google Docs API  It was ridiculously hard to find  but useful  so maybe it will help you some  http   pythonhosted org gdata docs api html   I used gspread recently for a project to graph employee time data  I don t know how much it might help you  but here s a link to the code  https   github com lightcastle employee-timecards  Gspread made things pretty easy for me  I was also able to add logic in to check for various conditions to create month-to-date and year-to-date results  But I just imported the whole dang spreadsheet and parsed it from there  so I m not 100  sure that it is exactly what you re looking for  Best of luck

[python] How do I access (read, write) Google Sheets spreadsheets with Python?

Examples related to python

Examples related to google-sheets

Examples related to google-api

Examples related to google-api-python-client

Examples related to google-sheets-api