Connecting Laravel to a Google Sheet

Sheldon P.
46-1__-vJQqeCmpw-kghDFX8yJw.png

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

  1. Installation of revolution/laravel-google-sheets
  2. Set up Google credentials (API Key)
  3. Set up Google Sheet
  4. Set up Google credentials (Service account key)
  5. Set permission on Google Sheet
  6. Enable required Google APIs
  7. Final configuration of Laravel Settings / Files
  8. Update PHP Code (homepage)
  9. Update PHP Code (post)
  10. 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)

  1. Go to https://developers.google.com/console and sign in (if necessary)
  2. Create a new project (if necessary)

     
  3. Click on “credentials” from the left-hand menu
  4. Click “Create credentials” followed up “OAuth client ID”
  5. If necessary, “Configure consent screen”
  6. Set the type to “Web application”
  7. Copy the “Client ID” (leave this window open)
  8. Open /.env and paste the ID next to GOOGLE_CLIENT_ID=
  9. Open /config/google.php and paste the ID into 'client_id' => env('GOOGLE_CLIENT_ID', ''),
  10. Go back to the Google Console and copy the “Client Secret”
  11. Open /.env and paste the ID next to GOOGLE_CLIENT_SECRET=
  12. Open /config/google.php and paste the ID into 'client_secret'    => env('GOOGLE_CLIENT_SECRET', ''),

 

3. Set up Google Sheet

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

 

4. Set up Google credentials (Service account key)

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

           

 

5. Set permission on Google Sheet

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

 

6. Enable required Google APIs

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

 

 

7. Final configuration of Laravel Settings / Files

  1. Open /.env and set the following:
    1. GOOGLE_SERVICE_ENABLED=true
    2. 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:

  1. /app/Http/Controllers/HomeController.php which displays a simple form and content from the target Sheet
  2. /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:

  1. Copy https://sheets.kawax.biz/css/app.css into your project under /css/app.css
  2. 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)