How to access (read, write) Google Sheets tables using Python?

Question

I am wondering if you can give me an example of reading / writing to / from a doc / google spreadsheet using python.

I looked at the 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 useful.

What I'm trying to do is query tables based on different columns, more like an SQL query, and then do some subsequent parsing with the data and put it in another table or document in google docs.

Best, -Abhi

Answer 1

(June-December 2016) Most of the answers here are out of date: 1) GData APIs are previous generation Google APIs, and therefore @ Josh Brown found it difficult to find this old GData Docs API documentation. Although not all GData APIs are out of date, more and more new Google APIs do not use the Google Data protocol ; and 2) Google has released the new Google Sheets API (not GData). To use the new API, you need to get the Google API client library for Python (it's as simple as pip install -U google-api-python-client [or pip3 for Python 3]) and use the latest Sheets v4 + API , which is much more powerful and flexible than older versions of the API.

Here is one sample code from white papers to help you get started. However, here are a few longer, more “real” examples of using the API that you can learn from (video plus blog posts):

• Migrate SQL data to a deep dive post in Sheet Plus code
• Formatting text using the Sheets API plus post deep diving code
• Create slides from data spreadsheets plus post deep diving code

The latest Sheets API provides functions not available in earlier versions, namely it provides developers programmatic access to Sheet as if you were using the user interface (creating frozen rows, formatting cells, resizing rows / columns, adding pivot tables, creating charts, etc. .d.) ..), but NOT as if it were some kind of database on which you could search and retrieve selected rows. You should basically build a query layer on top of the API that does this. One option is to use the Google Charts Visualization API query language , which supports queries like SQL . You can also request from within the sheet itself . Remember that this functionality existed before API v4, and that the security model was updated in August 2016 . To find out more, check out my G + resource for the full -U p entry from Google Developer Expert .

Also note that the API sheets are primarily for programmatically accessing spreadsheets & functionality as described above, but for executing -level file access , such as import / export, copying, moving, renaming, etc., use the API Google Drive instead. Drive API usage examples:

• List your files on Google Drive and write a deep dive code
• Google Drive: Download and Download Files plus “Poor Man to Simple Text to PDF Converter” post deep dive code (*)
• Export Google sheet as a CSV blog post only

(*) - TL; DR: Download a simple text file to disk, import / convert to Google Docs format, and then export this document to PDF. The post above uses Drive API v2; this follow-up post -U p describes porting it to the Drive API v3, and here is a developer video that combines both the poor converter posts.

To learn more about how to use the Google APIs with Python in general, check out my blog, as well as the many videos for Google developers ( series 1 and series 2 ) that I make.

ps. As for Google Docs, the REST API is currently unavailable, therefore the only way to programmatically access the Doc is to use the Google Apps script (which, like Node.js, is JavaScript outside the browser, but instead of running these applications on the Node server work in the Google cloud; also see my introductory video .) Using the Apps script, you can create a Docs application or add-on for Docs (and other applications like Sheets & Forms).

UPDATE July 2018 : The aforementioned ps. no longer true. The G Suite development team previously announced the new Google Docs REST API on Google Cloud NEXT '18. Developers interested in gaining access to the early access program for the new API should register at https://developers.google.com/docs .

UPDATE February 2019 : The Docs API, launched for preview in July last year, is now available mainly for everyone ... read the launch post for more details.

Answer 2

Check out GitHub - gspread .

I found that it is very easy to use, and since you can get the whole column

 first_col = worksheet.col_values(1) 

and a whole line

 second_row = worksheet.row_values(2) 

you can more or less build a basic choice ... where ... = ... easily.

Answer 3

I know this thread is already out of date, but there is decent documentation on the Google Docs API here. It was ridiculously hard to find but useful, so maybe this will help you. http://pythonhosted.org/gdata/docs/api.html

I recently used gspread for a project to display employee time data. I don’t know how much this can help you, but here is 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 to check the various conditions for creating monthly and annual results. But I just imported the whole dang spreadsheet and parsed it from there, so I'm not 100% sure that this is exactly what you are looking for. Good luck.

Answer 4

Take a look at the big port for api v4 - pygsheets . It should be very easy to use, not a Google client.

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("[e-mail]") 

See the docs here .

The author is here.

Answer 5

The latest google api docs document is how to write to a spreadsheet using python, but a little difficult to navigate. Here is a link to an example add .

The following code is my first successful attempt to add 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() 

Answer 6

You could take a look at Sheetfu . Below is an example from README. This provides super easy syntax for interacting with spreadsheets, as if it were a database table.

 from sheetfu import Table spreadsheet = SpreadsheetApp('path/to/secret.json').open_by_id('<insert spreadsheet id here>') 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 am the author of this library.

Answer 7

This topic seems pretty old. If anyone else is 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 you save your credentials JSON file in your current directory. Rename it to client_secret.json.

You may encounter errors if you do not enable the Google Sheet API with current credentials.

Answer 8

I think you are looking at the feed section based on this page of an API document. Then you can simply use the PUT / GET requests in a Python script, using either commands.getstatusoutput or subprocess .