Connecting Laravel to a Google Sheet

Introduction

This guide will give you a detailed, step-by-step walk-through of the steps to reproduce the example found at https://sheets.kawax.biz/

This guide exists because the current documentation available online is either out-of-date or incomplete.

A lot of the information contained in this document is an update or clarification of information found from the following sources:

We will go through the steps to install and configure the revolution/laravel-google-sheets package.

Overview of Steps

Installation of revolution/laravel-google-sheets
Set up Google credentials (API Key)
Set up Google Sheet
Set up Google credentials (Service account key)
Set permission on Google Sheet
Enable required Google APIs
Final configuration of Laravel Settings / Files
Update PHP Code (homepage)
Update PHP Code (post)
Quick Overview of Sheets Functions

1. Installation of revolution/laravel-google-sheets

On an installation of Laravel, run:

composer require revolution/laravel-google-sheets

Followed by:

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

2. Set up Google credentials (API Key)

Go to https://developers.google.com/console and sign in (if necessary)
Create a new project (if necessary)
Click on “credentials” from the left-hand menu
Click “Create credentials” followed up “OAuth client ID”
If necessary, “Configure consent screen”
Set the type to “Web application”
Copy the “Client ID” (leave this window open)
Open /.env and paste the ID next to GOOGLE_CLIENT_ID=
Open /config/google.php and paste the ID into 'client_id' => env('GOOGLE_CLIENT_ID', ''),
Go back to the Google Console and copy the “Client Secret”
Open /.env and paste the ID next to GOOGLE_CLIENT_SECRET=
Open /config/google.php and paste the ID into 'client_secret' => env('GOOGLE_CLIENT_SECRET', '')

3. Set up Google Sheet

Open http://sheets.google.com and create a new document
Give your new Sheet a name
Give the specific sheet (tab) a name
Copy the ID of the document from the URL
Open /.env and paste the document ID next to POST_SPREADSHEET_ID=

4. Set up Google credentials (Service account key)

Go back to the developer console and create a new “Service account key”
Select a service account (create a new one if necessary – Role > Project > Owner)

Type should be “JSON”
Save the .json file under the project’s /storage/ folder as “credentials.json” (and upload to the server if applicable)
From the console, click on “Manage service accounts”
Copy the email address for the service account we just created

5. Set permission on Google Sheet

Go back to your Sheet
Click on “Share”
Paste the email address for the service account and make sure to give it “edit” access

6. Enable required Google APIs

Return to the main Developer Dashboard (https://console.developers.google.com/apis/dashboard)
Click “ENABLE API AND SERVICES”
In the search box, type “drive api” and click on the “Google Drive API” option
Click “ENABLE”
Search for “sheets api” and click on “Google Sheets API”
Click “ENABLE”

7. Final configuration of Laravel Settings / Files

Open /.env and set the following:

```
GOOGLE_SERVICE_ENABLED=true
```

GOOGLE_SERVICE_ACCOUNT_JSON_LOCATION=../storage/credentials.json

8. Update PHP Code (homepage)

Background:

The https://github.com/kawax/google-sheets-project package installs with two basic controllers:

/app/Http/Controllers/HomeController.php which displays a simple form and content from the target Sheet

/app/Http/Controllers/PostController.php which handles the posted data and adds it to the target Sheet

Neither of these files seems to work properly “out-of-the-box”.

Lines 24 to 31 in HomeController.php are completely wrong and should be removed / replaced. Code to be removed:

        $sheets = Sheets::spreadsheet(config('sheets.post_spreadsheet_id'))

                        ->sheet(config('sheets.post_sheet_id'))

                        ->get();


        $header = $sheets->pull(0);


        $posts = Sheets::collection($header, $sheets);

        $posts = $posts->reverse()->take(10);

The proper way to populate $sheets is below:

        $sheets = Sheets::spreadsheet(config('sheets.post_spreadsheet_id'))

                        ->sheetById(config('sheets.post_sheet_id'))

                        ->all();

Then we need to properly format $posts. The following code will work:

        $posts = array();

        foreach ($sheets AS $data) {

            $posts[] = array(

                'name' => $data[0],

                'message' => $data[1],

                'created_at' => $data[2],

            );

        }

As a bonus, we need to add back missing .css and .js files to our project:

Copy https://sheets.kawax.biz/css/app.css into your project under /css/app.css
Copy https://sheets.kawax.biz/js/app.js into your project under /js/app.js

Your homepage will now load properly under https://[server]/public/

9. Update PHP Code (post)

Background:

Now that our homepage shows our form, we need to update the post controller (/app/Http/Controllers/PostController.php) to properly accept the data and add it to the Sheet properly. Again, this code doesn’t work “out-of-the-box”.

The code (lines 31 to 33) below needs to be changed slightly:

        Sheets::spreadsheet(config('sheets.post_spreadsheet_id'))

              ->sheet(config('sheets.post_sheet_id'))

              ->append([$append]);

Note that “->sheet()” function expects the name of the sheet but we are passing in the ID.

The code below fixes this issue:

        Sheets::spreadsheet(config('sheets.post_spreadsheet_id'))

              ->sheetById(config('sheets.post_sheet_id'))

              ->append([$append]);

You can now post using the homepage and it will save the new content in the target Sheet

10. Quick Overview of Sheets Functions

Sheets::spreadsheet()

Loads a spreadsheet by Id

Sheets::spreadsheetByTitle()

Loads a spreadsheet by Title

Sheets::sheet()

Loads a specific sheet by name (should be called only after a specific speadsheet has already been specified)

Sheets::sheetById()

Loads a specific sheet by Id (should be called only after a specific speadsheet has already been specified)

Sheets::sheetList()

Lists available sheets (tabs) within a spreadsheet file (Google Sheet)

Sheets::all()

Gets all values in a specific sheet (only called after loading spreadsheet and specifying sheet)

Sheets::update([array])

Replaces all values in the specified sheet with values from [array] (only called after loading spreadsheet and specifying sheet)

Sheets::append([array])

Appends all values in [array] to the end of the specified sheet (only called after loading spreadsheet and specifying sheet)