[Python] Filter spreadsheets with gspread

Purpose

I want to filter a spreadsheet using gspread. Since there is no procedure other than the official reference (self-examination), I will introduce it.

Sample sheet

test_sheet.png

I will use this sheet.

code

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
import os

#Find the alphabet from numbers
def num2alpha(num):
    if num<=26:
        return chr(64+num)
    elif num%26==0:
        return num2alpha(num//26-1)+chr(90)
    else:
        return num2alpha(num//26)+chr(64+num%26)

#The path where it is stored
abs_path = f'{os.path.dirname(os.path.abspath(__file__))}\\'

# GoogleAPI
scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
#Authentication key
json_keyfile_path = f'{abs_path}credentials.json'
#Read the service account key
credentials = ServiceAccountCredentials.from_json_keyfile_name(json_keyfile_path, scope)
#Perform OAuth authentication for pydrive
gauth = GoogleAuth()
gauth.credentials = credentials
drive = GoogleDrive(gauth)
#Spreadsheet storage folder
folder_id = 'Folder ID'
#Get a list of files in the spreadsheet storage folder
file_list = drive.ListFile({'q': "'%s' in parents and trashed=false" % folder_id}).GetList()
#Extract only the file name from the file list
title_list = [file['title'] for file in file_list]
#Authenticate for gspread
gc = gspread.authorize(credentials)
#Spreadsheet ID
sheet_id = [file['id'] for file in file_list if file['title'] == 'test']
sheet_id = sheet_id[0]
#Open workbook
workbook = gc.open_by_key(sheet_id)
#Open the worksheet
worksheet = workbook.worksheet('Sheet 1')
#Get the last column entered
last_column_num = len(list(worksheet.row_values(1)))
print(f'last_column_num:{last_column_num}')
#Convert last column from number to alphabet
last_column_alp = num2alpha(last_column_num)
print(f'last_column_alp:{last_column_alp}')
#Filter
worksheet.set_basic_filter(name=(f'A:{last_column_alp}'))

Execution result


last_column_num:3
last_column_alp:C

test_sheet2.png

Code description

I will explain step by step.

Workbook development

#The path where it is stored
abs_path = f'{os.path.dirname(os.path.abspath(__file__))}\\'

# GoogleAPI
scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
#Authentication key
json_keyfile_path = f'{abs_path}credentials.json'
#Read the service account key
credentials = ServiceAccountCredentials.from_json_keyfile_name(json_keyfile_path, scope)
#Perform OAuth authentication for pydrive
gauth = GoogleAuth()
gauth.credentials = credentials
drive = GoogleDrive(gauth)
#Spreadsheet storage folder
folder_id = 'Folder ID'
#Get a list of files in the spreadsheet storage folder
file_list = drive.ListFile({'q': "'%s' in parents and trashed=false" % folder_id}).GetList()
#Extract only the file name from the file list
title_list = [file['title'] for file in file_list]
#Authenticate for gspread
gc = gspread.authorize(credentials)
#Spreadsheet ID
sheet_id = [file['id'] for file in file_list if file['title'] == 'test']
sheet_id = sheet_id[0]
#Open workbook
workbook = gc.open_by_key(sheet_id)

First, in this part

Specifying the authentication key ↓ Access Google Drive ↓ Specify the spreadsheet storage folder ↓ Open the workbook by specifying the file name (spreadsheet name)

We are doing the processing. Please refer to the following article for details. Create / edit spreadsheet in any folder of Google Drive with python Edit Google Sheets in Python

Filter

Here is the filter part of the main subject.

#Open the worksheet
worksheet = workbook.worksheet('Sheet 1')

First, expand the worksheet. This time, the name is "Sheet 1", so specify it as Sheet 1.

#Get the last column entered
last_column_num = len(list(worksheet.row_values(1)))
print(f'last_column_num:{last_column_num}')

Then get the last column of data entered in the target worksheet.

Execution result


last_column_num:3

As you can see by looking at the sheet pasted earlier, it is in the third row, so you can get it well.

However, it is not possible to apply the filter as it is, so it is necessary to convert the "third column" to the alphabet format. (I tried it with numerical values, but it didn't work well, so it seems to be a library specification.)

#Find the alphabet from numbers
def num2alpha(num):
    if num<=26:
        return chr(64+num)
    elif num%26==0:
        return num2alpha(num//26-1)+chr(90)
    else:
        return num2alpha(num//26)+chr(64+num%26)

#Convert last column from number to alphabet
last_column_alp = num2alpha(last_column_num)
print(f'last_column_alp:{last_column_alp}')

Execution result


last_column_alp:C

I was able to successfully convert from 3 to C. For the conversion function, I used the one in the following article. How to convert numbers and alphabets to each other in Python

And finally, filter.

#Filter
worksheet.set_basic_filter(name=(f'A:{last_column_alp}'))

That's all there is to it.

If you specify in the format of "start column: end column", any column will be filtered.

In this case, we want to filter columns A to C, so the above specifications are used.

reference

API Reference

Recommended Posts

[Python] Filter spreadsheets with gspread
Image processing with Python 100 knock # 10 median filter
FizzBuzz with Python3
Scraping with Python
Statistics with python
Scraping with Python
Python with Go
Twilio with Python
Integrate with Python
Play with 2016-Python
AES256 with python
Tested with Python
Image processing with Python 100 knock # 12 motion filter
python starts with ()
with syntax (Python)
Bingo with python
Zundokokiyoshi with python
Excel with Python
Image processing with Python 100 knocks # 9 Gaussian filter
Microcomputer with Python
Cast with python
Edge extraction with python + OpenCV (Sobel filter, Laplacian filter)
Track green objects with python + numpy (particle filter)
[Python] Troubleshooting before accessing Google Spreadsheet with gspread
Serial communication with Python
Zip, unzip with python
Django 1.11 started with Python3.6
Primality test with Python
Python with eclipse + PyDev.
Socket communication with Python
Data analysis with python 2
Scraping with Python (preparation)
Learning Python with ChemTHEATER 03
Sequential search with Python
Run Python with VBA
Handling yaml with python
Solve AtCoder 167 with python
Serial communication with python
[Python] Use JSON with Python
Learning Python with ChemTHEATER 05-1
Learn Python with ChemTHEATER
Run prepDE.py with python3
1.1 Getting Started with Python
Collecting tweets with Python
Binarization with OpenCV / Python
3. 3. AI programming with Python
Kernel Method with Python
Non-blocking with Python + uWSGI
Scraping with Python + PhantomJS
Filter List in Python
Posting tweets with python
Use mecab with Python3
[Python] Redirect with CGIHTTPServer
Voice analysis with python
Think yaml with python
Operate Kinesis with Python
Getting Started with Python
Use DynamoDB with Python
Zundko getter with python
Handle Excel with python
Ohm's Law with Python