# Store data in google sheets using Laravel

# 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

- Sign the google and go to [developers console](https://console.cloud.google.com)
- Create a new project if necessary

![01.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661927890889/tXUSgBQ1w.png align="left")

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

![02.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661929480656/fMzj9-Btt.png align="left")

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

![03.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661929513904/xu7SSxx2C.png align="left")

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](https://cdn.hashnode.com/res/hashnode/image/upload/v1661929648677/3sCDBi2kI.png align="left")

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

![05.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661929676796/usJpW6e-n.png align="left")

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](https://cdn.hashnode.com/res/hashnode/image/upload/v1661929706987/qqxJPnthn.png align="left")

![07.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661929723834/6VFd3p5Af.png align="left")

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

![08.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661929751353/W8G0IOC5k.png align="left")

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

![09.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661929783166/2JicdOu7m.png align="left")

![10.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661929839285/Ps-AiWFJq.png align="left")

![11.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661929855309/fW0ztzNDy.png align="left")

- 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1661929917127/t-U7dR7gU.png align="left")

![13.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1661929933801/lWGs0rDLk.png align="left")

## Installing dependencies in Laravel

Now we have finished the setup for the google account

#### Install the package in our Laravel Application 

[laravel-google-sheets](https://packagist.org/packages/revolution/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
<?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
<?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](https://cdn.hashnode.com/res/hashnode/image/upload/v1661930580653/HbEahgK2k.png align="left")

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

## References
- [Laravel google sheet package](https://packagist.org/packages/revolution/laravel-google-sheets)
- [Kawax/laravel-google-sheets](https://github.com/kawax/laravel-google-sheets)
- [Connecting Laravel To A Google Sheet](https://drivemarketing.ca/en/blog/connecting-laravel-to-a-google-sheet/)