Unable to sketch vertical and horizontal lines around selected dataframe columns published in Gsheet using Python

Question

As per discussion on Python dataframe to Google Sheet: Freeze rows and sketch vertical lines around a sets of columns , I have been able to use the code below (thank you @Tanaike!) and sketch vertical and horizontal borders around dataframe df_final that I publish into Google Sheet at cell B7 using python.

Please chime in about 2 issues I have

As can be seen in the attached photo, the lines don't extend all the way to the column AE, but rather stop at Z (see red lines in photo). I tried to edit the code and make endColumnIndex higher than 26 but it doesn't help.
How do I make the contents of the sheet to be center aligned? I already auto fit them as shown in code below.

def Publish_Gsheet(df_final, service, spreadsheet_id, sheet_id):

### Clear the Google Sheet:
service.spreadsheets().batchUpdate(
    spreadsheetId=spreadsheet_id, body={"requests": [{
        "repeatCell": {
            "range": {
                "sheetId": sheet_id
            },
            "fields": "userEnteredValue,userEnteredFormat.borders"
        }
    }
    ]}).execute()
##########################


###  Write df_final to Google Sheet:
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()
###################################

### Autofit Columns and Rows width:
request_body = {
    'requests': [
        {
            'autoResizeDimensions': {
                'dimensions': {
                    'sheetId': sheet_id,
                    'dimension': 'COLUMNS',
                    'startIndex': 0,
                    'endIndex': 26
                }
            }
        },
        {
            'autoResizeDimensions': {
                'dimensions': {
                    'sheetId': sheet_id,
                    'dimension': 'ROWS',
                    'startIndex': 0,
                    'endIndex': 1000
                }
            }
        }
    ]
}


##### Sketching horizontal rows after each Week:
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": 26
        },
        "fields": "userEnteredFormat.borders"
    }
} for e in n]

##### Sketching vertical lines in between predefined columns:
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], [15, 16], [17, 18], [21,22], [22,23], [23,24], [27, 28]]]
add_requests2 = [{
    "repeatCell": {
        "cell": {"userEnteredFormat": {"borders": {"top": {"style": "SOLID_THICK"}}}},
        "range": {
            "sheetId": sheet_id,
            "startRowIndex": a,
            "endRowIndex": b,
            "startColumnIndex": 1,
            "endColumnIndex": 26
        },
        "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()

From `if possible, please chime in on the issue which is based on the code you suggested in this question: stackoverflow.com/q/72313797/6293211` to [my answer](https://stackoverflow.com/a/72023765), I saw your this question. But, I have to apologize for my poor English skill. Unfortunately, I cannot understand your current issue and your goa. But I would like to try to understand it. When I could correctly understand it, I would like to think of a solution. I would be grateful if you can forgive my poor English skill. — Tanaike, May 20 '22 at 06:35
1. About `As can be seen in the attached photo, the lines dont extend all the way to the column AE, but rather stop at Z (see red lines in photo). I tried to edit the code and make endColumnIndex higher than 26 but it doesnt help.`, in this case, do you want to modify only the vertical lines? The horizontal lines are not required to be modified. Is my understanding correct? — Tanaike, May 20 '22 at 06:49
2. About `How do I make the contents of the sheet to be center alligned? I already auto fit them as shown in code below.`, what cells do you want to set the center alignment? Do you want to set it for all cells of the sheet? — Tanaike, May 20 '22 at 06:49
Hi @Tanike, sorry for delay in response, I seemed to have missed your message. Yes I need both horizontal and vertical lines which are missing (red lines). For center alignment: I would like that for the entire sheet. — Sinha, May 23 '22 at 00:45
Thank you for replying. From your reply, I proposed a modified script as an answer. Could you please confirm it? If that was not useful and I misunderstood your goal, I apologize. — Tanaike, May 23 '22 at 01:09

score 0 · Accepted Answer · edited Jul 03 '22 at 08:00

Unfortunately, although I'm not sure whether I could correctly understand your question, from your provided question, I understood your goal as follows.

About As can be seen in the attached photo, the lines dont extend all the way to the column AE, but rather stop at Z (see red lines in photo). I tried to edit the code and make endColumnIndex higher than 26 but it doesnt help., in this case, you want to modify only the vertical lines. The horizontal lines are not required to be modified. You want to put the line at the right of column "AE".
About How do I make the contents of the sheet to be center aligned? I already auto fit them as shown in code below., You want to set this for all cells of the sheet.
About Yes I need both horizontal and vertical lines which are missing (red lines). For center alignment: I would like that for the entire sheet., when I saw your image, the red lines are only horizontal lines. From your image, you might want to put the vertical line between the column "AA" and "AB". And, you might want to put the horizontal lines from the left of column "B" to the right of column "AE". And, you might want to reflect the center alignment in the whole sheet.

If my understanding of your 2 goals is correct, how about the following modification?

Modified script:

In this case, please modify your showing script below the line of ### Autofit Columns and Rows width: as follows.

### Autofit Columns and Rows width:
request_body = {
    "requests": [
        {
            "autoResizeDimensions": {
                "dimensions": {
                    "sheetId": sheet_id,
                    "dimension": "COLUMNS",
                    "startIndex": 0,
                    "endIndex": 31,
                }
            }
        },
        {
            "autoResizeDimensions": {
                "dimensions": {
                    "sheetId": sheet_id,
                    "dimension": "ROWS",
                    "startIndex": 0,
                    "endIndex": 1000,
                }
            }
        },
    ]
}

##### Sketching horizontal rows after each Week:
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": 31,
            },
            "fields": "userEnteredFormat.borders",
        }
    }
    for e in n
]

##### Sketching vertical lines in between predefined columns:
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],
        [15, 16],
        [17, 18],
        [21, 22],
        [22, 23],
        [23, 24],
        [27, 28],
        [31, 32],
    ]
]
add_requests2 = [
    {
        "repeatCell": {
            "cell": {
                "userEnteredFormat": {"borders": {"top": {"style": "SOLID_THICK"}}}
            },
            "range": {
                "sheetId": sheet_id,
                "startRowIndex": a,
                "endRowIndex": b,
                "startColumnIndex": 1,
                "endColumnIndex": 31,
            },
            "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",
        }
    }
]

add_requests4 = [
    {
        "repeatCell": {
            "cell": {"userEnteredFormat": {"horizontalAlignment": "CENTER"}},
            "fields": "userEnteredFormat.horizontalAlignment",
            "range": {"sheetId": sheet_id},
        }
    }
]

requests.extend(add_requests1)
requests.extend(add_requests2)
requests.extend(add_requests3)
requests.extend(add_requests4)
service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body={"requests": requests}).execute()

I thought that in your goal, endColumnIndex should be 31.
In order to set the center alignment of the cell values, I added add_requests4.

Unable to sketch vertical and horizontal lines around selected dataframe columns published in Gsheet using Python

1 Answers1

Modified script:

Linked