Play with Google Spread Sheets in python (OAuth)
environment
- Mac OSX(El Capitan)
- Python 2.7
Preparation
Issuing Google Drive API
Creating a project
- If there is no project, create a new project from the example section on the upper right.
- You can decide the project name and ID
API
- Enable the Google Drive API from the Google Developers Console (https://console.developers.google.com/)
- After enabling the API, select the credentials on the left and select Create Credentials-> Service Account Key (create one if you don't have one)
- JSON and P12 format can be selected, but JSON is used here.
- JSON file will be downloaded automatically when completed
Sharing settings on the spreadsheet side
- Press the share button at the top right of your spreadsheet and enter the address in the
client_emailfield in the downloaded JSON file
- id
url https://docs.google.com/spreadsheets/d/***********/edit
The asterisk part becomes id
Python
Library installation
- Library
- oauth2client
sudo pip install --upgrade oauth2client --ignore-installed six-gdata (google regular: not used this time)sudo pip install gdata --ignore-installed six-gspread (This one seems to be more convenient, so I will use this one this time)sudo pip install gspread --ignore-installed six
- In El Capitan, pip installation fails due to System Integrity Protection (SIP), so
--ignore-installed sixis added at the end.
Code
- Previously, the
SignedJwtAssertionCredentialsclass was used, but in 2016/02 there was Library Update to useServiceAccountCredentials. became
import os
import gspread
from oauth2client.service_account import ServiceAccountCredentials
def main():
scope = ['https://spreadsheets.google.com/feeds']
doc_id = 'Your doc id'
path = os.path.expanduser("Path of json file")
credentials = ServiceAccountCredentials.from_json_keyfile_name(path, scope)
client = gspread.authorize(credentials)
gfile = client.open_by_key(doc_id)
worksheet = gfile.sheet1
records = worksheet.get_all_values()
for record in records:
print(record)
if __name__ == '__main__':
main()
Recommended Posts