Google Spreadsheets API: Notes in cells

Question

I made a php script to export some information to google spreadsheets using api v4. When I try to add notes into a cell I should do a request to the API for every note, and this arises a issue for me, explained ahead.

My current approach is the following one, I'm trying to add notes on every column of a single row ($headerNotes is an array):

public function writeHeaderNotes($headerNotes, $spreadsheetId, $sheetId, $row)
{
    $client = $this->getClient();
    $service = new Google_Service_Sheets($client);
    $cellstart = 0;
    $cellend = 1;

    foreach ($headerNotes as $note)
    {
        $body = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest(array(
            'requests' => array(
                'updateCells' => array(
                    'rows' => array(
                        'values' => array(
                            'note' => $note)),
                    'fields' => 'note',
                    'range' => array(
                        'sheetId' => $sheetId,
                        'startRowIndex' => $row-1,
                        'endRowIndex' => $row,
                        'startColumnIndex' => $cellstart,
                        'endColumnIndex' => $cellend)))
        ));
        $service->spreadsheets->batchUpdate($spreadsheetId, $body);
        $cellstart++;
        $cellend++;
    }

}

In this approach, notes are added one by one (using loop) and it's working fine. The main problem is that I'm doing as many requests as notes and google has limited cuotas. (100 write requests per user per 100 seconds). I need to optimize the number of requests and I need make just one request to add different notes in different cells of the same row. Reading google spreadsheet documentation, i can see that "note" node in request, is asking for a string value:

  "note": string,

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#updatecellsrequest https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/sheets#RowData https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#CellData

How could I add different notes to different single-row cells, without the need to make so many requests?

Andres Duarte Andres Duarte · Accepted Answer · 2019-11-13T13:07:44

As stated in the docs for the RowData object [1], the values field is an array where each value (CellData object) is the value to be inserted in each cell. You also need to use a Range that can cover all the values you want to insert in each row. I modified and tested your code to insert notes in the first 5 columns of the first row and worked succesfully:

$client = getClient();
$service = new Google_Service_Sheets($client);
$cellstart = 0;
$cellend = 5;
$note = "whatever";
$row = 1;

$body = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest(array(
    'requests' => array(
        'updateCells' => array(
            'rows' => array(
                'values' => array(
                    array('note' => $note),
                    array('note' => $note),
                    array('note' => $note),
                    array('note' => $note),
                    array('note' => $note)
                )
            ),
            'fields' => 'note',
            'range' => array(
                'sheetId' => $sheetId,
                'startRowIndex' => $row-1,
                'endRowIndex' => $row,
                'startColumnIndex' => $cellstart,
                'endColumnIndex' => $cellend
            )
        )
    )
));
$service->spreadsheets->batchUpdate($spreadsheetId, $body)

[1] https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/sheets#RowData

Google Spreadsheets API: Notes in cells

1 Answers