sibedas/app/Services/GoogleSheetService.php

<?php

namespace App\Services;

use Google_Client;
use Google_Service_Sheets;

class GoogleSheetService
{
    /**
     * Create a new class instance.
     */
    protected $client;
    protected $service;
    protected $spreadsheetID;
    public function __construct()
    {
        $this->client = new Google_Client();
        $this->client->setApplicationName("Sibedas Google Sheets API");
        $this->client->setScopes([Google_Service_Sheets::SPREADSHEETS_READONLY]);
        $this->client->setAuthConfig(storage_path("app/teak-banner-450003-s8-ea05661d9db0.json"));
        $this->client->setAccessType("offline");

        $this->service = new Google_Service_Sheets($this->client);
        $this->spreadsheetID = env("SPREAD_SHEET_ID");

        $this->service_sheets = new Google_Service_Sheets($this->client);
    }

    public function getSheetData($range){
        $response = $this->service->spreadsheets_values->get($this->spreadsheetID, $range);
        return $response->getValues();
    }

    public function getLastRowByColumn($column = "A")
    {
        try{
            // Ambil spreadsheet
            $spreadsheet = $this->service->spreadsheets->get($this->spreadsheetID);
            $sheets = $spreadsheet->getSheets();
    
            if (!empty($sheets)) {
                // Ambil nama sheet pertama dengan benar
                $firstSheetTitle = $sheets[0]->getProperties()->getTitle();
    
                // ✅ Format range harus benar!
                $range = "{$firstSheetTitle}!{$column}:{$column}";
    
                // Ambil data dari kolom yang diminta
                $response = $this->service->spreadsheets_values->get($this->spreadsheetID, $range);
                $values = $response->getValues();
    
                // Cek nilai terakhir yang tidak kosong
                $lastRow = 0;
                if (!empty($values)) {
                    foreach ($values as $index => $row) {
                        if (!empty($row[0])) { // Jika ada data, update lastRow
                            $lastRow = $index + 1;
                        }
                    }
                }
    
                return $lastRow;
            }
    
            return 0;
        }catch(\Exception $e){
            throw $e;
        }
    }
    public function getHeader()
    {
        try{
            $spreadsheet = $this->service->spreadsheets->get($this->spreadsheetID);
            $sheets = $spreadsheet->getSheets();
    
            // Ambil nama sheet pertama
            $firstSheetTitle = $sheets[0]->getProperties()->getTitle();
    
            // Ambil data dari baris pertama (header)
            $range = "{$firstSheetTitle}!1:1";
            $response = $this->service->spreadsheets_values->get($this->spreadsheetID, $range);
            $values = $response->getValues();
    
            // Kembalikan header (baris pertama)
            return !empty($values) ? $values[0] : [];
        }catch(\Exception $e){
            throw $e;
        }
    }

    public function getLastColumn()
    {
        $spreadsheet = $this->service->spreadsheets->get($this->spreadsheetID);
        $sheets = $spreadsheet->getSheets();

        // Ambil nama sheet pertama
        $firstSheetTitle = $sheets[0]->getProperties()->getTitle();

        // Ambil baris pertama untuk mendapatkan jumlah kolom yang terisi
        $range = "{$firstSheetTitle}!1:1";
        $response = $this->service->spreadsheets_values->get($this->spreadsheetID, $range);
        $values = $response->getValues();

        // Hitung jumlah kolom yang memiliki nilai
        return !empty($values) ? count(array_filter($values[0], fn($value) => $value !== "")) : 0;
    }

    public function getSheetDataCollection($totalRow = 10){
        try{
            $spreadsheet = $this->service->spreadsheets->get($this->spreadsheetID);
            $sheets = $spreadsheet->getSheets();
            $firstSheetTitle = $sheets[0]->getProperties()->getTitle();
    
            $header = $this->getHeader();
            $header = array_map(function($columnHeader) {
                // Trim spaces first, then replace non-alphanumeric characters with underscores
                $columnHeader = trim($columnHeader);
                return strtolower(preg_replace('/[^A-Za-z0-9_]/', '_', $columnHeader));
            }, $header);
            $range = "{$firstSheetTitle}!2:{$totalRow}";
    
            $response = $this->service->spreadsheets_values->get($this->spreadsheetID, $range);
            $values = $response->getValues();
    
            $mappedData = [];
            if (!empty($values)) {
                foreach ($values as $row) {
                    $rowData = [];
                    foreach ($header as $index => $columnHeader) {
                        // Map header to the corresponding value from the row
                        $rowData[$columnHeader] = isset($row[$index]) ? $row[$index] : null;
                    }
                    $mappedData[] = $rowData;
                }
            }
    
            return $mappedData;
        }catch(\Exception $e){
            throw $e;
        }
    }
    public function get_data_by_sheet($no_sheet = 1){
        $spreadsheet = $this->service->spreadsheets->get($this->spreadsheetID);
        $sheets = $spreadsheet->getSheets();
        $sheetTitle = $sheets[$no_sheet]->getProperties()->getTitle();
        $range = "{$sheetTitle}";
        $response = $this->service->spreadsheets_values->get($this->spreadsheetID, $range);
        $values = $response->getValues();
        return!empty($values)? $values : [];
    }
}