CODEWITHSUNDEEP
phpgoogle-sheetsgoogle-sheets-api
emiya
emiya

Reputation: 103

Get correct sheet in spreadsheet

This is how my Spreadsheet is now spreadsheet.

Right now, I need to get to correct sheet by name and change color of some cells, given sheet ID and sheet name. However, I am stuck at how to get the correct sheet.

My code now:

   $service = new Google_Service_Sheets($client);
   $spreadSheetId = 'my_spread_sheet_id';
   $spreadSheetName = 'my_sheet_day';
   $sheetInfo = $service->spreadsheets->get($spreadsheetId)->getSheets();

I thought I can try getSheetsByName but it doesn't work. Looking at documentation does not have information on how to get sheet information either. Am I taking the wrong way ?

Upvotes: 1

Views: 474

Answers (1)

Tanaike
Tanaike

Reputation: 201378

I believe your goal as follows.

  • You want to retrieve the sheet ID from the sheet name.
  • You want to achieve this using Sheets API with googleapis for php.
  • You have already been able to get values from Google Spreadsheet using Sheets API.

In this case, to retrieve the object of properties from each sheet using the method of "spreadsheets.get" in Sheets API. The sample script is as follows.

Sample script:

$service = new Google_Service_Sheets($client);
$spreadSheetId = 'my_spread_sheet_id';
$spreadSheetName = 'my_sheet_day';

// Retrieve the objects from Google Spreadsheet using Sheets API.
$sheets = $service->spreadsheets->get($spreadSheetId, ["fields" => "sheets(properties)"])->getSheets();

// Create an object for searching the sheet ID from the sheet name.
$obj = array();
foreach ($sheets as $i => $sheet) {
    $property = $sheet -> getProperties();
    $obj[$property -> getTitle()] = $property -> getSheetId();
}

// Retrieve the sheet ID from the sheet name.
$sheetId = $obj[$spreadSheetName];
echo $sheetId;
  • In this sample script, the fields of sheets(properties) is used. I think that in this case, you can also use $sheets = $service->spreadsheets->get($spreadSheetId)->getSheets();.

Reference:

Upvotes: 1

Related Questions