Google Sheet is a cloud based spreadsheet tool with the help of which multiple users can work together on a single sheet. We use Google Sheet to store data, logs, form data, and for reports. Here the data gets updated in real time. And we can also share the sheet with multiple users. In this article, we will learn about Google Sheet Integration with Laravel with the help of examples.
Introduction
Web applications or websites use enquiry forms to collect user data. All the data stored in these two ways, either stored in a database or in a CSV file. But if the data has to be shared, then we do it manually. We cannot use live filters in it, means we can use any filter only for ourselves, we cannot easily share it live with anyone. Goole Sheet solves all these problems. We store data live in it. If the sheet is shared with multiple users, then any filter or report gets shown to all the shared users.
Integration of Google Sheet in Laravel is done with the help of packages.
Make a Google Sheet
We will create a new Google Sheet by logging into Google Drive. We can give any name to the Google sheet, but please keep it meaningful. Now we can also rename the first tab, otherwise leave it as it is. If we rename the tab, please there should not be any space in the name, because this tab's name is used at the time of connection. Now we will add headers in the first row like Name, Email, Phone No., Enquiry Date etc.
Google Cloud Project Setup
To set up a Google Cloud project, we have to visit Google Cloud Console. By clicking on "Select Project", a popup opens, which contains all the projects that we have created. We can select any one of these projects or create a new project by clicking on the "New Project" option. Here we give a name in "Project Name" (like Laravel Google Sheet) and click on create.
Enable Google Sheets API
Now we will choose "Enable API & Services" option from the "API & Services" option from the left menu. Now we will click on the "Library" option from the left. Many libraries will be displayed here. We have to enable only two libraries, one is Google Drive API and the other Google Sheet API. Here we will enable "Google Drive API" because Google sheet comes under google drive and without google drive we cannot access google sheet. So, we will search and enable "Google Drive API" & "Google Sheer API".
Credentials Setup
Now we will click on the "Credentials" option from "API & Services". Here we need three things
- OAuth Client ID
- API Keys
- Service Account
Setup OAuth Client ID
Click on "Create Credentials" option & select OAuth Client ID.
Application Type: Select Any one option like website, Android, iOS, Chrome Extension etc.
Name: Put any name
If you are using frontend & send request from browser, then add call back url in "Authorized JavaScript Origins".
If you are send request from web server means backend, then add call back url in "Authorized redirect URIs".
Then click on save button. After save you will get "Client ID" & "Client Secret".
API Keys
Click on "Create Credentials" option & select API Key.
Now, you will get new API Key
Service Account
Click on "Create Credentials" option & select Service Account.
Service Account Name: Put Any name
Service Account Description(Optional): Add some description about service account
Click on "Create and continue" button. Again, click on "Done" button. Now, in service Account, click on edit button. Now, click on "Key" tab & after that click on "Add key", select "JSON" option in opened popup, Click on Create button, It will automatically download a json file. Rename this file as "credentials.json" & paste this file under storage folder in Laravel.
Install Laravel package
Now, We will install the Google Sheets package library for Laravel via the composer command.
composer require revolution/laravel-google-sheets
After installation, execute below command to make config file
php artisan vendor:publish --tag="google-config"
it will create "google.php" file under "config" folder in Laravel. now we will add some variables in env file.
GOOGLE_DEVELOPER_KEY=put_api_keys_here GOOGLE_CLIENT_ID=put_oauth_client_id GOOGLE_CLIENT_SECRET=put_oauth_client_secret GOOGLE_SERVICE_ENABLED=true GOOGLE_SERVICE_ACCOUNT_JSON_LOCATION=storage/credentials.json POST_SPREADSHEET_ID=put_spreadsheet_id POST_SHEET_ID="0" GOOGLE_REDIRECT=put_call_back_uri_that_you_added_in_oauth
How to find google spread sheet id?
Go to Google Sheet & check url, for example,
https://docs.google.com/spreadsheets/d/1w4OtNZpo0SNDIJpC0cmuiGgVFuesWmVqkflpBoZbhdfer/edit
Google Spread Sheet ID: 1w4OtNZpo0SNDIJpC0cmuiGgVFuesWmVqkflpBoZbhdfer
Connect Google Sheets with Laravel
Now, we will create a route
route/web.php
Route::get('save_data_gsheet', 'App\Http\Controllers\WelcomeController@save_data_google_sheet');
Route::post('save_data_gsheet', 'App\Http\Controllers\WelcomeController@save_data_google_sheet');
Now, make a controller named WelcomeController and add a method named save_data_google_sheet.
app/Http/Controllers/WelcomeController.php
<?php
namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Revolution\Google\Sheets\Facades\Sheets;
use Session;
class WelcomeController extends Controller
{
public function save_data_google_sheet(Request $request)
{
if(!empty($request->email))
{
$recordarray = array(array( $request->name, $request->email, $request->mobile, date('d-m-Y')));
$setrange = "Sheet1!A1:D1";
$appenddata = Sheets::spreadsheet(config('google.post_spreadsheet_id'))->sheet(config('google.post_sheet_id'))->range($setrange)->append($recordarray);
Session::flash('Data saved successfully');
}
return view('enquiry_form');
}
}
?>
Now, make a blade file named enquiry_form.blade.php
resources/views/enquiry_form.blade.php
<html>
<head>
<title>Save Enquiry Form Data to Google Sheet</title>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<form name="frmenquiry" action="{{ url('save_data_gsheet') }}" method="POST">
{{ csrf_field() }}
<div class="form-group">
<label>Name</label>
<input class="form-control" type="text" name="name" placeholder="Name" required />
</div>
<div class="form-group">
<label>Email</label>
<input class="form-control" type="email" name="email" placeholder="Email" required />
</div>
<div class="form-group">
<label>Name</label>
<input class="form-control" type="text" name="mobile" placeholder="Mobile No." required />
</div>
<button class="btn btn-primary" type="submit">Submit</button>
</form>
</div>
</div>
</div>
</body>
</html>
Now if we fill data in the form then the data will be instantly added to the Google Sheet.
Conclusion
By integrating Google Sheets with Laravel, you may improve your application's capacity to manage data in an approachable and cooperative manner. This integration can be used for creating dashboards, gathering feedback, or recording activity. It is versatile and strong.
