如何批量更新Google Sheets时追加数字而非字符串/撇号

Got it, let's get your Google Sheets batch updates inserting actual numbers instead of those string values with apostrophes—here's how to fix this:

The root of your issue is that you're using setStringVal() for all values, which tells Google Sheets to treat everything as text (hence the leading apostrophe). To insert proper numbers, we need to use the setNumberVal() method from the Google Sheets PHP client library, and conditionally apply it based on the value type.

Modified addRowToSpreadsheet Function #

Here's your updated function that automatically handles numeric values (integers and floats) as actual numbers, while keeping non-numeric values as strings:

function addRowToSpreadsheet($sheetsService, $spreadsheetId, $sheetId, $newValues = []) {
    // Build the CellData array
    $values = [];
    foreach ($newValues AS $d) {
        $cellData = new Google_Service_Sheets_CellData();
        $value = new Google_Service_Sheets_ExtendedValue();
        
        // Check if the value is numeric (either a number type or numeric string)
        if (is_numeric($d)) {
            // Convert numeric strings to actual number types (int/float)
            $numericValue = is_float($d) ? $d : (int)$d;
            $value->setNumberVal($numericValue);
        } else {
            // Keep non-numeric values as strings
            $value->setStringVal($d);
        }
        
        $cellData->setUserEnteredValue($value);
        $values[] = $cellData;
    }

    // Build and execute the append request
    $rowData = new Google_Service_Sheets_RowData();
    $rowData->setValues($values);
    
    $appendRequest = new Google_Service_Sheets_AppendCellsRequest();
    $appendRequest->setSheetId($sheetId);
    $appendRequest->setRows([$rowData]);
    $appendRequest->setFields('userEnteredValue');
    
    $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
    $batchUpdateRequest->setRequests([
        new Google_Service_Sheets_Request([
            'appendCells' => $appendRequest
        ])
    ]);
    
    return $sheetsService->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);
}

Batch Appending Multiple Rows #

If you need to append multiple rows at once (true batch processing), you can extend this logic to loop through each row and add all requests to a single batch update:

// Example: Batch append 3 rows with mixed numeric and string data
$rowsToAppend = [
    [100, "Wireless Headphones", 79.99],
    [200, "Bluetooth Speaker", 49.99],
    [300, "Phone Case", 14.99]
];

$requests = [];
foreach ($rowsToAppend as $rowValues) {
    $cellDataArray = [];
    foreach ($rowValues AS $d) {
        $cellData = new Google_Service_Sheets_CellData();
        $value = new Google_Service_Sheets_ExtendedValue();
        
        if (is_numeric($d)) {
            $numericValue = is_float($d) ? $d : (int)$d;
            $value->setNumberVal($numericValue);
        } else {
            $value->setStringVal($d);
        }
        
        $cellData->setUserEnteredValue($value);
        $cellDataArray[] = $cellData;
    }
    
    $rowData = new Google_Service_Sheets_RowData();
    $rowData->setValues($cellDataArray);
    
    $appendRequest = new Google_Service_Sheets_AppendCellsRequest();
    $appendRequest->setSheetId($sheetId);
    $appendRequest->setRows([$rowData]);
    $appendRequest->setFields('userEnteredValue');
    
    $requests[] = new Google_Service_Sheets_Request([
        'appendCells' => $appendRequest
    ]);
}

// Execute the batch update
$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
$batchUpdateRequest->setRequests($requests);
$sheetsService->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);

Important Notes #

• Value Type Check: The is_numeric() function handles both actual number types (int/float) and string representations of numbers (like "123"). We convert string numbers to proper number types to ensure Google Sheets recognizes them correctly.
• fields Parameter: Setting fields to userEnteredValue ensures we only update the cell's value, not other properties like formatting or data validation.
• Testing: Make sure your input values are properly typed—if you're pulling data from a database or CSV, double-check that numeric columns are being cast to int/float before passing them to the function.

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