stagfe2/app/Http/Controllers/GsheetController.php

<?php

namespace App\Http\Controllers;

use Exception;
use Google\Service\Sheets\ValueRange;
use Google_Client;
use Illuminate\Console\Command;
use Revolution\Google\Sheets\Facades\Sheets;
use Revolution\Google\Sheets\Sheets as SheetsSheets;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class GsheetController extends Controller
{
    

    public function testGsheet(){
        $sql = 'SELECT uid, name_first, name_last FROM client';
        $sqlParams = [];

        $values = $this->getValues($sql, $sqlParams);

        $spreadsheetId = '1jRCkukeSfZufY8gpKSGYgv9cgeDbA1ZYP4pKJhyPP_o';
        $sheetName = 'Sheet1';
        return $this->exportToGsheet($sql, $sqlParams, $spreadsheetId, $sheetName);

    }


    private function exportToGsheet($sql, $sqlParams, $spreadsheetId, $sheetName){
       
        $client = $this->getApiClient();
        $service = new \Google\Service\Sheets($client);

        
        $values = $this->getValues($sql, $sqlParams);

        $range = $sheetName.'!A1:V1';
        $body = new ValueRange([
            'values' => $values
        ]);
        $params = [
           'valueInputOption' => 'RAW'
        ];

        $result = $service->spreadsheets_values->append($spreadsheetId, $range, $body, $params);

        return $result;
    }


    function getApiClient(){
      
        $KEY_FILE_LOCATION = storage_path('stag-gsheets-d9ead2f78b4b.json');

        $client = new Google_Client();
        $client->setApplicationName("My Rooster Admin");
        $client->setAuthConfig($KEY_FILE_LOCATION);
        
        $client->setScopes([\Google\Service\Sheets::DRIVE, \Google\Service\Sheets::SPREADSHEETS]);

        return $client;

    }


    private function getValues($sql, $sqlParams){

        $values = [];

        $raws = DB::select($sql, $sqlParams);

        if(!count($raws)){
            throw new \Exception("No data");
        }

        $firstRaw = $raws[0];

        $headers = [];
        foreach($firstRaw as $k => $val) {
            $headers[] = $k;
        }
        $values[] = $headers;

        
        foreach($raws as $raw){
            $rawStr = '';
            foreach($headers as $header ){
                $rawStr = $rawStr.$raw->$header.'~~';
            }
            $values[] = explode('~~', $rawStr);
        }
        
       
        return $values;
    }

}