JoBins Engineering

JoBins Engineering

Store data in google sheets using Laravel

Store data in google sheets using Laravel

ashok gurung's photo
ashok gurung
·Aug 31, 2022·

4 min read

Subscribe to our newsletter and never miss any upcoming articles

Play this article

Introduction

As a developer sometimes we need to share or access the data in google sheets for business purposes and for those who don't need a user account of the system to access specific data. In this tutorial, you can see a step-by-step process to store data in Google Sheets in a simple way.

Prerequisite

  • Already configured Laravel Application
  • Google account

Setup Google Project

Before installing the Laravel package let’s set up a Google project for the google sheet API.

Steps to setup google sheet API

01.png

  • After creating the project click the create credentials and select OAuth Client ID

02.png

  • While creating OAuth client ID select Web application add name and redirect URIs.

03.png

Note: Redirect URL can be either your development URL i.e. localhost or production URL which will be used to redirect users back to your application after google authentication.

  • Create a service account

04.png

From the "manage accounts", section create a new service account if necessary

05.png

After creating a service account, we need to generate a key by clicking an already generated service account email. Navigate to keys tabs, and create a new private key for adding data in sheets.

06.png

07.png

After creating the key, save the JSON file in your project storage folder and rename it to credentials.json

08.png

  • Now we need to enable Google Sheets API and Google Drive API from the dashboard.

09.png

10.png

11.png

  • Set editor permissions in the google sheet

Copy the service email id from the developer console and go to google sheets and share this email with the editor role

12.png

13.png

Installing dependencies in Laravel

Now we have finished the setup for the google account

Install the package in our Laravel Application

laravel-google-sheets

We need to install the package by following the command

$ composer require revolution/laravel-google-sheets

After successfully installing publish the package which will create google.php file inside your config directory

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

Open the google.php and update the scopes and add additional config for the google client

<?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],
    '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. You can also pass the credentials as an array
         * instead of a file path.
         */
        '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'),
];

Configure the env file with the required credentials information from your google project

GOOGLE_APPLICATION_NAME=
GOOGLE_CLIENT_ID=
GOOGLE_CLIENT_SECRET=
GOOGLE_REDIRECT=
GOOGLE_DEVELOPER_KEY=
GOOGLE_SERVICE_ENABLED=true
GOOGLE_SERVICE_ACCOUNT_JSON_LOCATION=../storage/credentials.json
POST_SPREADSHEET_ID=

Now the application is ready to generate the sheet in google. At first, create a test method inside any test controllers with necessary routes and add read, and write logic according to the business needs. Here I have created a test method and dynamically generate sheet names and write array data in that specific sheet.

<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\BaseController;
use Carbon\Carbon;
use Revolution\Google\Sheets\Facades\Sheets;

class GoogleSpreedSheetController extends BaseController
{
   public function __construct()
   {
   }

   public function index()
   {
       /** generate sheet name **/
       $sheetName = sprintf('%s-Test', format_date('m-d', Carbon::today()->toDateString()));

       /** prepare the data in array **/
       $data = [
           [
               'ID',
               'Name',
           ],
           [
               'U001',
               'John',
           ],
           [
               'U002',
               'Harry',
           ],
       ];

       /** generate a new sheet in a specific spread sheet **/
       Sheets::spreadsheet(config('google.post_spreadsheet_id'))->addSheet($sheetName);

       /** write the data in the newly generated sheet **/
       Sheets::sheet($sheetName)->append($data);

   }
}

Results

15.png

Conclusion

This article is basically related to storing the data in already created google sheets which may help eliminate manually importing data processes.

References

 
Share this