Home > Net >  Unable to sketch vertical and horizontal lines around selected dataframe columns published in Gsheet
Unable to sketch vertical and horizontal lines around selected dataframe columns published in Gsheet

Time:05-23

As per discussion on enter image description here

CodePudding user response:

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

  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, 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".

  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., You want to set this for all cells of the sheet.

  3. 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.
  • Related