Google Sheet Integration in Laravel

Image Source

Introduction

In this tutorial, you can see how you can work on the data stored in Google Sheets in PHP, with the use of Laravel to be precise.

Prerequisite

1. Laravel application needs to be set up.

2. Google API package for Laravel application.

Creating a Google Project

Steps-

1. Create a google project for the google Sheet API to integrate with a Laravel application.

2. To do that, sign up to Google, Now head over to Google Developers Console and create or select an existing project.

• If you want to create a new project then click on NEW PROJECT in the upper right corner and create a new project-

• Now click CREATE and go to API’s & Services tab in the left panel and then go to credentials section.

3. Click on Create Credentials button and create client ID. Enter project name and enter Laravel route as redirect URL (http:localhost:8000/login/google/callback). set your domain name(for production) or use localhost for development. Redirect URL will be used to redirect users back to your app after authentication.

4. Click save and Download the JSON credentials file.

5. Now click on Enable API and Services from the dashboard to Enable Google sheet API.

Search for Google Drive, Google+ APIs, and Google Sheet API and make sure they are enabled.

Google sheet API documents- https://developers.google.com/sheets/api/reference/rest?apix=true

6. Copy Service Email id from Google developer console-

7. Set permissions on Google Sheet- (Go to Google sheet, Click on Share Button and paste Service Email id and give Editor Access)

Install the Google API package in your Laravel Application

After setting up your Google account, head to your Laravel application, and install the google client package.

$ composer require revolution/laravel-google-sheets

Run

$ php artisan vendor:publish --provider="PulkitJalan\Google\GoogleServiceProvider" --tag="config"

to publish the google config file

Create a google config file in the config folder as config\google.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'           => [\Google_Service_Sheets::DRIVE, \Google_Service_Sheets::SPREADSHEETS],
//    'scopes'           => [\Google_Service_Sheets::DRIVE_READONLY, \Google_Service_Sheets::SPREADSHEETS_READONLY],
'access_type'      => 'offline',
'approval_prompt'  => 'force',
'prompt'           => 'consent', //"none", "consent", "select_account" default:none
/*
|----------------------------------------------------------------------------
| 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', storage_path('credentials.json')),
],
/*
|----------------------------------------------------------------------------
| 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'           => [],
'post_spreadsheet_id' => env('POST_SPREADSHEET_ID'),
'post_sheet_id'       => env('POST_SHEET_ID'),
];

The above code establishes a connection with the google client bypassing the credentials from your .env file to the config.

Configure Google Service

GOOGLE_DEVELOPER_KEY=
GOOGLE_SERVICE_ENABLED=true
GOOGLE_SERVICE_ACCOUNT_JSON_LOCATION=storage/credentials.json
POST_SPREADSHEET_ID=
POST_SHEET_ID=
GOOGLE_REDIRECT_URL=http:localhost:8000/login/google/callback

Add these credentials to your .env file which you can collect from the google developer console. You can add GOOGLE_DEVELOPER_KEY i.e APP_KEY, POST_SPREADSHEET_ID, POST_SHEET_ID, REDIRECT_URL.

Use the following code in any existing Controller file or you can create your own GoogleServiceController.php and use the following code to perform operations with the google sheet.

<?php
namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
use Revolution\Google\Sheets\Facades\Sheets;
class GoogleServiceController extends Controller
{
   use ResponseTrait;
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
   //
}
// retrieve the data from google sheet
public function getGoogleSheetValues()
{
   $getrange = 'A:I';
 $values = Sheets::spreadsheet(config('google.post_spreadsheet_id'))
->sheet(config('google.post_sheet_id'))
->range($getrange)->all();
 return $values;
}
// append new row to google sheet
public function appendValuesToGoggleSheet()
{
     $append = [
       'title' =>'Test Title',
       'description' => 'This is dummy title'
     ];
      $appendSheet =        Sheets::spreadsheet(config('google.post_spreadsheet_id'))
->sheet(config('google.post_sheet_id'))
->append([$append]);
 }
}

Happy Learning!!!

Conclusion

In this article, you learned the work on the data stored in Google Sheets in PHP, with the use of Laravel in detail.
That’s all! I hope you enjoyed reading this article and learned something new. Do share this article if you find it useful.

Follow me and my team to know what we are building secretly….

Reference-

1. https://drivemarketing.ca/en/blog/connecting-laravel-to-a-google-sheet/
2. https://developers.google.com/sheets/api/quickstart/php
3. https://github.com/kawax/laravel-google-sheets