Manipulate spreadsheets locally using Python

This Qiita article is a memo of the code used in the YouTube video. ▼ Please check the video for details (I'm really happy if you like the YouTube video) https://youtu.be/TpkL5hQDPWo

pythonGsheet.gif

Preparation

First, create a new project on the http://console.developers.google.com/ page and enable the following two APIs.

  1. Enable Google Drive API and get the JSON file (renamed to credits.json)
  2. Enable the Google Sheet API

Then type two commands in the terminal.

$ pip install gspread
$ pip install oauth2client

Code (Explanation is added to the comment part.)

sample.py


import gspread 
from oauth2client.service_account import ServiceAccountCredentials
from pprint import pprint 

scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]

creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)

client = gspread.authorize(creds)

#I'm getting the first sheet titled python.
sheet = client.open("python").sheet1

#All values are assigned to a variable called data.
data = sheet.get_all_records()

#Vertical and horizontal data can be obtained by the following methods.
row = sheet.row_values(3)
col = sheet.col_values(3)

#If you want to get only a specific cell, you can get it below.
cell = sheet.cell(3,2).value

#If you want to change or add values to your spreadsheet, you can do the following:
sheet.update_cell(3,2, "nakajo")
insertRow = [3, "nakajo", "Tangerine juice"]
sheet.insert_row(insertRow, 3)

pprint(data)

Commands to type locally

$ python sample.py

Please subscribe to the channel

http://youtube.com/user/NJTVnetwork?sub_confirmation=1 We are aiming for ** 1,000 subscribers **!

Recommended Posts

Manipulate spreadsheets locally using Python
Access spreadsheets using OAuth 2.0 from Python
Start using Python
Install Python locally
Scraping using Python
Manipulate objects using Blender 2.8's low-level Python API
Fibonacci sequence using Python
Data analysis using Python 0
Data cleaning using Python
Using Python #external packages
WiringPi-SPI communication using Python
Age calculation using python
Manipulate riak from python
Search Twitter using Python
Name identification using python
Notes using Python subprocesses
Try using Tweepy [Python2.7]
Python notes using perl-ternary operator
Scraping using Python 3.5 async / await
real-time-Personal-estimation (learning using GPU locally)
Save images using python3 requests
[S3] CRUD with S3 using Python [Python]
[Python] Try using Tkinter's canvas
Using Quaternion with Python ~ numpy-quaternion ~
Python notes using perl-special variables
[Python] Using OpenCV with Python (Basic)
[Python] Filter spreadsheets with gspread
Scraping using Python 3.5 Async syntax
Website change monitoring using python
Post to Twitter using Python
Start to Selenium using python
Search algorithm using word2vec [python]
Change python version using pyenv
python: Basics of using scikit-learn ①
# 1 [python3] Simple calculation using variables
Create JIRA tickets using Python
Instrument control using Python [pyvisa]
Python memo using perl --join
Manipulate various databases with Python
Web scraping using Selenium (Python)
[Python] I tried using OpenPose
[Python] JSON validation using Voluptuous
Broadcast on LINE using python
Data analysis using python pandas
Translate using googletrans in Python
Using Python mode in Processing
Using OpenCV with Python @Mac
[Python] Shooting game using pyxel
Send using Python with Gmail
Complement python with emacs using company-jedi
How to install python using anaconda
Harmonic mean with Python Harmonic mean (using SciPy)
Initializing global variables using Python decorators
[Python] Loading csv files using pandas
GUI programming in Python using Appjar
Retry post request using python requests
Python Note: About comparison using is
[Ubuntu] [Python] Object tracking using dlib
Image capture of firefox using python
[Python] Using OpenCV with Python (Image Filtering)
Precautions when using pit in Python