Google Sheets API with php - Find a cell value

Question

I am using the Google Sheets API with PHP and reading a sheet, I need to find a row and update its content.

I am currently iterating over the rows, looking for the value, but as the sheet grows, this seems rather inefficient. Is there a way to search for a cell, to retrieve the row, so I can then update?

My code to iterate is as follows.

        $spreadsheet = (new Google\Spreadsheet\SpreadsheetService)
           ->getSpreadsheetFeed()
           ->getById("xxx sheet id xxx");

        $worksheets = $spreadsheet->getWorksheetFeed()->getEntries();
        $worksheet = $worksheets[0];

        $CellFeed = $worksheet->getCellFeed();

        foreach ($CellFeed->getEntries() as $E)
        {
            $r = $E->getRow();
            /* ...... */
        }

Answer 1

I believe your goal as follows.

• You want to search a value from the specific column in the Spreadsheet and want to retrieve the row numbers of searched rows.
• You want to achieve this using PHP.

Issue and workaround:

In that case, unfortunately, when Sheets API is used, in the current stage, it is required to do the following flow.

1. Retrieve all values from the sheet you want to search.
2. Retrieve the row and column numbers from the retrieved values.

This might be the same with your current script. Because in the current stage, there are no methods for directly searching the values in Sheets API. So in this answer, as a workaround, I would like to propose to use Web Apps created by Google Apps Script. When Google Apps Script is used, the searched row numbers can be retrieved by the TextFinder which is the built-in method. And the process cost of TextFinder is low. So I proposed it.

Usage:

Please do the following flow.

1. Create new project of Google Apps Script.

Sample script of Web Apps is a Google Apps Script. So please create a project of Google Apps Script.

If you want to directly create it, please access to https://script.new/. In this case, if you are not logged in Google, the log in screen is opened. So please log in to Google. By this, the script editor of Google Apps Script is opened.

It is required to put this Google Apps Script project to the same Google Drive of the Spreadsheet you want to use.

2. Prepare script.

Please copy and paste the following script (Google Apps Script) to the script editor. This script is for the Web Apps.

function doGet(e) {
  const sheet = SpreadsheetApp.openById(e.parameter.spreadsheetId).getSheetByName(e.parameter.sheetName);
  const res = sheet.getRange(1, 2, sheet.getLastRow()).createTextFinder(e.parameter.searchValue).findAll().map(r => r.getRow());
  return ContentService.createTextOutput(JSON.stringify({rowNumbers: res})).setMimeType(ContentService.MimeType.JSON);
}

3. Deploy Web Apps.

1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
2. Select "Me" for "Execute the app as:".
   • By this, the script is run as the owner.
3. Select "Anyone, even anonymous" for "Who has access to the app:".

• In this case, no access token is required to be request. I think that I recommend this setting for testing this workaround.
• Of course, you can also use the access token. When you use the access token, please include one of scopes for Drive API like https://www.googleapis.com/auth/drive.readonly.
• And also, I think that a key value can be used as the query parameter instead of the access token.

4. Click "Deploy" button as new "Project version".
5. Automatically open a dialog box of "Authorization required".
   1. Click "Review Permissions".
   2. Select own account.
   3. Click "Advanced" at "This app isn't verified".
   4. Click "Go to ### project name ###(unsafe)"
   5. Click "Allow" button.
6. Click "OK".
7. Copy the URL of Web Apps. It's like https://script.google.com/macros/s/###/exec.
   • When you modified the Google Apps Script, please redeploy as new version. By this, the modified script is reflected to Web Apps. Please be careful this.

4. Testing Web Apps using PHP script.

Please set the URL of your Web Apps to the following script. And, please set the spreadsheet ID, sheet name. From your replying, in this sample, the search value and column number are Pj/5678 and 2, respectively. 2 of searchColumn means the column "B".

<?php
$url = 'https://script.google.com/macros/s/###/exec';  // Please set the URL of Web Apps.
$q = array(
  'spreadsheetId' => '###',  // Please set the Spreadsheet ID.
  'sheetName' => 'Sheet1',
  'searchValue' => 'Pj/5678',
  'searchColumn' => 2
);

$curl = curl_init();
$option = [
  CURLOPT_URL => $url . '?' . http_build_query($q),
  CURLOPT_CUSTOMREQUEST => 'GET',
  CURLOPT_FOLLOWLOCATION => true,
  CURLOPT_RETURNTRANSFER => true
];
curl_setopt_array($curl, $option);
$res = curl_exec($curl);
$obj = json_decode($res);
print_r($obj);
curl_close($curl);
?>

Result:

When above script is run, the following value is returned. The row numbers of searched rows are returned.

{"rowNumbers":[###, ###,,,]}

Note:

• When you modified the script of Web Apps, please redeploy the Web Apps as new version. By this, the latest script is reflected to Web Apps. Please be careful this.

References:

• Web Apps
• Taking advantage of Web Apps with Google Apps Script
• Class TextFinder