如何通过Google Sheets API批量为所有工作表应用样式？

一、应用样式到所有工作表 #

如果要对所有工作表生效，且不想提前调用API获取工作表列表，可通过以下方式实现：

核心思路 #

直接通过工作表名称指定操作目标，无需依赖sheetId。前提是你已知所有需要操作的工作表名称（若无法提前知晓，只能先调用spreadsheets.get获取工作表列表，这是Google Sheets API的机制限制，无通配符可直接匹配所有工作表）。

代码实现

// 定义需要操作的所有工作表名称
$sheetNames = ['Sheet1', 'Sheet2', '月度报表'];
$formatRowColrequests = [];

foreach ($sheetNames as $sheetName) {
    // 1. 冻结首行
    $formatRowColrequests[] = new \Google\Service\Sheets\Request([
        'updateSheetProperties' => [
            'properties' => [
                'title' => $sheetName, // 通过名称指定工作表
                'gridProperties' => [
                    'frozenRowCount' => 1
                ]
            ],
            'fields' => 'gridProperties.frozenRowCount'
        ]
    ]);

    // 2. 列自适应宽度
    $formatRowColrequests[] = new \Google\Service\Sheets\Request([
        "autoResizeDimensions" => [
            "dimensions" => [
                "sheetName" => $sheetName,
                "dimension" => "COLUMNS",
                "startIndex" => 0,
            ]
        ]
    ]);

    // 3. 首行样式设置
    $formatRowColrequests[] = new \Google\Service\Sheets\Request([
        "repeatCell" => [
            "range" => [
                "sheetName" => $sheetName,
                "endRowIndex" => 1,
            ],
            "cell" => [
                "userEnteredFormat" => [
                    "backgroundColor" => [
                        "red" => 152 / 255,
                        "green" => 217 / 255,
                        "blue" => 99 / 255,
                        "alpha" => 0.8
                    ],
                    "horizontalAlignment" => "LEFT",
                    "textFormat" => [
                        "fontSize" => 12,
                        "bold" => true,
                    ]
                ]
            ],
            "fields" => "userEnteredFormat(textFormat,backgroundColor,horizontalAlignment)"
        ]
    ]);

    // 4. 正文行样式设置
    $formatRowColrequests[] = new \Google\Service\Sheets\Request([
        "repeatCell" => [
            "range" => [
                "sheetName" => $sheetName,
                "startRowIndex" => 1,
            ],
            "cell" => [
                "userEnteredFormat" => [
                    "horizontalAlignment" => "LEFT",
                    "textFormat" => [
                        "fontSize" => 10,
                        "bold" => false
                    ]
                ]
            ],
            "fields" => "userEnteredFormat(textFormat,backgroundColor,horizontalAlignment)"
        ],
    ]);
}

// 执行批量更新
$batchUpdateCellFormatRequest = new \Google\Service\Sheets\BatchUpdateSpreadsheetRequest([
    'requests' => $formatRowColrequests,
]);
$service->spreadsheets->batchUpdate($target_spreadID, $batchUpdateCellFormatRequest);

二、按工作表名称分别设置差异化样式 #

如果需要给不同工作表设置不同样式，只需在循环中根据名称分支处理即可：

// 定义不同工作表的差异化样式配置
$sheetStyles = [
    'Sheet1' => [
        'headerBg' => ['red' => 152/255, 'green' => 217/255, 'blue' => 99/255, 'alpha' => 0.8],
        'headerFontSize' => 12,
        'bodyFontSize' => 10
    ],
    '月度报表' => [
        'headerBg' => ['red' => 255/255, 'green' => 217/255, 'blue' => 99/255, 'alpha' => 0.8],
        'headerFontSize' => 14,
        'bodyFontSize' => 11
    ]
];

$formatRowColrequests = [];

foreach ($sheetStyles as $sheetName => $style) {
    // 冻结首行（通用设置）
    $formatRowColrequests[] = new \Google\Service\Sheets\Request([
        'updateSheetProperties' => [
            'properties' => [
                'title' => $sheetName,
                'gridProperties' => [
                    'frozenRowCount' => 1
                ]
            ],
            'fields' => 'gridProperties.frozenRowCount'
        ]
    ]);

    // 列自适应宽度（通用设置）
    $formatRowColrequests[] = new \Google\Service\Sheets\Request([
        "autoResizeDimensions" => [
            "dimensions" => [
                "sheetName" => $sheetName,
                "dimension" => "COLUMNS",
                "startIndex" => 0,
            ]
        ]
    ]);

    // 首行样式（差异化设置）
    $formatRowColrequests[] = new \Google\Service\Sheets\Request([
        "repeatCell" => [
            "range" => [
                "sheetName" => $sheetName,
                "endRowIndex" => 1,
            ],
            "cell" => [
                "userEnteredFormat" => [
                    "backgroundColor" => $style['headerBg'],
                    "horizontalAlignment" => "LEFT",
                    "textFormat" => [
                        "fontSize" => $style['headerFontSize'],
                        "bold" => true,
                    ]
                ]
            ],
            "fields" => "userEnteredFormat(textFormat,backgroundColor,horizontalAlignment)"
        ]
    ]);

    // 正文行样式（差异化设置）
    $formatRowColrequests[] = new \Google\Service\Sheets\Request([
        "repeatCell" => [
            "range" => [
                "sheetName" => $sheetName,
                "startRowIndex" => 1,
            ],
            "cell" => [
                "userEnteredFormat" => [
                    "horizontalAlignment" => "LEFT",
                    "textFormat" => [
                        "fontSize" => $style['bodyFontSize'],
                        "bold" => false
                    ]
                ]
            ],
            "fields" => "userEnteredFormat(textFormat,backgroundColor,horizontalAlignment)"
        ],
    ]);
}

// 执行批量更新
$batchUpdateCellFormatRequest = new \Google\Service\Sheets\BatchUpdateSpreadsheetRequest([
    'requests' => $formatRowColrequests,
]);
$service->spreadsheets->batchUpdate($target_spreadID, $batchUpdateCellFormatRequest);

关键注意事项 #

• 所有请求通过sheetName指定目标工作表，避免依赖sheetId，无需提前调用API获取工作表列表。
• 若必须覆盖所有未知名称的工作表，唯一方式是先调用$service->spreadsheets->get($target_spreadID)获取sheets数组，提取每个工作表的title或sheetId后再循环生成请求。

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