[Python] Fixed lines with gspread-formatting + Coloring every other line

Purpose

I want to use gspread to fix and color rows automatically to make the spreadsheet easier to read.

Sample sheet

Use this sheet.

code

import gspread
from gspread_formatting import *
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)

"""
Formatting in spreadsheet
"""
#Header line
header_fmt = cellFormat(
    backgroundColor=color(1, 0.7, 0.3),
    textFormat=textFormat(bold=True, foregroundColor=color(0, 0, 0)),
    horizontalAlignment='CENTER'
)
#Data row
data_fmt = cellFormat(
    backgroundColor=color(1, 0.9, 0.7)
)
"""
Spreadsheet editing
"""
#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)))
#Convert last column from number to alphabet
last_column_alp = num2alpha(last_column_num)
#Filter
worksheet.set_basic_filter(name=(f'A:{last_column_alp}'))
#Fix the first line
worksheet.freeze(rows=1)
#Header line coloring + bold + centered
format_cell_range(worksheet, f'A1:{last_column_alp}1', header_fmt)
#Get the last line of input data
last_row_num = len(list(worksheet.col_values(1)))
#Color every other row of data
for row in range(2, last_row_num + 1, 2):
    format_cell_range(worksheet, f'A{row}:{last_column_alp}{row}', data_fmt)

Execution result

Code description

I will explain step by step.

Formatting

"""
Formatting in spreadsheet
"""
#Header line
header_fmt = cellFormat(
    backgroundColor=color(1, 0.7, 0.3),
    textFormat=textFormat(bold=True, foregroundColor=color(0, 0, 0)),
    horizontalAlignment='CENTER'
)
#Data row
data_fmt = cellFormat(
    backgroundColor=color(1, 0.9, 0.7)
)

Formatting part.

Set the color with backgroundColor. (R, G, B can be set arbitrarily with a numerical value from 0 to 1) Set bold text and text color with textFormat. Set the character position with horizontalAlignment.

The reference link is below. Add comments, colors and borders to cells in Google SpreadSheet in Python

Access Google Drive-Filter

Please refer to the past articles posted by the author. [Python] Filter spreadsheets with gspread

Fix the first line

#Fix the first line
worksheet.freeze(rows=1)

If you specify any number of rows in rows, you can fix it anywhere you like.

Header line coloring + bold + centered

#Header line coloring + bold + centered
format_cell_range(worksheet, f'A1:{last_column_alp}1', header_fmt)

The arguments are (worksheet you want to edit, range of cells (A1 to C1), format).

Color every other row of data

#Get the last line of input data
last_row_num = len(list(worksheet.col_values(1)))
#Color every other row of data
for row in range(2, last_row_num + 1, 2):
    format_cell_range(worksheet, f'A{row}:{last_column_alp}{row}', data_fmt)

Get the last row of data entry rows ↓ Coloring in a loop from the second line to the last line every other line

The flow of processing.

Now you can color every other line.

Consider request limits

In the previous sample, the number of lines is small, so the above code is fine, but if the number of lines increases, it will be caught in the request limit of the spreadsheet and will fail with an error. (There is a limit of 100 requests per 100 seconds)

We will fix it so that it does not get caught in this limit.

The code before the repair is a process of selecting line by line → coloring → selecting line by line.

After the repair, the process is changed from batch selection to coloring. This will reduce the number of requests.

code

#Store the coloring range and format of the data part in the list as a tuple type
ranges = [(f'A{row}:{last_column_alp}{row}', data_fmt) for row in range(2, last_row_num + 1, 2)]
print(ranges)
#Store the format of the header part at the beginning of the created list
ranges.insert(0, (f'A1:{last_column_alp}1', header_fmt))
print(ranges)
#Coloring all at once
format_cell_ranges(worksheet, ranges)

Execution result

[('A2:C2', <CellFormat backgroundColor=(red=1;green=0.9;blue=0.7)>), ('A4:C4', <CellFormat backgroundColor=(red=1;green=0.9;blue=0.7)>)]
[('A1:C1', <CellFormat backgroundColor=(red=1;green=0.7;blue=0.3);horizontalAlignment=CENTER;textFormat=(foregroundColor=(red=0;green=0;blue=0);bold=True)>), ('A2:C2', <CellFormat backgroundColor=(red=1;green=0.9;blue=0.7)>), ('A4:C4', <CellFormat backgroundColor=(red=1;green=0.9;blue=0.7)>)]

Store all the range you want to color in the list ↓ Coloring all at once

It is a process called. (Please refer to the contents of ranges displayed in the execution result part)

Unlike before, I am using a module called format_cell_ranges.

Official sample

fmt = cellFormat(
    backgroundColor=color(1, 0.9, 0.9),
    textFormat=textFormat(bold=True, foregroundColor=color(1, 0, 1)),
    horizontalAlignment='CENTER'
    )

fmt2 = cellFormat(
    backgroundColor=color(0.9, 0.9, 0.9),
    horizontalAlignment='RIGHT'
    )

format_cell_ranges(worksheet, [('A1:J1', fmt), ('K1:K200', fmt2)])

In this way, batch processing can be performed by storing the range and format with tuples in the list.

With this, you can fix the line + color every other line without getting caught in the limit!

Official link

gspread-formatting 0.3.0