CODEWITHSUNDEEP
phpgoogle-sheetsgoogle-sheets-apispreadsheet
Teodor Todorov
Teodor Todorov

Reputation: 3

Google Sheets API, Read Values by Filter

I am trying to use Google Sheets API with PHP for reading and writing values to google spreadsheet. At the moment I can write values inside the sheet using append method.

The problem is I am trying to read only the rows that match some condition like 'date beetween' or 'user name'. For the purpose I think I have to use this method batchGetByDataFilter, but I am not sure exactly how to implement it in my php script.

This is part of my code:

    $client = GoogleSheets::getClient();
    $service = new Google_Service_Sheets($client);

    $spreadsheetId = '1nZ0ybxUucbeNPVE_opKLdSFJVHmWIo1hVcvuQA4H-aY';
    $range = 'Sheet1!A2:J';

    $response = $service->spreadsheets_values->batchGetByDataFilter($spreadsheetId);
    echo "Return Values From Spreadsheet: \n";
    var_dump($response);exit;
    
    return $response->getValues();

Upvotes: 0

Views: 7524

Answers (1)

Jescanellas
Jescanellas

Reputation: 2608

The DataFilter only filters by developer metadata and by ranges but not by values.

To achieve this, you will have to get a specific range of values and loop through them, returning only the matched values. You can copy the code from the Quickstart which returns values from a Sheet. Change the Sheet Id and the Range to fit your needs. Also, once it finds data, you should use an if condition:

...

if (empty($values)) {
    print "No data found.\n";
} else {
    print "Data found.\n";
    foreach ($values as $row) {

        // Print column A if there is a match
        if ($row[0] == "some username"){
            printf("%s, %s\n", $row[0], $row[4]);
        }
    }
}

Upvotes: 1

Related Questions