retrieve all rows in Google Spreadsheet via API using Laravel

Question

I'm trying to get the data from Google spread sheet by using this library: This tutorial, and I follow this tutorial to configure the setup of API and no any error, but once I tried to implement any of the examples they provide in the above link, it show me some errors:

Example 1 (on their link):

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use PulkitJalan\Google\Facades\Google;
use Revolution\Google\Sheets\Sheets;

class SpreadController extends Controller
{
    function index()
    {
        $rows = Sheets::sheet('Sheet 1')->get();

        $header = $rows->pull(0);
        $values = Sheets::collection($header, $rows);
        dd($values);
    }

}

The error is:

Unresolvable dependency resolving [Parameter #0 [ array $config ]] in class PulkitJalan\Google\Client

Example 2 (on their link):

Code:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use PulkitJalan\Google\Facades\Google;
use Revolution\Google\Sheets\Sheets;

class SpreadController extends Controller
{
    function index()
    {
        $user = $request->user();

        $token = [
            'access_token'  => $user->access_token,
            'refresh_token' => $user->refresh_token,
            'expires_in'    => $user->expires_in,
            'created'       => $user->updated_at->getTimestamp(),
        ];

// all() returns array
        $values = Sheets::setAccessToken($token)->spreadsheet('1n0nyJX9wYp-_frg7TzoyMu3WyuSwtCo21g')->sheet('Sheet 1')->all();

    }

}

The error is:

Undefined variable: request

This is the config file in the library directory ("vendor/pulkitjalan/google-apiclient/src/config/config.php"):

<?php

return [
    /*
    |----------------------------------------------------------------------------
    | Google application name
    |----------------------------------------------------------------------------
    */
    'application_name' => env('GOOGLE_APPLICATION_NAME', ''),

    /*
    |----------------------------------------------------------------------------
    | Google OAuth 2.0 access
    |----------------------------------------------------------------------------
    |
    | Keys for OAuth 2.0 access, see the API console at
    | https://developers.google.com/console
    |
    */
    'client_id'       => env('GOOGLE_CLIENT_ID', ''),
    'client_secret'   => env('GOOGLE_CLIENT_SECRET', ''),
    'redirect_uri'    => env('GOOGLE_REDIRECT', ''),
    'scopes'          => [],
    'access_type'     => 'online',
    'approval_prompt' => 'auto',

    /*
    |----------------------------------------------------------------------------
    | Google developer key
    |----------------------------------------------------------------------------
    |
    | Simple API access key, also from the API console. Ensure you get
    | a Server key, and not a Browser key.
    |
    */
    'developer_key' => env('GOOGLE_DEVELOPER_KEY', ''),

    /*
    |----------------------------------------------------------------------------
    | Google service account
    |----------------------------------------------------------------------------
    |
    | Set the credentials JSON's location to use assert credentials, otherwise
    | app engine or compute engine will be used.
    |
    */
    'service' => [
        /*
        | Enable service account auth or not.
        */
        'enable' => env('GOOGLE_SERVICE_ENABLED', false),

        /*
        | Path to service account json file
        */
        'file' => env('GOOGLE_SERVICE_ACCOUNT_JSON_LOCATION', '')
    ],

    /*
    |----------------------------------------------------------------------------
    | Additional config for the Google Client
    |----------------------------------------------------------------------------
    |
    | Set any additional config variables supported by the Google Client
    | Details can be found here: 
    | https://github.com/google/google-api-php-client/blob/master/src/Google/Client.php
    | 
    | NOTE: If client id is specified here, it will get over written by the one above.
    |
    */
    'config' => [],
];

and .env file contains this assigned values for the config variables:

 GOOGLE_APPLICATION_NAME= Append Data
 GOOGLE_CLIENT_ID= 995576272219-q9t67ufvehsqoctcq7g.apps.googleusercontent.com
 GOOGLE_CLIENT_SECRET= ow1kEPcuSEGmGIXZtl
 GOOGLE_REDIRECT=
 GOOGLE_DEVELOPER_KEY=
 GOOGLE_SERVICE_ENABLED=
 GOOGLE_SERVICE_ACCOUNT_JSON_LOCATION=

Can anyone guide me through the solution how I can get the data from spreadsheet or solve the problem considered here.

I think you have missed `src` files – Zain Farooq Aug 31 '18 at 06:06 — Zain Farooq, Aug 31 '18 at 06:06

score 1 · Answer 1 · answered Jun 24 '21 at 09:59

To retrieve all the rows from google spreadsheet, just remove cell number from the range.

$getrange = 'A:E';
$data = $service->spreadsheets_values->get($spreadsheetId, $getrange);

$values = $data->getValues();

if (empty($values)) {
    print "No data found.\n";
} else {
    echo "<pre>";
    print_r($values);
}

score 0 · Answer 2 · answered Nov 28 '18 at 12:59

You can use https://github.com/googleapis/google-api-php-client package to accomplish the task.

To install this package add

"google/apiclient": "^2.0"

to composer.json and run the composer update command.

Please follow https://www.fillup.io/post/read-and-write-google-sheets-from-php/ tutorial for more information

After installing the package need to set values in the .env file and constants.php file which is located in the config folder.

Here I have used a sheet name variable. If you omit this one then it will take the first sheet(Tab) in the spreadsheet.

Sample code:

namespace App\Utilities;

    use App\Exceptions\LogExceptions;
    use AWS;
    use Illuminate\Http\File;
    use Illuminate\Support\Facades\Storage;
    use Config;

    class SpreadSheetManagemnt{
      private $CLIENT;
      private $SERVICE;
      private $SPREAD_SHEET;
      private $SPREAD_SHEET_ID;
    /**
     * Returns an authorized API client.
     * @return Google_Client the authorized client object
     */
      public function __construct() {

      $this->CLIENT = new \Google_Client();
      $this->CLIENT->setApplicationName("My Sample App");
      $this->CLIENT->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
      $this->CLIENT->setAccessType('offline');
      $this->CLIENT->setAuthConfig(Config::get('constants.SPREAD_SHEET_AUTH_FILE'));

      $this->SPREAD_SHEET_ID = Config::get('constants.SPREAD_SHEET_ID');

      $this->SERVICE = new \Google_Service_Sheets($this->CLIENT);
      $this->SPREAD_SHEET =  $this->SERVICE->spreadsheets->get($this->SPREAD_SHEET_ID);
   }

    /**
     * Function to show sheet datas
     * @param string sheet name
     * @return boolean
     */
    public function showDatas($sheetName)
    {
        try{
          // column ranges in spreadsheet
          $range = "'".$sheetName."'!A2:F3";
          $rows = $this->SERVICE->spreadsheets_values->get($this->SPREAD_SHEET_ID, $range, ['majorDimension' => 'ROWS']);
          dd($rows['values']);

        }catch( \Throwable $e){
            LogExceptions::writeLog($e);
        }
    }

}

how do I get All values in a row. but in a row there could be multple cell. Is there any function to get all the values from a row from a google sheet? Because Here I can see the column names are hard-coded. — FerdousTheWebCoder, Mar 01 '19 at 15:50

retrieve all rows in Google Spreadsheet via API using Laravel

2 Answers2