How do i search Google Sheet by a value stored in it

Question

I need to search for a specific value that I stored in a sheet and get the entire row or the location of the cell that the value is store.

Note: My sheet contains more than 10000 rows of data and I need to update a single column. I'm not interested to fetch all the data from the sheet and update it, as it will affect the performance of my site.

please help me to find a solution.

When you store that value, set some developer metadata on it as well. You can then filter by your developer metadata. https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchGetByDataFilter — tehhowch, Aug 06 '18 at 22:09
I think this is part of asked here : https://stackoverflow.com/questions/49161249/google-sheets-api-how-to-find-a-row-by-value-and-update-its-content — 4givN, Jun 13 '19 at 14:07

score 2 · Answer 1 · answered Aug 19 '20 at 23:26

Better late than never!? Well I also had the same problem but I needed to change multiple fields, I even looked at the links above but nothing in php. The code below also works if you need to change just one field or several. (google translate)

<?php
     $myvalue = 'NEW VALUE';
    $values = [[$c_id,]];
    // make conn with credentials
    $client = new \Google_Client();
    $client->setApplicationName('Google Sheets with PHP');
    $client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
    $client->setAccessType('offline');
    $client->setAuthConfig(__DIR__ . '/cred.json');
    $service = new Google_Service_Sheets($client);
    $spreadsheetId = "your-id";

    // insert custom range to match with name of spreadsheet and range to search  
    $range = "COMPLETO!A2:A50000";
    $cell_id;
    $cell_range;
    $response = $service->spreadsheets_values->get($spreadsheetId, $range);
    $values_r = $response->getValues();           
    if (empty($values_r)) {
        print "None Found.\n";
    } else {
        print "Data found\n";
        $range_index = '1';
        foreach ($values_r as $row) {
            // Show the results in array 
            $range_index++;
            // Match com id do banco de dados
            if($row[0] === $c_id){
                echo "ID found\n";
                echo "$row[0]\n";
                echo "Cell ID A${range_index}\n";
                $cell_id = "A${range_index}";
                // in $cell_range set the effective range to change
               // $cell_range = "A${range_index}:CM${range_index}";
                break;
            }
        }
    }

    $body = new Google_Service_Sheets_ValueRange([
    'values' => $values
    ]);
    // try Update
     $append_sheet = $service->spreadsheets_values->update($spreadsheetId, $cell_range, $body,['valueInputOption' => 'RAW']);
    echo "Update Google Sheet\n";
    $conn = null;

?>

How do i search Google Sheet by a value stored in it

1 Answers1