[PYTHON] Try to scrape as much as you can scrape the sample program of Google Spreadsheet API (v4) quick start

For those who want to read a Google Drive spreadsheet file with python, but don't know what Google's sample is doing, just want to read it.

Former story https://developers.google.com/sheets/api/quickstart/python First of all, please do your best until STEP2.

Then it is the program part of STEP3

# coding:utf-8
import httplib2

import apiclient
import oauth2client

#Setting
# -----------------------------------------------------------------------------
SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly'  #Ask for read permission
APPLICATION_NAME = 'read sample'  #I don't speak Japanese, so I only use English characters

SHEET_ID = 'Spreadsheet file ID. Can be picked up from the URL when editing the file'
SHEET_TAB = 'Sheet name in the spreadsheet'
RANGE = 'A1:Z1000'  #Range to read
# -----------------------------------------------------------------------------


#Read authentication file
store = oauth2client.file.Storage("./credential.json")  #Do you have an authentication file?
credentials = store.get()

#Ask for permission if unauthenticated(Browser authentication)
if not credentials or credentials.invalid:
    flow = oauth2client.client.flow_from_clientsecrets('client_secret.json', SCOPES)
    flow.user_agent = APPLICATION_NAME

    #Initial authentication
    import argparse
    args = '--auth_host_name localhost --logging_level INFO --noauth_local_webserver'
    flags = argparse.ArgumentParser(parents=[oauth2client.tools.argparser]).parse_args(args.split())

    credentials = oauth2client.tools.run_flow(flow, store, flags)  #Authentication file is created when allowed by browser authentication

#Connect using authentication file
http = credentials.authorize(httplib2.Http())

#Sheet loading
service = apiclient.discovery.build('sheets', 'v4', http=http, discoveryServiceUrl='https://sheets.googleapis.com/$discovery/rest?version=v4')
values = service.spreadsheets().values().get(spreadsheetId=SHEET_ID, range=SHEET_TAB+'!'+RANGE).execute().get('values', [])

#You can do various things using values
print(values)

Please add your own error handling _ (: 3 ”∠) _

bonus

If you want to read all the sheets in a spreadsheet, it's useful to get SHEET_TAB from a list of metadata sheets instead of fixed values.

#Sheet loading
#Get the service together
service = apiclient.discovery.build('sheets', 'v4', http=http, discoveryServiceUrl='https://sheets.googleapis.com/$discovery/rest?version=v4')

#Get the name of the sheet before reading values
sheet_meta = service.spreadsheets().get(spreadsheetId=SHEET_ID).execute()
for sheet in sheet_meta.get('sheets', ''):
    #Get sheet name
    SHEET_TAB = sheet.get("properties", {}).get("title", 0)

    #Here too(SHEET just before_TAB overwriting)
    values = service.spreadsheets().values().get(spreadsheetId=SHEET_ID, range=SHEET_TAB+'!'+RANGE).execute().get('values', [])

    #You can do various things using values
    print(values)

Recommended Posts

Try to scrape as much as you can scrape the sample program of Google Spreadsheet API (v4) quick start
Until you can use the Google Speech API
Try connecting to Supervisord via XMLRPC to start / stop the program
How to start the program
Until you try the Google Cloud Vision API (harmful image detection)
Python beginners hit the unofficial API of Google Play Music to play music
Try using n to downgrade the version of Node.js you have installed
Allow Slack to notify you of the end of a time-consuming program process
[RSpec] Make the response of the request to the Google API stubized by stub_request (: post) the same format as the actual one.