Google-api-php-client: Write selectively to a range in spreadsheet not working with null values

[madebyrogal]

When I try write selectively value like in this example
https://developers.google.com/sheets/samples/writing#write_selectively_to_a_range

I get Google_Service_Exception code 400 message Invalid JSON payload received
But when I use not_null type instead null, everything is ok :)

$this->serviceSheet = new Google_Service_Sheets($this->googleClient); 
$this->valueRange = new Google_Service_Sheets_ValueRange(); 
$this->valueRange->setValues($data['columns']);
$this->valueRange->setMajorDimension('ROWS'); 
$response = $this->serviceSheet->spreadsheets_values->update($this->fileID, $data['range'], $this->valueRange, array("valueInputOption" => $this->valueInputOption));

where $data['columns'] is like in example:

[[null,"$1","$2", ""], [], [null,"4/1/2016", "4/15/2016", ""]]

[bshaffer]

Have you tried using an empty string instead of null values?

[madebyrogal]

Yes, I tried, and it worked, but empty string like this '' delete value, but I wanted to skip value.

[bshaffer]

@madebyrogal can you inspect what your JSON payload looks like and add it here?

[bshaffer]

@madebyrogal have you tried using Google_Model::NULL_VALUE?

[biakaveron]

@madebyrogal have you tried using Google_Model::NULL_VALUE?

works, thank you!

[BARNZ]

Was just about to open an issue but luckily found this answer. Maybe it should be made more obvious that Google_Model::NULL_VALUE should be supplied in the dataset instead of actual php null values?

I traced the underlying issue down to the getSimpleValue function when processing an array and it encounters a php null value it skips adding it to the output array. This can lead to the output array becoming sparse / associative which when json encoded becomes a json object instead of an array. The resulting json is then rejected by the api:

{"valueInputOption":"RAW","data":[{"majorDimension":"ROWS","range":"Sheet1!Y13:AJ13","values":[{"0":"","1":"","2":"","4":"","5":"","6":"","8":"14\/05\/2019","9":"07:00:00"}]}]}

expected output:

{"valueInputOption":"RAW","data":[{"majorDimension":"ROWS","range":"Sheet1!Y13:AJ13","values":[["","","",null,"","","",null,"14\/05\/2019","07:00:00"]]}]}

[miklasch]

Google_Model::NULL_VALUE saved my sanity, thanks! Was going bonkers until Google finally lead me here. :)

[jzinkovich]

I'm have this same issue on the java-client, is there any resolution for java to this issue?

[pestka]

@madebyrogal have you tried using Google_Model::NULL_VALUE?

This is unbelievable that it is nowhere to be found in google documentation. So much wasted time.
It is clear that google does not care about API documentation.
Thank you, sir.