arindam/gsheet-appscript

A laravel package for access and manage any google sheet

v3.0.0 2024-03-10 18:07 UTC

MIT

• Arindam Roy

• google sheet
• google appscript

README

A laravel package for access and manage any google sheet.

Installation

No dependency on PHP version and LARAVEL version

STEP 1: Run the composer command:

composer require arindam/gsheet-appscript

STEP 2: Laravel without auto-discovery:

If you don't use auto-discovery, add the ServiceProvider to the providers array in config/app.php

In providers section

Arindam\GsheetAppScript\GsheetAppScriptServiceProvider::class,

In aliases section

'GsheetAppScript' => Arindam\GsheetAppScript\Gsheet\GsheetAppScriptClassFacade::class,

STEP 3: Publish the package config:

php artisan vendor:publish --provider="Arindam\GsheetAppScript\GsheetAppScriptServiceProvider" --force
-OR-
php artisan vendor:publish --tag="gsheet-appscript:config"

STEP 4: Add Google Sheet AppScript API Url to .env file:

GSHEET_APPSCRIPT_API_URL=_YOUR_APP_SCRIPT_API_URL_

How to use?: It's Very Easy

FACADE HELPER FUNCTIONS

use GsheetAppScript;

GsheetAppScript::allRecords(); //get all records from google sheet

GsheetAppScript::addRow(['Text1', 'Text2', 'Text3' ...]); //add row in the google sheet

GsheetAppScript::setHeading(['Heading1', 'Heading2', 'Heading3' ...]); //set or edit heading in the google sheet

GsheetAppScript::editRow(['id' => 4, 'Text1', 'Text2', 'Text3' ...]); //edit data in the google sheet, just pass the row number as id with data

GsheetAppScript::removeRow(['id' => 4]); //remove row from google sheet, just pass the row number

GsheetAppScript::clearSheet(); //delete all records in google sheet

DIRECT USE BY ROUTE

>> Just install and run the below route

Ex: http://your-website/onex/gsheet

Ex: http://localhost:8000/onex/gsheet

You can modify the configuration settings in - "config/gsheet-appscript.php":

/** If you want to disable the route or this feature, then make it false */
'is_route_enabled' => true,

/** If you want to change the route prefix */
'route_prefix' => 'onex',

/** If you want to change the route name or path */
'route_name' => 'gsheet',

/** If you want to change the page heading */
'page_heading' => 'Google Sheet',

/** If you want to enable the securiry for access the google sheet information
 *  Then make it ('is_enabled') true and also you can set login-id and password through .env
 */
'authentication' => [
    'is_enabled' => env('GSHEET_APPSCRIPT_AUTH_ENABLED', false),
    'login_id' => env('GSHEET_APPSCRIPT_LOGIN_ID', 'onexadmin'),
    'password' => env('GSHEET_APPSCRIPT_LOGIN_PASSWORD', 'onexpassword')
]

Google AppScript Code:

Just Copy & paste below code in your google sheet appscript section & get the app url

/** All Get Request */
function doGet() {
  return ContentService.createTextOutput(getAllRows());
}

/** All Post Request */
function doPost(req) {
  let data = JSON.parse(req.postData.contents);
  if(data.actionkey == "SAVE") {
    return ContentService.createTextOutput(addSaveRow(data));
  }
  if(data.actionkey == "UPDATE") {
    return ContentService.createTextOutput(updateSaveRow(data));
  }
  if(data.actionkey == "DELETE") {
    return ContentService.createTextOutput(deleteRow(data));
  }
  if(data.actionkey == "VIEW") {
    return ContentService.createTextOutput(viewRow(data));
  }
  if(data.actionkey == "CLEAR") {
    return ContentService.createTextOutput(clearSheet());
  }
}

/** Add */
function addSaveRow(jsonObjData) {
  if (jsonObjData['actionkey']) {
    delete jsonObjData['actionkey'];
  }
  SpreadsheetApp.getActiveSheet().appendRow(Object.values(jsonObjData));
  return 'SUCCESS';
}

/** Edit */
function getRowId(textId) {
  let findData = SpreadsheetApp.getActiveSheet().createTextFinder(textId).matchEntireCell(true).findNext();
  if(findData) {
    return findData.getRow();
  }
  return 0;
}

/** Update */
function updateSaveRow(jsonObjData) {
  let sheetRowNo = jsonObjData.id;
  if (jsonObjData['actionkey']) {
    delete jsonObjData['actionkey'];
  }
  if (jsonObjData['id']) {
    delete jsonObjData['id'];
  }
  if(sheetRowNo) {
    Object.keys(jsonObjData).forEach(function(key, index) {
      SpreadsheetApp.getActiveSheet().getRange(sheetRowNo, index + 1).setValue(jsonObjData[key]);
    });
    return "SUCCESS";
  }
  return "ERROR";
}

/** View a Record */
function viewRow(jsonObjData) {
  let findData = SpreadsheetApp.getActiveSheet().createTextFinder(jsonObjData.id).matchEntireCell(true).findNext();
  if(findData) {
    return findData;
  }
  return 0;
}

/** Delete */
function deleteRow(jsonObjData) {
  if(jsonObjData.id) {
    SpreadsheetApp.getActiveSheet().deleteRow(jsonObjData.id);
    return "SUCCESS";
  } else {
    return "ERROR";
  }
}

/** Get All */
function getAllRows() {
  let obj = {};
  let data = SpreadsheetApp.getActiveSheet().getDataRange().getValues().filter(row => row.join(""));
  obj = {
    data: data,
    cols: SpreadsheetApp.getActiveSheet().getDataRange().getNumColumns()
  }
  return JSON.stringify(obj);
}

/** Clear All */
function clearSheet() {
  SpreadsheetApp.getActiveSheet().clear({contentsOnly: true});
  return "SUCCESS";
}

license:

The MIT License (MIT). Please see License File for more information.

Post Issues: if found any

If have any issue please write me.