How to Access the Google Spreadsheets API in PHP

Download Google SpreadSheet Google API PHP

You can only do it that way if its a binary type file for a Google sheet you will need to export it. Also you should be creating a drive service and not a sheets service.

$service = new Google_Service_Drive($client);
$response = $service->files->export($createdFile->id, 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', array('alt' => 'media' ));
$content = $response->getBody()->getContents();    
file_put_contents('test.xlsx',$content);

Google Sheets API with php - Find a cell value

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

Grant access on spreadsheet using Google Sheets API

• You are trying to give permission with the Drive API
• Your code only contains $client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
• To use both the Sheets and the Drive API with the same client, assign to the client BOTH respective scopes

Can I access a Spreadsheet by Title in Google Sheets api v4 using the PHP client (or at all!)?

You can use the Drive.Files.list with a query parameter then use the IDs returned
on Spreadsheets.get to "open" your spreadsheet.

To learn more about the q parameter, read this documentation.

For filtering by MimeType as well as by title, you can do name = 'yourname' and mimeType = 'application/vnd.google-apps.spreadsheet', for example.

Google Sheets API v.4 for PHP: exclude certain columns from $request

I believe your goal is as follows.

• You want to retrieve the values from Spreadsheet excluding the columns "B" and "C".
• You want to achieve this using googleapis for PHP.
• You have already been able to get values from Spreadsheet using Sheets API.

In this case, how about the following patterns?

Pattern 1:

In this pattern, the method of "spreadsheets.values.get" is used. At first, the values are retrieved from "Sheet1!A2:I", and the result values can be retrieved by transposing.

From:

$range = 'Sheet1!A2:I';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();

To:

$range = 'Sheet1!A2:I';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$transpose = array_map(null, ...$response->getValues());
array_splice($transpose, 1, 2);
$values  = array_map(null, ...$transpose);

Pattern 2:

In this pattern, the method of "spreadsheets.values.batchGet" is used. At first, the values are retrieved from "Sheet1!A2:A" and "Sheet1!D2:I", and retrieved values are merged as an array.

From:

$range = 'Sheet1!A2:I';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();

To:

$response = $service->spreadsheets_values->batchGet($spreadsheetId, array('ranges' => ['Sheet1!A2:A', 'Sheet1!D2:I']));
$values = [];
foreach($response['valueRanges'][0]['values'] as $i => $v) {
    array_push($values, array_merge($v, $response['valueRanges'][1]['values'][$i]));
}
// In this case, you can retrieve the result values as "$values".

References:

• Method: spreadsheets.values.get
• Method: spreadsheets.values.batchGet

---

Related Topics