I'm writing an app previously held by another developer. After a certain treatment, he wanted to fill a Google Sheets file with values. Before he could start developing, he was gone, leaving me with the task to understand the google-api-client-php library.
I manage to insert values (that was a big step for me) but I would like to add a background color to certain cells. I didn't find any way to acheive this...
For now, that's how I insert values :
class Sheet {
public function __construct($client) {
$this->service = new \Google_Service_Sheets($client);
}
public function write($line, $newValues, $startColumn)
{
$values = new \Google_Service_Sheets_ValueRange();
$values->setValues([ $newValues ]);
$this->service->spreadsheets_values->update($this->id, $range, $values, ['valueInputOption' => 'USER_ENTERED']);
}
}
And I'd like to create a colorLine() function.
Here my first try :
public function colorLine($line, $r, $g, $b, $a = 1) {
$myRange = [
'sheetId' => 1,
'startRowIndex' => $line,
'endRowIndex' => $line,
'startColumnIndex' => 0,
'endColumnIndex' => 1000,
];
$requests = [
new \Google_Service_Sheets_Request([
'addConditionalFormatRule' => [
'rule' => [
'ranges' => [ $myRange ],
'booleanRule' => [
'condition' => [
'type' => 'CUSTOM_FORMULA',
'values' => [ [ 'userEnteredValue' => '=1' ] ]
],
'format' => [
'backgroundColor' => [ 'red' => $r, 'green' => $g, 'blue' => $b ]
]
]
],
'index' => 1
]
])
];
$batchUpdateRequest = new \Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
'requests' => $requests
]);
$response = $this->service->spreadsheets->batchUpdate($this->id,
$batchUpdateRequest);
}
First, I don't even really understand what I've written... Plus, it's saying "Invalid requests[0].addConditionalFormatRule: No grid with id: 1", but it's not that bad, I don't think it would have done what I'm looking for.
I think it would create a "conditional format", but I just want a background... This API looks very complicated for simple applications...
Anyway! If someone could help me, I would be very thankful!