无法用Python绘制在Gsheet中发布的选定数据栏周围的竖线和水平线

Question

根据对Python dataframe to Google Sheet: Freeze rows and sketch vertical lines around a sets of columns的讨论，我已经能够使用下面的代码(谢谢@Tanaike!)并在使用python发布到Google的dataframe df_final周围勾勒出垂直和水平的边框。

请插入大约2期我有

从附图中可以看到

1. ，这些线不是一直延伸到列AE，而是在Z处停下来(见照片中的红线)。我试图编辑代码并使endColumnIndex高于26，但这没有帮助。

1. 如何使工作表的内容对齐？我已经自动安装了它们，如下面的代码所示.

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

​

​

Answer 1

不幸的是，虽然我不确定我是否能正确理解你的问题，从你提供的问题，我理解你的目标如下。

1. 关于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.，在本例中，您只想修改垂直行。水平线不需要修改。你想把这一行放在“AE”栏的右边。

1. 关于How do I make the contents of the sheet to be center aligned? I already auto fit them as shown in code below.，您希望为工作表的所有单元格设置此值。

1. 关于Yes I need both horizontal and vertical lines which are missing (red lines). For center alignment: I would like that for the entire sheet.，当我看到你的图像时，红线只是水平线。从您的图像来看，您可能需要在列"AA“和"AB”之间放置垂直线。并且，您可能需要将"B“列左边的水平线放在"AE”列的右侧。而且，您可能希望在整个工作表中反映中心对齐。

如果我对你的两个目标的理解是正确的，下面的修改如何？

修改脚本：

在这种情况下，请修改### Autofit Columns and Rows width:行下面的显示脚本，如下所示。

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

我认为在您的目标中，31.

• In应该是顺序来设置单元格值的中心对齐，我添加了add_requests4.