Google Sheet to Laravel 8 Integration

Question

I want to integrate google sheet with Laravel 8 without any third party tool or connector. I know its simple for many but i am not able to get through. Highly appreciate for your effort and answers!

Answer 1

Requirements:

• Google OAuth Keys.
• Google Service Account.
• Enabling Google Drive API and Google Sheets API.
• revolution/laravel-google-sheets Laravel package.

Steps:

Step 1: Installing revolution/laravel-google-sheets package

composer require revolution/laravel-google-sheets

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

Step 2: Setup Google API OAuth Key

• Navigate to Google Developers Console
• Create a Google project or select already existed project.
• Click Create credentials followed by OAuth client ID.
• If necessary, Configure consent screen.
• Set the type to Web Application.
• Copy client_id and client_secret and set in .env:

GOOGLE_CLIENT_ID=XXXXXX-XXXXXXXXXXXXXXXXXXXXXXXX.apps.googleusercontent.com
GOOGLE_CLIENT_SECRET=XXXXXX-XXXXXXXXXXX-XXXXXXXXXXXXXXXX

Step 3: Setup Google Service Account

• Navigate to Google Developers Console

• Create a new Service account key from Credentials.

• Give your service account a name.

• Under Grant this service account access to project step click on Select a Role dropdown and choose Project from left side and Editor from right side as shown in this image

• Click Continue then Done.

• Edit the service account and go to Keys tab.

• Create a new Key of type JSON, and copy the json file to your project storage directory and rename it as credentials.json -or any other name you like.

• Add credentials.json file path to .env as following

GOOGLE_SERVICE_ENABLED=true
GOOGLE_SERVICE_ACCOUNT_JSON_LOCATION=/home/user/code/project/storage/credentials.json

Step 4: Setup a Google Spreadsheet to integrate with

• Create/Open a Google Spreadsheet file.

• Give the sheet a name

• Copy the ID of the document from file URL (e.g. https://docs.gogle.com/spreadsheets/d/{{SPREADSHEET_ID}}), see this image

• Set the document id you copied in last step to .env

SPREADSHEET_ID=1kjtQBxxxxxx-xxxxxxxxxxxxxxxxxxxxxxxxxxxx

• Copy the Email of the recently created Service Account, see this image

• Share the sheet with the Service Account email as Editor, see this image

Step 5: Enable required Google APIs

• Navigate to Google Developers Console APIs Dashboard.
• Click ENABLE API AND SERVICES.
• Search for Google Drive API and Enable it.
• Search for Google Sheets API and Enable it.

Step 6: Test it

use Revolution\Google\Sheets\Facades\Sheets;

// Add new sheet to the configured google spreadsheet
Sheets::spreadsheet(config('sheets.spreadsheet_id'))->addSheet('sheetTitle');

$rows = [
    ['1', '2', '3'],
    ['4', '5', '6'],
    ['7', '8', '9'],
];

// Append multiple rows at once
Sheets::sheet('sheetTitle')->append($rows);

see the package documentation for more examples.