How to BatchUpdate Google Sheets appending numbers instead of strings/apostrophes

Question

I spent time and developed a solution to append data to a Google sheet. I am using the PHP library and things are working well.

I have a function that works fine. I can append data when I need to. The function is:

    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();
        $value->setStringValue($d);
        $cellData->setUserEnteredValue($value);
        $values[] = $cellData;
    }
    // Build the RowData
    $rowData = new Google_Service_Sheets_RowData();
    $rowData->setValues($values);
    // Prepare the request
    $append_request = new Google_Service_Sheets_AppendCellsRequest();
    $append_request->setSheetId($sheetId);
    $append_request->setRows($rowData);
    $append_request->setFields('userEnteredValue');
    // Set the request
    $request = new Google_Service_Sheets_Request();
    $request->setAppendCells($append_request);
    // Add the request to the requests array
    $requests = array();
    $requests[] = $request;
    // Prepare the update
    $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest(array(
        'requests' => $requests
    ));

    try {
        // Execute the request
        $response = $sheetsService->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);
        if ($response->valid()) {            
            return true;// Success, the row has been added
        }
    } catch (Exception $e) {        
        error_log($e->getMessage());// Something went wrong
    }

    return false;
}

The issue is: if I send through the following array:

('Joe', 'Schmo', 23)

What gets inserted is:

Joe | Shmo | '23

How can I send through this data and not have the number get an apostrophe. What I want is:

Joe | Shmo | 23

-- Update based on answer:

If I set the array like this:

        $values = array('25000.00','test', intval(2));

I am trying to force the type by pushing intval(). The issue is that this triggers the following error:

Google_Service_Exception in REST.php line 118:
{
"error": {
"code": 400,
"message": "Invalid value at 'requests[0].append_cells.rows.values[2].user_entered_value.string_value' (TYPE_STRING), 2",
"errors": [
{
"message": "Invalid value at 'requests[0].append_cells.rows.values[2].user_entered_value.string_value' (TYPE_STRING), 2",
"domain": "global",
"reason": "badRequest"
}
],
"status": "INVALID_ARGUMENT"
}
}

thanks

Answer 1

In excel '23 is a string (characters) and 23 is a number. The array element needs to be converted.

See:Openpyxl Numbers Stored as Text

Answer 2

After a ton of review, googling and other crap. I have figured out the issue. The issue is simple. This code where I loop through values:

foreach ($newValues AS $d) {
    $cellData = new Google_Service_Sheets_CellData();
    $value = new Google_Service_Sheets_ExtendedValue();
    $value->setStringValue($d);
    $cellData->setUserEnteredValue($value);
    $values[] = $cellData;
}

Needs a small update:

foreach ($newValues AS $d) {
        $cellData = new Google_Service_Sheets_CellData();
        $value = new Google_Service_Sheets_ExtendedValue();
        if(is_numeric($d)){
            $value->setNumberValue($d);
        } else {
            $value->setStringValue($d);
        }           
        $cellData->setUserEnteredValue($value);
        $values[] = $cellData;
    }

The issue is that the PHP library already supports this but it was poorly documented. I had to dig into the library.

In: vendor/google/apiclient-services/src/Google/Service/Datastore/Value.php

there are other functions to handle other types of data. I just have to run an if/else or case.