如何按列名动态设置Google Sheets单元格格式？

我目前需要按列名为Google Sheets单元格设置日期和小数格式。已实现按列范围设置格式的PHP代码，但希望修改代码以支持按列名设置：若列名为“Doc. Date”则设为dd-mm-yyyy日期格式，若列名为“Amount(MYR)”则设为#,##0.00小数格式，请问需要修改哪部分代码？

原代码如下：

//Date Format
new \Google\Service\Sheets\Request([
    "repeatCell" => [
        "range" => [
            "sheetId" => $sheet_id,
            "startColumnIndex" => 1,
            "endColumnIndex" => 3,
        ],
        "cell" => [
            "userEnteredFormat" => [
                "numberFormat" => [
                "type" => "DATE",
                "pattern" => "dd-mm-yyyy"
                ]
            ]
        ],
        "fields" => "userEnteredFormat.numberFormat",
    ],
]),

//Decimal Format
new \Google\Service\Sheets\Request([
    "repeatCell" => [
        "range" => [
            "sheetId" => $sheet_id,
            "startColumnIndex" => 2,
            "endColumnIndex" => (sizeof($this->dataCols->all())),
        ],
        "cell" => [
            "userEnteredFormat" => [
                "numberFormat" => [
                "type" => "NUMBER",
                "pattern" => "#,##0.00"
                ]
            ]
        ],
        "fields" => "userEnteredFormat.numberFormat",
    ],
]),

Google Sheets API不支持直接通过列名指定格式范围，必须先将列名转换为对应的列索引，再修改原代码中的范围参数。具体修改步骤如下：

1. 先获取表头列名与索引的映射 #

先调用API读取表头行数据，构建列名到索引的映射数组：

// 读取表头行（假设表头在第1行，对应range格式为'工作表名称!1:1'）
$response = $service->spreadsheets_values->get($spreadsheetId, 'Sheet1!1:1');
$headerRow = $response->getValues()[0];

// 生成列名到索引的映射
$columnMap = [];
foreach ($headerRow as $index => $columnName) {
    $columnMap[trim($columnName)] = $index;
}

2. 修改格式请求中的范围参数 #

把原代码中硬编码的startColumnIndex和endColumnIndex替换为通过映射得到的动态索引：

日期格式请求修改 #

// 获取"Doc. Date"对应的列索引
$docDateColIndex = $columnMap['Doc. Date'] ?? -1;
if ($docDateColIndex !== -1) {
    $dateRequest = new \Google\Service\Sheets\Request([
        "repeatCell" => [
            "range" => [
                "sheetId" => $sheet_id,
                "startColumnIndex" => $docDateColIndex,
                "endColumnIndex" => $docDateColIndex + 1, // 左闭右开规则，指定整列
            ],
            "cell" => [
                "userEnteredFormat" => [
                    "numberFormat" => [
                        "type" => "DATE",
                        "pattern" => "dd-mm-yyyy"
                    ]
                ]
            ],
            "fields" => "userEnteredFormat.numberFormat",
        ],
    ]);
}

小数格式请求修改 #

// 获取"Amount(MYR)"对应的列索引
$amountColIndex = $columnMap['Amount(MYR)'] ?? -1;
if ($amountColIndex !== -1) {
    $decimalRequest = new \Google\Service\Sheets\Request([
        "repeatCell" => [
            "range" => [
                "sheetId" => $sheet_id,
                "startColumnIndex" => $amountColIndex,
                "endColumnIndex" => $amountColIndex + 1,
            ],
            "cell" => [
                "userEnteredFormat" => [
                    "numberFormat" => [
                        "type" => "NUMBER",
                        "pattern" => "#,##0.00"
                    ]
                ]
            ],
            "fields" => "userEnteredFormat.numberFormat",
        ],
    ]);
}

3. 执行批量更新 #

将上述两个请求加入到批量更新的请求数组中，调用API执行即可。

关键修改点总结 #

• 新增读取表头并建立列名-索引映射的逻辑，这是实现按列名设置格式的核心前提
• 替换原代码中硬编码的列索引为动态获取的索引
• 增加索引存在性判断，避免列名不存在时触发错误

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