Highlighting rows and columns in google sheet using gspread

Question

I am working on google sheets and using gspread to dump some data from different sources in it. Then I have some rows and columns which I want to highlight by a color but for some reason it is only highlighting column A and not upto G which I intend to. Below is the function I created -

def highlight_cells(worksheet, var, var2={}):
    # Step 1: Find the column index for the variables you want to highlight based on its row
    cell = worksheet.find(var)
    if cell:
        column_index = cell.col

    # Step 2: Get the range of cells in the column
    start_row = cell.row + 1
    end_row = len(worksheet.get_all_values())
    column_range = f'{chr(ord("A") + column_index - 1)}{start_row}:{chr(ord("A") + column_index - 1)}{end_row}'
    # column_range = f'A{start_row}:G{end_row}'

    # Step 3-4: Iterate over each cell and store the formatting changes
    requests = []

    for range_cell in worksheet.range(column_range):
        if range_cell.row >= start_row:
            if var == 'name' and range_cell.value == 'John':                   

                requests.append({
                    'updateCells': {
                        'range': {
                            'sheetId': worksheet.id,
                            'startRowIndex': range_cell.row - 1,
                            'endRowIndex': range_cell.row,
                            'startColumnIndex': 0,
                            'endColumnIndex': 7
                        },
                        'rows': [{
                            'values': [{
                                'userEnteredFormat': {
                                    'backgroundColor': {'red': 0.7, 'green': 0.94, 'blue': 0.92}
                                }
                            }]
                        }],
                        'fields': 'userEnteredFormat.backgroundColor'
                    }
                })

# Step 5: Batch update the formatting of the cells
if requests:
    body = {
        'requests': requests
    }
    worksheet.spreadsheet.batch_update(body)

When I call highlight_cells(worksheet=worksheet, var='name'), it does highlight the right rows but only column A and not upto G. Not able to figure out what is going wrong here.

Answer 1

In your script, how about using repeatCell instead of updateCells? When this is reflected in your script, please modify as follows.

Modified script:

I modified requests.append({,,,}) in your script.

def highlight_cells(worksheet, var, var2={}):
    # Step 1: Find the column index for the variables you want to highlight based on its row
    cell = worksheet.find(var)
    if cell:
        column_index = cell.col

    # Step 2: Get the range of cells in the column
    start_row = cell.row + 1
    end_row = len(worksheet.get_all_values())
    column_range = f'{chr(ord("A") + column_index - 1)}{start_row}:{chr(ord("A") + column_index - 1)}{end_row}'
    # column_range = f'A{start_row}:G{end_row}'

    # Step 3-4: Iterate over each cell and store the formatting changes
    requests = []

    for range_cell in worksheet.range(column_range):
        if range_cell.row >= start_row:
            if var == 'name' and range_cell.value == 'John':

                # --- I modified the below script
                requests.append({
                    'repeatCell': {
                        'range': {
                            'sheetId': worksheet.id,
                            'startRowIndex': range_cell.row - 1,
                            'endRowIndex': range_cell.row,
                            'startColumnIndex': 0,
                            'endColumnIndex': 7
                        },
                        'cell': {
                            'userEnteredFormat': {
                                'backgroundColor': {'red': 0.7, 'green': 0.94, 'blue': 0.92}
                            }
                        },
                        'fields': 'userEnteredFormat.backgroundColor'
                    }
                })
                # ---

# Step 5: Batch update the formatting of the cells
if requests:
    body = {
        'requests': requests
    }
    worksheet.spreadsheet.batch_update(body)

Reference:

• RepeatCellRequest