Invalid value at 'requests[0].update_cells.rows[0]'

Question

I am having lots of difficulty with the Google PHP API Client.

I modified the REST.php class to dump the JSON that is getting sent to the Google Sheets API.

The JSON that gets generated is this:

{
    "requests": [
        {
            "updateCells": {
                "fields": "*",
                "rows": [
                    [
                        "0",
                        "1",
                        "2",
                        "3"
                    ],
                    [
                        "this",
                        "is",
                        "a",
                        "csv"
                    ]
                ],
                "start": {
                    "columnIndex": 0,
                    "rowIndex": 0,
                    "sheetId": 1503856757
                }
            }
        }
    ]
}

Using the OAuth Playground I can see that I've got invalid values. I've no idea why the values are invalid.

I have an "Insert Dimension Request" commented out. I set the the dimension to be 10 rows and 10 columns just to get started and try and figure out why the values are invalid. The error response gives no indication as to why these values are invalid.

I was able to get the Insert Dimension Requests to work via the OAuth Playground, but for the life of me I cannot get the updateCellsRequest to work.

I get the exact same error when using AppendCellsRequest as well.

Is there anywhere to get more detailed analysis of the errors that are being returned?

What am I doing wrong?

// https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#updatecellsrequest
// Rows, Fields, Start, Range
$updateCellsRequest = new \Google_Service_Sheets_UpdateCellsRequest();

$rowData = array();
$lexer = new Lexer(new LexerConfig());
$interpreter = new Interpreter();
$interpreter->addObserver(function(array $row) use (&$rowData, &$range) {
    $sheetRowData = array();

    foreach ($row as $column => $value) {
        $sheetCellData = new \Google_Service_Sheets_CellData();
        $eValue = new \Google_Service_Sheets_ExtendedValue();
        if (is_numeric($value)) {
            $eValue->setNumberValue($value);
        } else {
            $eValue->setStringValue($value);
        }
        $sheetCellData->setUserEnteredValue($eValue);
        $sheetRowData[] = $sheetCellData;
    }

    $sheetRowData = new \Google_Service_Sheets_RowData($sheetRowData);
    $rowData[] = $row;
});
$lexer->parse($fileName, $interpreter);

$updateCellsRequest->setRows($rowData);
$updateCellsRequest->setFields('*');

$gridCoord = new \Google_Service_Sheets_GridCoordinate();
$gridCoord->setSheetId($gSheet->getProperties()->getSheetId());
$gridCoord->setRowIndex(0);
$gridCoord->setColumnIndex(0);

$updateCellsRequest->setStart($gridCoord);

$range = new \Google_Service_Sheets_GridRange();
$range->setSheetId($gSheet->getProperties()->getSheetId());
$range->setStartRowIndex(0);
$range->setStartColumnIndex(0);
$updateCellsRequest->setRange($range);

// Google Sheets Service
$sheetsService = new \Google_Service_Sheets($this->client);

// Create a batch update request
$updateRequest = new \Google_Service_Sheets_BatchUpdateSpreadsheetRequest();

// // Insert Dimension Request
// $idr = new \Google_Service_Sheets_InsertDimensionRequest();

// $range = new \Google_Service_Sheets_DimensionRange();
// $range->setSheetId($gSheet->getProperties()->getSheetId());
// $range->setStartIndex(0);
// $range->setDimension('ROWS');
// $range->setEndIndex(sizeof($rowData));

// $idr->setRange($range);


// add a sheets request
$sheetsRequest = new \Google_Service_Sheets_Request();
$sheetsRequest->setUpdateCells($updateCellsRequest);
// $sheetsRequest->setInsertDimension($idr);

// add the sheets request to our batch update
$updateRequest->setRequests(array($sheetsRequest)); 

Edit

The error:

{
  "error": {
    "status": "INVALID_ARGUMENT", 
    "message": "Invalid value at 'requests[0].append_cells.rows[0]' (type.googleapis.com/google.apps.sheets.v4.RowData), \"0\"\nInvalid value at 'requests[0].append_cells.rows[1]' (type.googleapis.com/google.apps.sheets.v4.RowData), \"1\"\nInvalid value at 'requests[0].append_cells.rows[2]' (type.googleapis.com/google.apps.sheets.v4.RowData), \"2\"\nInvalid value at 'requests[0].append_cells.rows[3]' (type.googleapis.com/google.apps.sheets.v4.RowData), \"3\"\nInvalid value at 'requests[0].append_cells.rows[0]' (type.googleapis.com/google.apps.sheets.v4.RowData), \"this\"\nInvalid value at 'requests[0].append_cells.rows[1]' (type.googleapis.com/google.apps.sheets.v4.RowData), \"is\"\nInvalid value at 'requests[0].append_cells.rows[2]' (type.googleapis.com/google.apps.sheets.v4.RowData), \"a\"\nInvalid value at 'requests[0].append_cells.rows[3]' (type.googleapis.com/google.apps.sheets.v4.RowData), \"csv\"", 
    "code": 400, 
    "details": [
      {
        "fieldViolations": [
          {
            "field": "requests[0].append_cells.rows[0]", 
            "description": "Invalid value at 'requests[0].append_cells.rows[0]' (type.googleapis.com/google.apps.sheets.v4.RowData), \"0\""
          }, 
          {
            "field": "requests[0].append_cells.rows[1]", 
            "description": "Invalid value at 'requests[0].append_cells.rows[1]' (type.googleapis.com/google.apps.sheets.v4.RowData), \"1\""
          }, 
          {
            "field": "requests[0].append_cells.rows[2]", 
            "description": "Invalid value at 'requests[0].append_cells.rows[2]' (type.googleapis.com/google.apps.sheets.v4.RowData), \"2\""
          }, 
          {
            "field": "requests[0].append_cells.rows[3]", 
            "description": "Invalid value at 'requests[0].append_cells.rows[3]' (type.googleapis.com/google.apps.sheets.v4.RowData), \"3\""
          }, 
          {
            "field": "requests[0].append_cells.rows[0]", 
            "description": "Invalid value at 'requests[0].append_cells.rows[0]' (type.googleapis.com/google.apps.sheets.v4.RowData), \"this\""
          }, 
          {
            "field": "requests[0].append_cells.rows[1]", 
            "description": "Invalid value at 'requests[0].append_cells.rows[1]' (type.googleapis.com/google.apps.sheets.v4.RowData), \"is\""
          }, 
          {
            "field": "requests[0].append_cells.rows[2]", 
            "description": "Invalid value at 'requests[0].append_cells.rows[2]' (type.googleapis.com/google.apps.sheets.v4.RowData), \"a\""
          }, 
          {
            "field": "requests[0].append_cells.rows[3]", 
            "description": "Invalid value at 'requests[0].append_cells.rows[3]' (type.googleapis.com/google.apps.sheets.v4.RowData), \"csv\""
          }
        ], 
        "@type": "type.googleapis.com/google.rpc.BadRequest"
      }
    ]
  }
}

Answer 1

Alright, I finally figured this out.

I was constructing the rows incorrectly. You must use RowData, and CellData appropriately.

The end result was to first create an UpdateSheetProperties request and an UpdateCellsRequest.

    $updateCellsRequest = new \Google_Service_Sheets_UpdateCellsRequest(array(
        'fields' => '*',
        'start'  => new \Google_Service_Sheets_GridCoordinate(array(
            'sheetId' => $gSheet->getProperties()->getSheetId(),
            'rowIndex' => 0,
            'columnIndex' => 0
        ))
    ));

    // update sheet properties request
    $updateSheetPropertiesRequest = new \Google_Service_Sheets_UpdateSheetPropertiesRequest(array(
        'fields' => '*',
        'properties' => new \Google_Service_Sheets_SheetProperties(array(
            'index' => 0,
            'title'  => $this->generateSheetTitle($season),
            'sheetId' => $gSheet->getProperties()->getSheetId(),
            'gridProperties' => new \Google_Service_Sheets_GridProperties(array(
                'rowCount' => 2,
                'columnCount' => 4
            ))
        ))
    ));

Then, add the rows to the updateCellsRequest object:

    $rowData = array();
    $lexer = new Lexer(new LexerConfig());
    $interpreter = new Interpreter();
    $interpreter->addObserver(function(array $row) use (&$rowData) {
        $sheetRowData = new \Google_Service_Sheets_RowData();
        $cellData = array();

        foreach ($row as $column => $value) {
            $sheetCellData = new \Google_Service_Sheets_CellData(array(
                'userEnteredValue' => new \Google_Service_Sheets_ExtendedValue(array(
                    is_numeric($value) ? 'numberValue' : 'stringValue' => $value
                ))
            ));
            $cellData[] = $sheetCellData;
        }
        $sheetRowData->setValues($cellData);
        $rowData[] = $sheetRowData;
    });
    $lexer->parse($fileName, $interpreter);

    $updateCellsRequest->setRows($rowData);

And lastly, create the batch request object:

    // Google Sheets Service
    $sheetsService = new \Google_Service_Sheets($this->client);

    // Create a batch update request
    $updateRequest = new \Google_Service_Sheets_BatchUpdateSpreadsheetRequest(array(
        'requests' => array(
            new \Google_Service_Sheets_Request(array('updateSheetProperties' => $updateSheetPropertiesRequest)),
            new \Google_Service_Sheets_Request(array('updateCells' => $updateCellsRequest))
        )
    ));

    // send the request
    $sheetsService->spreadsheets->batchUpdate($spreadsheetId, $updateRequest);