CODEWITHSUNDEEP
phpgoogle-sheetsgoogle-sheets-api
Ja Nosch
Ja Nosch

Reputation: 423

Formatting cells background and bold in google sheet (php)

I'm trying to change the background color and the bold value of a couple of cells in my spreadsheet. I know I have a working service because I'm doing a spreadsheets_values update right before and it works.

Changing values works so I'm guessing it must be something with my config. I dont get any errors, the $result variable remains empty. Any ideas would be appreciated, thanks.

// known to work from changing the values
$client = getClient();
$service = new Google_Service_Sheets($client);
$spreadsheetId = 'MY SPREADSHEET ID';

// new code
$r = $g = $b = 0.5;
$a = 1;

$myRange = [
    //'sheetId' => 0, // can be omitted because I'm working on the first sheet
    'startRowIndex' => 5,
    'endRowIndex' => 10,
    //'startColumnIndex' => 0, // can be omitted because default is 0
    'endColumnIndex' => 5,
];
$format = [
    'backgroundColor' => [
        'red' => $r,
        'green' => $g,
        'blue' => $b,
        'alpha' => $a,
    ],
    'textFormat' => [
      'bold' => true
    ]
];


$requests = [
    new Google_Service_Sheets_Request([
        'repeatCell' => [
            'fields' => 'userEnteredFormat.backgroundColor, userEnteredFormat.textFormat.bold',
            'range' => $myRange,
            'cell' => [
                'userEnteredFormat' => $format,
            ],
        ],
    ])
];


$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
  'requests' => $requests
]);
$result = $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);

Upvotes: 2

Views: 2630

Answers (1)

Ja Nosch
Ja Nosch

Reputation: 423

SOLUTION:

I made 2 mistakes here:

1) i thought error reporting was on, but it was not. @greybeard got me looking for that and after that I got the following error (this might help others):

Uncaught Google_Service_Exception: { "error": { "code": 400, "message": "Invalid requests[0].repeatCell: No grid with id: 1"

2) as you can see in the error above my grid id is rejected. this is due to the fact that I confused sheetId with sheet index. google sheets actually have a "real" id that you can get by the following request

$sheetId = $service->spreadsheets->get($spreadsheetId);

This returns an array with an object which holds the sheetIds. For the sheet with index 0 this would be

$sheetId = $sheetId->sheets[0]->properties->sheetId;

To directly access only a certain sheet and not the whole spreadsheet you can add the 'ranges' property

$sheetId = $service->spreadsheets->get($spreadsheetId, ['ranges' => 'NAME_OF_SHEET']);

so the full script would be:

$client = getClient(); // this calls my custom function that does the authentication
$service = new Google_Service_Sheets($client);
$spreadsheetId = 'MY SPREADSHEET ID';

// get sheetId of sheet with index 0
$sheetId = $service->spreadsheets->get($spreadsheetId);
$sheetId = $sheetId->sheets[0]->properties->sheetId;

// set colour to a medium gray
$r = $g = $b = 0.5;
$a = 1;

// define range
$myRange = [
    'sheetId' => $sheetId, // IMPORTANT: sheetId IS NOT the sheets index but its actual ID
    'startRowIndex' => 5,
    'endRowIndex' => 10,
    //'startColumnIndex' => 0, // can be omitted because default is 0
    'endColumnIndex' => 5,
];

// define the formatting, change background colour and bold text
$format = [
    'backgroundColor' => [
        'red' => $r,
        'green' => $g,
        'blue' => $b,
        'alpha' => $a,
    ],
    'textFormat' => [
      'bold' => true
    ]
];

// build request
$requests = [
    new Google_Service_Sheets_Request([
        'repeatCell' => [
            'fields' => 'userEnteredFormat.backgroundColor, userEnteredFormat.textFormat.bold',
            'range' => $myRange,
            'cell' => [
                'userEnteredFormat' => $format,
            ],
        ],
    ])
];

// add request to batchUpdate    
$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
  'requests' => $requests
]);

// run batchUpdate
$result = $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);

Upvotes: 2

Related Questions