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

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()

CodePudding user response：

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.

Sample script:

References: