PHP code example of reandimo / google-sheets-helper

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

reandimo / google-sheets-helper example snippets

reandimo\GoogleSheetsApi\Helper;

// Set environment variables
putenv('credentialFilePath=path/to/credentials.json');
putenv('tokenPath=path/to/token.json');

$sheets = new Helper();
$sheets->setSpreadsheetId('your-spreadsheet-id');
$sheets->setWorksheetName('Sheet1');
$sheets->setSpreadsheetRange('A1:Z1000');

reandimo\GoogleSheetsApi\Helper;

$credentialFilePath = 'path/to/credentials.json';
$tokenPath = 'path/to/token.json';

$sheets = new Helper($credentialFilePath, $tokenPath);
$sheets->setSpreadsheetId('your-spreadsheet-id');
$sheets->setWorksheetName('Sheet1');

use reandimo\GoogleSheetsApi\Helper;

// Configure environment variables once
putenv('credentialFilePath=path/to/credentials.json');
putenv('tokenPath=path/to/token.json');

// Create multiple instances for different sheets
$sheet1 = new Helper();
$sheet1->setSpreadsheetId('spreadsheet-1-id');
$sheet1->setWorksheetName('Sheet1');
$sheet1->setSpreadsheetRange('A1:A20');

$sheet2 = new Helper();
$sheet2->setSpreadsheetId('spreadsheet-2-id');
$sheet2->setWorksheetName('Sheet2');
$sheet2->setSpreadsheetRange('B1:B20');

try {
    $sheets->setSpreadsheetRange('A1:C10');
    $values = $sheets->get();
    
    if ($values && !empty($values)) {
        foreach ($values as $row) {
            echo implode(', ', $row) . "\n";
        }
    } else {
        echo "No data found in range.\n";
    }
} catch (Exception $e) {
    echo "Error reading data: " . $e->getMessage() . "\n";
}

try {
    $value = $sheets->getSingleCellValue('B2');
    echo "Value in B2: " . ($value ?: 'empty') . "\n";
} catch (Exception $e) {
    echo "Error reading cell: " . $e->getMessage() . "\n";
}

try {
    $sheets->setSpreadsheetRange('A1:Z100');
    $result = $sheets->findCellByValue('search term');
    
    if ($result) {
        echo "Found at cell: {$result['cell']} (row {$result['row']}, column {$result['column']})\n";
    } else {
        echo "Value not found.\n";
    }
} catch (Exception $e) {
    echo "Error searching: " . $e->getMessage() . "\n";
}

try {
    $sheets->setSpreadsheetRange('A1:C1');
    $rowsUpdated = $sheets->appendSingleRow([
        'John Doe',
        '[email protected]',
        'Developer'
    ]);
    
    if ($rowsUpdated >= 1) {
        echo "Row appended successfully.\n";
    } else {
        echo "Failed to append row.\n";
    }
} catch (Exception $e) {
    echo "Error appending row: " . $e->getMessage() . "\n";
}

try {
    $sheets->setSpreadsheetRange('A1:C');
    $rowsUpdated = $sheets->append([
        ['Jane Smith', '[email protected]', 'Designer'],
        ['Bob Johnson', '[email protected]', 'Manager'],
        ['Alice Brown', '[email protected]', 'Analyst']
    ]);
    
    echo "Appended {$rowsUpdated} rows.\n";
} catch (Exception $e) {
    echo "Error appending rows: " . $e->getMessage() . "\n";
}

use Google_Model;

$sheets->appendSingleRow([
    'John Doe',
    '[email protected]',
    Google_Model::NULL_VALUE, // Leave cell empty
    'Active'
]);

try {
    $update = $sheets->updateSingleCell('B5', 'Updated value');
    
    if ($update->getUpdatedCells() >= 1) {
        echo "Cell updated successfully.\n";
    } else {
        echo "No cells were updated.\n";
    }
} catch (Exception $e) {
    echo "Error updating cell: " . $e->getMessage() . "\n";
}

try {
    $sheets->setSpreadsheetRange('A1:F5');
    $update = $sheets->update([
        ['Header1', 'Header2', 'Header3', 'Header4', 'Header5', 'Header6'],
        ['Data1', 'Data2', 'Data3', 'Data4', 'Data5', 'Data6'],
        ['Data7', 'Data8', 'Data9', 'Data10', 'Data11', 'Data12'],
        ['Data13', 'Data14', 'Data15', 'Data16', 'Data17', 'Data18'],
        ['Data19', 'Data20', 'Data21', 'Data22', 'Data23', 'Data24']
    ]);
    
    echo "Updated {$update->getUpdatedCells()} cells.\n";
} catch (Exception $e) {
    echo "Error updating range: " . $e->getMessage() . "\n";
}

try {
    $worksheets = $sheets->getSpreadsheetWorksheets();
    
    foreach ($worksheets as $worksheet) {
        echo "Sheet ID: {$worksheet['id']}, Title: {$worksheet['title']}\n";
    }
} catch (Exception $e) {
    echo "Error getting worksheets: " . $e->getMessage() . "\n";
}

try {
    $sheets->setWorksheetName('Sheet1');
    $newSheetId = $sheets->duplicateWorksheet('Copy of Sheet1');
    
    if ($newSheetId) {
        echo "Worksheet duplicated successfully. New ID: {$newSheetId}\n";
    } else {
        echo "Failed to duplicate worksheet.\n";
    }
} catch (Exception $e) {
    echo "Error duplicating worksheet: " . $e->getMessage() . "\n";
}

try {
    $deleted = $sheets->deleteWorksheet('SheetToDelete');
    
    if ($deleted) {
        echo "Worksheet deleted successfully.\n";
    } else {
        echo "Failed to delete worksheet.\n";
    }
} catch (Exception $e) {
    echo "Error deleting worksheet: " . $e->getMessage() . "\n";
}

try {
    $renamed = $sheets->renameWorksheet('OldName', 'NewName');
    
    if ($renamed) {
        echo "Worksheet renamed successfully.\n";
    } else {
        echo "Failed to rename worksheet.\n";
    }
} catch (Exception $e) {
    echo "Error renaming worksheet: " . $e->getMessage() . "\n";
}

try {
    $newSheetId = $sheets->addWorksheet('NewSheet', 100, 10);
    echo "New worksheet created with ID: {$newSheetId}\n";
} catch (Exception $e) {
    echo "Error creating worksheet: " . $e->getMessage() . "\n";
}

try {
    // Change background color (RGB values)
    $sheets->setSpreadsheetRange('A1:Z10');
    $sheets->colorRange([142, 68, 173]); // Purple
    
    // Clear range values
    $cleared = $sheets->clearRange();
    if ($cleared) {
        echo "Range cleared successfully.\n";
    }
} catch (Exception $e) {
    echo "Error formatting: " . $e->getMessage() . "\n";
}

try {
    $newSpreadsheetId = $sheets->create('My New Spreadsheet');
    echo "Created new spreadsheet with ID: {$newSpreadsheetId}\n";
} catch (Exception $e) {
    echo "Error creating spreadsheet: " . $e->getMessage() . "\n";
}

use reandimo\GoogleSheetsApi\Helper;

$columnIndex = Helper::getColumnLettersIndex('AZ'); // Returns 52
echo "Column AZ is at index: {$columnIndex}\n";

$columnIndex = Helper::getColumnLettersIndex('AA'); // Returns 27
echo "Column AA is at index: {$columnIndex}\n";

try {
    $sheets->setSpreadsheetRange('A1:Z100');
    $values = $sheets->get();
    
    // Process data...
    
} catch (Google_Service_Exception $e) {
    // Handle Google API specific errors
    echo "Google API Error: " . $e->getMessage() . "\n";
    echo "Error Code: " . $e->getCode() . "\n";
    
} catch (Exception $e) {
    // Handle general errors
    echo "General Error: " . $e->getMessage() . "\n";
}

bash
php ./vendor/reandimo/google-sheets-helper/firstauth