How To Read Data From Google Sheets Using Laravel?

This post is a continuation of the previous post on this blog about How To Create a New Project in Google Cloud Console? If you haven’t read that, I strongly suggest you read it first, before proceeding with this one, in order to be able to follow along correctly.

So, just like every other example that we talk about here, let’s first go ahead and create a New Laravel Project and then run it. Once done, let’s go and grab the JSON file we downloaded, as per our last post’s last section, here.

For ease of reading, let’s rename the file as creds.json. Now, copy this file and paste it into the storage folder of your project.

Install the Google API Client Package

In order to interact with a google sheet using Laravel, we will need to first bring in the Google API Client package. Let’s do that by running the following command:

composer require google/apiclient

Once this is done, let’s just go back to our previous blog post, to the section which says Also, don’t forget to copy the email id that should be listed below the Service Accounts section. We will need this email id. So, go to your Google Cloud Console and from under Service Accounts, copy this email id.

Now, let’s create a new Google sheet by going to docs.google.com and create a new spreadsheet. Then click the Share button on the top right of the newly created Google sheet and paste your service account email id here, to share the sheet with this email id (which is essentially your service account).

Come back to your Laravel project now and go to your .env file. There, paste the following code:

GOOGLE_SHEET_NAME='Your_Sheet_Name'

GOOGLE_SHEET_ID=Google_Sheet_Id

Paste the correct sheet name and the sheet id from the locations as shown below.

Now, go under the root folder and create a config\Google.php config file. Paste the following code there:

<?php

return [
    'google_sheet_id' => env('GOOGLE_SHEET_ID'),
    'sheet_name'      => env('GOOGLE_SHEET_NAME')
];

Let’s create a Services folder, and inside that, we will create a GoogleSheetService.php file. This is where all the magic will happen.

The code in this service file will be as given below:

use Google_Client;
use Google_Service_Sheets;
use Google_Service_Sheets_ValueRange;

class GoogleSheetService
{
    private $spreadSheetId;
    private $sheetName;
    private $client;
    private $googleSheetService;

    public function __construct()
    {
        $this->spreadSheetId = config('google.google_sheet_id');
        $this->sheetName     = config('google.sheet_name');
        $this->client        = new Google_Client();
        $this->client->setAuthConfig(storage_path('creds.json'));
        $this->client->addScope("https://www.googleapis.com/auth/spreadsheets");

        $this->googleSheetService = new Google_Service_Sheets($this->client);

    }

    public function readGoogleSheet($sheetName)
    {
        $dimensions = $this->getDimensions($this->spreadSheetId, $this->sheetName);
        $range = $sheetName.'!A1:' . $dimensions['colCount'];
        //$range = $sheetName.'!A1:' . 'AL';
        //dd($range);
        $data = $this->googleSheetService
            ->spreadsheets_values
            ->batchGet($this->spreadSheetId, ['ranges' => $range]);

        dd($data->getValueRanges()[0]->values);
        return $data->getValueRanges()[0]->values;
    }

    private function getDimensions($spreadSheetId, $sheetName)
    {
        //dd($this->googleSheetService->spreadsheets_values);
        $rowDimensions = $this->googleSheetService->spreadsheets_values->batchGet(
            $spreadSheetId,
            ['ranges' => $sheetName.'!A:A', 'majorDimension' => 'COLUMNS']
        );

        //if data is present at nth row, it will return array till nth row
        //if all column values are empty, it returns null
        $rowMeta = $rowDimensions->getValueRanges()[0]->values;
        if (!$rowMeta) {
            return [
                'error' => true,
                'message' => 'missing row data'
            ];
        }

        $colDimensions = $this->googleSheetService->spreadsheets_values->batchGet(
            $spreadSheetId,
            ['ranges' => $sheetName.'!1:1', 'majorDimension' => 'ROWS']
        );

        //if data is present at nth col, it will return array till nth col
        //if all column values are empty, it returns null
        $colMeta = $colDimensions->getValueRanges()[0]->values;
        if (!$colMeta) {
            return [
                'error' => true,
                'message' => 'missing row data'
            ];
        }

        return [
            'error' => false,
            'rowCount' => count($rowMeta[0]),
            'colCount' => $this->colLengthToColumnAddress(count($colMeta[0]))
        ];
    }

    private function colLengthToColumnAddress($number)
    {
        if ($number <= 0) return null;

        $letter = '';
        while ($number > 0) {
            $temp = ($number - 1) % 26;
            $letter = chr($temp + 65) . $letter;
            $number = ($number - $temp - 1) / 26;
        }
        return $letter;
    }
}

Now, to run that, you will just have to call the readGoogleSheet method from wherever you wish to. In my case, for the sake of simplicity, I am calling it from with the route file itself, like this:

Route::get('data',function(GoogleSheetService $gsheet){
    $gsheet->readGoogleSheet(config('google.sheet_name'));
});

This should return the contents of the Google Sheet.

Bonus Tip: How To Convert XLS File To Google Sheets File?

Initially, while trying this POC, I simply uploaded a preexisting xls file to my Google drive and use it. Surprisingly, I did not work and repeatedly gave me an error saying This operation is not supported for this document. Failed_precondition.

Error because of improper file format on Google Sheets

After looking around, I realized, the above only works for googlesheets format and not for the xls/xlsx format. So, I had then to figure out how to convert XLS/XLSX Format To Google sheets format.

How to convert XLS/XLSX Format To Google sheets format?

Thankfully, this was a very simple thing. All you have to do is, go to File->Save As Google Sheets.

That’s all that needs to be done.

You can download the code of this project from Github, here.

** This code is built upon Amitabh Roy’s repository which can be found here – https://github.com/amitavdevzone/google-data-studio