Python dataframe to Google Sheet: Freeze rows and sketch vertical lines around a sets of columns

Question

I have a dataframe df_final (it is a time series data) as shown in attached photo. I am able to write this dataframe to the Google Sheet using the code below.

Questions:

In Google Sheet, I would like to draw vertical lines around the entire dataframe
Next I would also like to sketch vertical line on both sides of columns C,D,E,F as well as G,H,I,J as shown in attached photo.
Lastly, I would like to freeze the row7 (ie.. at the header of the dataframe)

Clear the Google Sheet:

service.spreadsheets().values().clear(
    spreadsheetId=spreadsheet_id,
    range='A1:AZ2600',
    body={}
).execute()

Write df_final to Google Sheet:

cell_range_insert= 'B7'
values = df_final.to_json()
body = {'values': values}
response_date= service.spreadsheets().values().append(
    spreadsheetId=spreadsheet_id,
    valueInputOption='RAW',
    range=cell_range_insert,
    body=dict(
        majorDimension= 'ROWS',
        values=df_final.T.reset_index().T.values.tolist()
    )
).execute()

score 1 · Accepted Answer · answered Apr 27 '22 at 05:41

In your situation, how about the following sample script? In this script, I modified this sample script.

Sample script:

spreadsheet_id = "###" # Please set Spreadsheet ID.
sheet_id = 0 # Please set the sheet ID.

# 1. Clear sheet. In this case, the values and the borders are removed.
service.spreadsheets().batchUpdate(
    spreadsheetId=spreadsheet_id, body={"requests": [{
        "repeatCell": {"range": {"sheetId": sheet_id}, "fields": "userEnteredValue,userEnteredFormat.borders"}
    }, {
        "updateSheetProperties": {"properties": {"sheetId": sheet_id}, "fields": "gridProperties.frozenRowCount"}
    }
    ]}).execute()

# 2. Put the values from the dataframe to Spreadsheet.
cell_range_insert = 'B7'
# values = df_final.to_json() # It seems that this is not used.
# body = {'values': values} # It seems that this is not used.
v = df_final.T.reset_index().T.values.tolist()
response_date = service.spreadsheets().values().append(
    spreadsheetId=spreadsheet_id,
    valueInputOption='RAW',
    range=cell_range_insert,
    body=dict(
        majorDimension='ROWS',
        values=v
    )
).execute()

# 3. Set the borders.
temp = -1
n = []
for index, row in df_final.iloc[:, 7:8].iterrows():
    s = ''.join(row.astype(str).tolist())
    if temp != s:
        n.append(index)
        temp = s
offset = 7
requests = [{
    "repeatCell": {
        "cell": {"userEnteredFormat": {"borders": {"top": {"style": "SOLID_THICK"}}}},
        "range": {
            "sheetId": sheet_id,
            "startRowIndex": e + offset,
            "endRowIndex": e + 1 + offset,
            "startColumnIndex": 1,
            "endColumnIndex": 10
        },
        "fields": "userEnteredFormat.borders"
    }
} for e in n]

end = len(v) + offset - 1
add_requests1 = [{
    "repeatCell": {
        "cell": {"userEnteredFormat": {"borders": {"left": {"style": "SOLID_THICK"}}}},
        "range": {
            "sheetId": sheet_id,
            "startRowIndex": 6,
            "endRowIndex": end,
            "startColumnIndex": a,
            "endColumnIndex": b
        },
        "fields": "userEnteredFormat.borders.left"
    }
} for [a, b] in [[1, 2], [2, 3], [6, 7], [10, 11]]]
add_requests2 = [{
    "repeatCell": {
        "cell": {"userEnteredFormat": {"borders": {"top": {"style": "SOLID_THICK"}}}},
        "range": {
            "sheetId": sheet_id,
            "startRowIndex": a,
            "endRowIndex": b,
            "startColumnIndex": 1,
            "endColumnIndex": 10
        },
        "fields": "userEnteredFormat.borders.top"
    }
} for [a, b] in [[6, 7], [end, end + 1]]]
add_requests3 = [{
    "updateSheetProperties": {
        "properties": {"gridProperties": {"frozenRowCount": offset}, "sheetId": sheet_id},
        "fields": "gridProperties.frozenRowCount"
    }
}]
requests.extend(add_requests1)
requests.extend(add_requests2)
requests.extend(add_requests3)
service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body={"requests": requests}).execute()

When this script is run, the borders are put to the left side of the columns "B", "C", "G" and "K". And, the borders are put to the top of rows of 7 rows and the end of rows. The middle of horizontal borders is put using my previous script.
And also, the 1st 7 rows are frozen.

References:

Hi Tanaike, thank you for your answer. Sorry could not get to it earlier, will try your code soon and let you know. — Sinha, Apr 30 '22 at 02:20
@Sinha Thank you for replying and testing it. I'm glad your issue was resolved. Thank you, too. — Tanaike, May 01 '22 at 01:20
if possible, please chime in on the issue which is based on the code you suggested in this question: https://stackoverflow.com/q/72313797/6293211 — Sinha, May 20 '22 at 05:06

Python dataframe to Google Sheet: Freeze rows and sketch vertical lines around a sets of columns

1 Answers1

Sample script:

References:

Linked