How to get google spread sheet data from Sheet ID (From gid) instead of Spread Sheet Id in php

Question

I have one spreadsheet and it contains multiple sheets in tab format .

My code is getting only first spread Sheet data and I have an array which contains all the sheet Id.

My problem is how can I get all the Sheet Data, as I have unique gid for all.

Here SpreadSheet Id is same for all the sheets only sheet id (gid) is different.

I searched a lot, I got only getting data from spreadSheet Id.

<?php
require __DIR__ . '/vendor/autoload.php';

if (php_sapi_name() != 'cli') {
    throw new Exception('This application must be run on the command line.');
}

/**
 * Returns an authorized API client.
 * @return Google_Client the authorized client object
 */
function getClient()
{
    $client = new Google_Client();
    $client->setApplicationName('Google Sheets API PHP Quickstart');
    $client->setScopes(Google_Service_Sheets::SPREADSHEETS_READONLY);
    $client->setAuthConfig('credentials.json');
    $client->setAccessType('offline');
    $client->setPrompt('select_account consent');

    // Load previously authorized token from a file, if it exists.
    // The file token.json stores the user's access and refresh tokens, and is
    // created automatically when the authorization flow completes for the first
    // time.
    $tokenPath = 'token.json';
    if (file_exists($tokenPath)) {
        $accessToken = json_decode(file_get_contents($tokenPath), true);
        $client->setAccessToken($accessToken);
    }

    // If there is no previous token or it's expired.
    if ($client->isAccessTokenExpired()) {
        // Refresh the token if possible, else fetch a new one.
        if ($client->getRefreshToken()) {
            $client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
        } else {
            // Request authorization from the user.
            $authUrl = $client->createAuthUrl();
            printf("Open the following link in your browser:\n%s\n", $authUrl);
            print 'Enter verification code: ';
            $authCode = trim(fgets(STDIN));

            // Exchange authorization code for an access token.
            $accessToken = $client->fetchAccessTokenWithAuthCode($authCode);
            $client->setAccessToken($accessToken);

            // Check to see if there was an error.
            if (array_key_exists('error', $accessToken)) {
                throw new Exception(join(', ', $accessToken));
            }
        }
        // Save the token to a file.
        if (!file_exists(dirname($tokenPath))) {
            mkdir(dirname($tokenPath), 0700, true);
        }
        file_put_contents($tokenPath, json_encode($client->getAccessToken()));
    }
    return $client;
}


// Get the API client and construct the service object.
$client = getClient();
$service = new Google_Service_Sheets($client);



$spreadsheetId = 'xxxxxxx--tttttttttttL_ttthhdfhdhshshshshhshsh-84';///random spread sheet id
$range = 'A:G';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();//getting first sheet data only


    $sheet_id = array();    
    // Load Google API library and set up client
    // You need to know $spreadsheetID (can be seen in the URL)
    $sheetService = $service;  
    $spreadSheet = $sheetService->spreadsheets->get($spreadsheetId);
    $sheets = $spreadSheet->getSheets();

    foreach($sheets as $sheet) {

    $sheet_id[] = $sheet->properties->sheetId;


    }


 ///$sheet_id   -- it will give all the id of sheets, I have 36 sheets in a single spreadsheet, so it's giving 36 ids in an array format

Any Suggestion Will be appreciated ..

Answer 1

To get sheet's name by GID you can use corresponding Sheets Api:

$spreadsheet_service=new Google_Service_Sheets($client);

$body = new Google_Service_Sheets_GetSpreadsheetByDataFilterRequest([
    'data_filters'=>[
        "gridRange"=>[
            "sheetId"=>SHEET_GID_HERE
        ]
    ]
]);

$response = $spreadsheet_service->spreadsheets->getByDataFilter(SPREADSHEET_ID_HERE,$body,['fields'=>'sheets(properties.title)']);
print_r($response->getSheets());

Answer 2

After a lots of research , I got the solution and I would like to post my solution.

$client = $this->getClient();
$service = new Google_Service_Sheets($client);

// Prints the names and majors of students in a sample spreadsheet:
// https://docs.google.com/spreadsheets/d/xxxxxx--yyyyyyyyyyyyyy_zzzzzzzzzzzzzzzz/edit

 //

 $spreadsheetId = 'xxxxxx--yyyyyyyyyyyyyy_zzzzzzzzzzzzzzzz';
  $sheet_id = array();    
    // Load Google API library and set up client
    // You need to know $spreadsheetID (can be seen in the URL)
    $sheetService = $service;  
    $spreadSheet = $sheetService->spreadsheets->get($spreadsheetId);
    $sheets = $spreadSheet->getSheets();

    foreach($sheets  as  $key=>$sheet) {

    // $sheet_id[$key]['gid'] = $sheet->properties->sheetId;
    // $sheet_id[$key]['title'] = $sheet->properties->title;


$range = $sheet->properties->title.'!A:G';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values[$sheet->properties->title] = $response->getValues();


    }

Here we have only spreadSheet Id , from that We can get titles of all the sheets and from the title we will get all the details :

$range = $sheet->properties->title.'!A:G'; /// loop the title and get the whole sheet value

Answer 3

I can help you with the javascript - you'll have to work out any php for yourself.

If you have the sheet ID, then it is easy to back track to a sheet name, and then to "get" the sheet by name.

1 - getSheets() - gets all the sheets in the current spreadsheet. From this, you can find the sheet name AND the ID of each sheet.

2 - The ID is obtained using getSheetId() - returns the ID of a sheet which you can compare to your list.

3 - The sheet name is obtained using getSheetName() - returns the sheet name which you can use in the method getSheetByName.

4 - getSheetByName(name) - enables you to return a specific sheet with the given name.

The following example gets an object containing the sheetIDs for the ActiveSpreadsheet. It loops through those sheets capturing the respective SheetName and SheetID. Using a nested loop it loops through my list of SheetIDs and compares the SheetID. If the SheetID matches, then the code uses the SheetName to access the sheet by name. If the SheetIDs don't match, it continues though the nexted loop, and then so on.

I've left a number of Logger commands in the code so that the OP can test check details at convenient points in the script.

function so54586032() {

  // set up the spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // this is the sheet where I listed my SheetIDs
  var mainsheet = ss.getSheetByName("54586032");

  // calculate the number of IDs 
  var Avals = mainsheet.getRange("A1:A").getValues();
  var Alast = Avals.filter(String).length;
  //Logger.log("DEBUG: Number of SheetIDs in my list: " + Alast); //DEBUG

  // get the list of all sheets in this spreadsheet
  var sheets = ss.getSheets();
  //calculate the number of sheets
  var sheetslength = sheets.length
  //Logger.log("DEBUG: Number of actual sheets in this spreadsheet: " + sheetslength); //DEBUG

  // LOOP through the actual sheets
  for (var i = 0; i < sheets.length; i++) {
    //Logger.log("DEBUG: i: " + i + ", sheet name: " + sheets[i].getName() + ", sheet ID: " + sheets[i].getSheetId()); // DEBUG

    // loop through the list of sheets 
    for (var z = 0; z < Alast; z++) {
      //Logger.log("DEBUG: z: " + z + ", sheet ID: " + Avals[z][0]); //DEBUG

      // test if this shhetID equals the next number in my list
      if (sheets[i].getSheetId() == Avals[z][0]) {
        //Logger.log("DEBUG: Match: " + sheets[i].getSheetId() + " to " + Avals[z][0]); //DEBUG
        // do something
      } else {
        //Logger.log("DEBUG: No Match"); //DEBUG
      };
    }

  }

}

---

My list containing relevant SheetIDs