Cannot filter Google Sheet via PHP / google-sheets-API because of faulty criteria definition ("cannot bind a list to map for field 'criteria'")

Question

I'm trying to apply a basic filter to a Google Sheet from my website using the Google-API-PHP-Client. I only want to display rows where the value in column A equals "global_city_actors".

Unfortunately, I cannot seem to construct the request correctly. The source of the error appears to be in the "criteria" specification, particularly the "0" key for the column index (doesn't work whether using "" or not). I get the following error message but cannot make sense of it:

Invalid value at 'requests[0].set_basic_filter.filter' (Map), Cannot bind a list to map for field 'criteria'.", "errors": [ { "message": "Invalid value at 'requests[0].set_basic_filter.filter' (Map), Cannot bind a list to map for field 'criteria'.", "reason": "invalid" } ], "status": "INVALID_ARGUMENT" } }

I would be grateful for any leads. Here is my code:

// The first bit is inspired by: https://www.fillup.io/post/read-and-write-google-sheets-from-php/ 
require_once(APPPATH.'third_party/google-api-php-client-2.2.3/vendor/autoload.php');
$this->client = new Google_Client();
$this->client->setApplicationName('My Test API');
$this->client->setScopes([Google_Service_Sheets::SPREADSHEETS]);
$this->client->setAccessType('offline');
$this->client->setAuthConfig(APPPATH.'third_party/google-api-php-client-2.2.3/credentials/.....json');
$this->service = new Google_Service_Sheets($this->client);
$this->spreadsheetId = '1yRg6Kai6MwKcE8ZKdCggfWwPZVIOu-MfByvgWLSEiUI';

// This is the faulty request:
$requests = [
              new Google_Service_Sheets_Request([
                  'setBasicFilter' => [
                      'filter' => [
                          'range' => [
                            'sheetId' => 0,
                            'startColumnIndex' => 0,
                            'endColumnIndex' => 0
                          ],
                        'criteria' => [
                          "0" => [
                              'condition' => [
                                'type' => 'TEXT_EQ',
                                'values' => [
                                  'userEnteredValue' => 'global_city_actors'
                                ]
                              ]
                          ]
                        ]
                      ]
                  ]
              ])
            ];

$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
      'requests' => $requests
]);


try {
        $response_object = $this->service->spreadsheets->batchUpdate($this->spreadsheetId, $batchUpdateRequest);
}
catch (Exception $e) {
        return show_error('An error occurred: <strong>'.$e->getMessage().'</strong></p>');
}

score 0 · Answer 1 · answered Nov 18 '19 at 16:55

0

I also was able to reproduce the error, which affects only when trying to create the filter for the first column. This error has already been reported in Google's issue tracker: http://issuetracker.google.com/issues/144692636

answered Nov 18 '19 at 16:55

Andres Duarte

3,166
1
7
14

Thanks for checking and reporting it! I suppose there is nothing to be done other than wait for the resolution (or move the content to be filtered to another column). – Chris G. Nov 20 '19 at 16:02
Yes that's right. Although the problem is only for PHP library so you could make the post request using PHP methods, adding the authorization token in the headers https://stackoverflow.com/questions/5647461/how-do-i-send-a-post-request-with-php – Andres Duarte Nov 20 '19 at 16:32

score 0 · Answer 2 · answered Jan 08 '20 at 01:53

I just encountered this issue and thought I would mention there is a workaround using stdClass listed on the github issue - https://github.com/googleapis/google-api-php-client/issues/1748#issuecomment-560550645

$criteria = new stdClass;
$criteria->{'0'} = array(
    'condition' => array(
        'type' => 'TEXT_EQ',
        'values' => array(
            'userEnteredValue' => 'global_city_actors'
        )
    )
);
$body = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest(
    array(
        'requests' => array(
            'setBasicFilter' => array(
                'filter' => array(
                    'range' => [
                        'sheetId' => 0,
                        'startColumnIndex' => 0,
                        'endColumnIndex' => 0
                    ],
                'criteria' => $criteria
                )
            )
        )
    )
);

var_dump($service->spreadsheets->batchUpdate($spreadsheetId, $body));

The workaround worked for me. Hope that helps.

Cannot filter Google Sheet via PHP / google-sheets-API because of faulty criteria definition ("cannot bind a list to map for field 'criteria'")

2 Answers2