[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

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

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