如何完善Python代码：从Google表格取图表替换Slides占位符

问题描述 #

我正在用Google API实现月度报告自动化，流程为复制表格与幻灯片模板、更新表格数据，之后需要将表格中的图表添加至幻灯片中。表格每个工作表对应一个图表，每张幻灯片也仅需放置一个图表。我希望通过图表ID从表格中获取图表，并通过幻灯片ID替换幻灯片中的图表占位符。目前已编写了如下代码，请问如何完成剩余部分？

def spreadsheet_chart_to_slide(spreadsheet_id, sheet_name, slides_id):
    """
    Get chart from a spreadsheet and replace chart placeholder
    """

    # Fetch chart information from sheets
    sheets_service = Create_Service('sheets', 'v4')

    sheet = sheets_service.spreadsheet().get(
        spreadsheetId = spreadsheet_id,
        ranges = [sheet_name]).execute().get('sheets')[0]
    chart_id_sheet = sheet['charts'][0]['chartId']

    # Fetch placeholder info from slides
    slide_service = Create_Service('slides', 'v1')


    # Replace chart in slides
    slide_service = Create_Service('sheets', 'v4')

    requests = []

    slide_service.presentations().batchUpdate(
        body = {
            "requests": requests
        },
        presentationId = slides_id).execute()

解决方案 #

关键修正与步骤 #

1. 修复服务实例错误：替换图表部分错误地重新创建了Sheets服务，应保留之前初始化的Slides服务实例。
2. 定位幻灯片中的图表占位符：从目标幻灯片中找到图表类型的占位符ID，用于后续替换操作。
3. 构造Slides API批量更新请求：使用replaceImageWithSheetsChart请求，将表格中的图表替换到幻灯片的指定占位符上。

完整代码实现 #

def spreadsheet_chart_to_slide(spreadsheet_id, sheet_name, slides_id, slide_index=0):
    """
    Get chart from a spreadsheet and replace chart placeholder in target slide
    :param spreadsheet_id: ID of the source Google Sheet
    :param sheet_name: Name of the sheet containing the chart
    :param slides_id: ID of the target Google Slide
    :param slide_index: Index of the slide to update (starts from 0)
    """

    # Fetch chart information from sheets
    sheets_service = Create_Service('sheets', 'v4')
    sheet = sheets_service.spreadsheet().get(
        spreadsheetId=spreadsheet_id,
        ranges=[sheet_name]
    ).execute().get('sheets')[0]
    chart_id_sheet = sheet['charts'][0]['chartId']

    # Initialize Slides service
    slide_service = Create_Service('slides', 'v1')

    # Get target slide and find chart placeholder
    presentation = slide_service.presentations().get(presentationId=slides_id).execute()
    target_slide = presentation['slides'][slide_index]
    placeholder_object_id = None

    # Iterate through slide elements to find chart placeholder
    for element in target_slide['pageElements']:
        if 'shape' in element and element['shape'].get('placeholder'):
            placeholder = element['shape']['placeholder']
            if placeholder['type'] == 'CHART':
                placeholder_object_id = element['objectId']
                break

    if not placeholder_object_id:
        raise ValueError(f"No chart placeholder found in slide index {slide_index}")

    # Construct request to replace placeholder with Sheets chart
    requests = [
        {
            "replaceImageWithSheetsChart": {
                "objectId": placeholder_object_id,
                "spreadsheetId": spreadsheet_id,
                "chartId": chart_id_sheet,
                "linkingMode": "LINKED"  # 使用LINKED可保持图表与表格联动，UNLINKED为静态副本
            }
        }
    ]

    # Execute batch update
    slide_service.presentations().batchUpdate(
        body={"requests": requests},
        presentationId=slides_id
    ).execute()

说明 #

• slide_index参数：用于指定要更新的幻灯片索引（从0开始），如果工作表和幻灯片按顺序一一对应，可在调用时动态传入对应索引。
• linkingMode选项：设置为LINKED时，幻灯片图表会与表格源图表同步更新；设置为UNLINKED则生成静态副本，不随表格数据变化。
• 错误处理：添加了占位符未找到的异常抛出，便于调试。

内容的提问来源于stack exchange，提问作者lela_rib