How to use datetimerenderoption in google sheets api v4 in php?

Question

I have to write 3 columns in google sheet using v4 api in php

$range = $sheetName;
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
$lastRow = count($values);
$rowIndex = $lastRow;
$columnIndex = 0;

$requests = array();
$requests[] = new Google_Service_Sheets_Request(array(
    'updateCells' => array(
        'start' => array(
            'sheetId' => $sheetId,
            'rowIndex' => $rowIndex,
            'columnIndex' => $columnIndex
        ),
        'rows' => array(
            array(
                'values' => array(
                    array(
                        'userEnteredValue' => array('numberValue' => '111111'),
                        'userEnteredFormat' => array('backgroundColor' => array('red'=>1, 'green'=>0.94, 'blue'=>0.8))
                    ),
                    array(
                        'userEnteredValue' => array('stringValue' => 'aaaaaaaaa'),
                        'userEnteredFormat' => array('backgroundColor' => array('red'=>1, 'green'=>0.94, 'blue'=>0.8))
                    ),
                    array(
                        'userEnteredValue' => array('numberValue' => '2015-05-05'),
                        'userEnteredFormat' => array('numberFormat' => array('type'=>'DATE', 'pattern'=>'yyyy-mm-dd'), 'backgroundColor' => array('red'=>1, 'green'=>0.94, 'blue'=>0.8))
                    )
                )
            )
        ),
        'fields' => 'userEnteredValue,userEnteredFormat.backgroundColor'
    )
));

$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest(array(
    'requests' => $requests
));

try {
    $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);
} catch(Exception $e) {
    print_R($e);
}

I am using above code to do the task, the only problem I am facing is that I am getting an error while inserting date.

I read that datetimerenderoption is to be used for inserting the date but I am not getting how to do it.

I've reformatted your code, but it would be useful to know **what** error you are getting and what you have tried with datetimerenderoption. — Matt Raines, Aug 23 '16 at 08:47
I just want to thank you for providing a decent example of how to create / work with requests in Google Sheets!! I find the API documentation not particularly helpful. — Antony, May 03 '17 at 09:09

score 1 · Answer 1 · edited May 23 '17 at 11:33

1

DateTimeRenderOption is used in the values collection APIs (and are also just for reading, not writing). You're using the spreadsheets collection APIs, which are more bare-bones and don't use that convenience option.

If you paste the error you're receiving it will be easier to help, but I'm assuming it has something to do with the lines that set a numberValue. The docs say numberValue takes a number, not a string. If you'd like to set a date, you'll need to provide the number for that date in Serial Number format. You can review this SO answer for good suggestions on dealing with serial numbers.

edited May 23 '17 at 11:33

Community

1
1

answered Aug 22 '16 at 19:51

Sam Berlin

3,603
12
23

The error coming - Invalid value at 'requests[0].update_cells.rows[0].values[2].user_entered_value.number_value' (TYPE_DOUBLE), "2016-05-05" – Amit Gupta Aug 23 '16 at 09:50
Yes, that's the error for what I thought was the problem. You're passing a strimg to something that wants a number. See my above answer. – Sam Berlin Aug 23 '16 at 11:06
I am not getting how to set the serial number format in PHP. Please help – Amit Gupta Aug 23 '16 at 11:18

How to use datetimerenderoption in google sheets api v4 in php?

1 Answers1