Google Spreadsheet Acolyte

Manish Manghwani — Fri, 07 Aug 2020 20:34:34 +0000

Repetition is the first step to become monotonous

Philosophy behind making the package

We usually tend to do things that are repetitive in nature and waste two most crucial entities : time and energy. Both are inversely proportional to each other and yet we require both to survive. Former one validates our presence and latter one is vital for presence. coming back from philosophy and jumping straight to terminal

Introduction

Google Spreadsheet acolyte is a laravel package to dump records from spreadsheet into database table.

Usefulness

It removes dependency from CSV or any other dumping extension. It eradicates the need of intervention with database. You just need to have one active database connection defined within your .env file and table must exist within that database, that's the bare minimum it requires as of now.

Setup

You can install the package via composer:

composer require manish-manghwani/google-spreadsheet-acolyte

Meanwhile you can fulfil other dependencies as follow

Step 1

You must have one google account.

Step 2

Step 3

Create new project

Step 4

Enable Google Sheets Api

Step 5

Create Service Accounts

Step 6

Download Credentials.json

Step 7

Create one spreasheet from same google account and share it with the service account id that you created just now.

After Completing above steps, use the credentials.json file and move it into root directory. (Since they are crucial credentials, you may wish to add it's name in .gitignore file too)

Installation

Install the package via composer using

composer require manish-manghwani/google-spreadsheet-acolyte

Usage

Get the Google Spreadsheet Url. It may look similiar to this https://docs.google.com/spreadsheets/d/1auqTdpciifOA6PH5JbSoRFegdgdr48icvgwqsfWqrqI/edit#gid=0
You should have name of the credential file that was downloaded from service account (for eg: credentials.json)
You should have table name in which data will be inserted (for eg: dummy)

With above things in place you can run below command.

php artisan import:sheet --file-url=https://docs.google.com/spreadsheets/d/1auqTdpciifOA6PH5JbSoxRFegdgdr48icvgwqsfWqrqI/edit#gid=0 --table-name=dummy --credentials-file-name=credentials

NOTE:

If sheet is not shared with service account email id, it will fail.
First row of spreadsheet must be exactly same as of columns present in table.
Spreadsheet name should not be changed it must be Sheet1
Data to be inserted must be present in Sheet1

DEV Community: Manish Manghwani