googlesheetsToPHP

<?php

require_once __DIR__ . '/vendor/autoload.php';

// Google Sheets setup
$client = new \Google_Client();
$client->setApplicationName('Google Sheets with PHP');
$client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
$client->setAccessType('offline');
$client->setAuthConfig(__DIR__ . '/credentials.json');

$service = new \Google_Service_Sheets($client); // create new instance, allows interaction
$spreadsheetId = "testing123";

// Database connection
$host = getenv('DB_HOST') ?: '123';
$port = getenv('DB_PORT') ?: '3306';
$username = getenv('DB_USER') ?: 'testing';
$password = getenv('DB_PASS') ?: 'testing';
$database = getenv('DB_NAME') ?: 'db_testing';

try {
    // Connect to the database
    $pdo = new PDO("mysql:host=$host;port=$port;dbname=$database", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Get the table name
    $tableName = 'acc_ms'; // CHANGE THIS

    // Fetch data from the database
    $stmt = $pdo->query("SELECT * FROM $tableName");
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // check for encoding issues
    foreach($data as $index => $row) {
        foreach($row as $key => $value) {
            $data[$index][$key] = utf8_encode($value);
        }
    }

    if (empty($data)) {
        echo "No data found in the table.";
        exit;
    }

    // Prepare data for Google Sheets
    if(!empty($data)) {
        $values = [];
        $headers = array_keys($data[0]);
        $values[] = $headers;

        foreach ($data as $row) {
            $rowData = [];
            foreach ($headers as $header) {
                $rowData[] = ($row[$header] === null) ? '' : (string)$row[$header];
            }
            $values[] = $rowData;
        }
    } else {
        echo "No data to write.";
    }
    // Debugging: Print the data fetched from the database
    echo "<pre>";
    print_r($data);
    echo "</pre>";
    // Check if a sheet with the table name already exists
    $spreadsheet = $service->spreadsheets->get($spreadsheetId);
    $sheets = $spreadsheet->getSheets();
    $sheetExists = false;
    foreach ($sheets as $sheet) {
        if ($sheet->getProperties()->getTitle() === $tableName) {
            $sheetExists = true;
            break;
        }
    }

    if (!$sheetExists) {
        // Create a new sheet with the table name
        $requests = [
            new Google_Service_Sheets_Request([
                'addSheet' => [
                    'properties' => [
                        'title' => $tableName
                    ]
                ]
            ])
        ];

        $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
            'requests' => $requests
        ]);

        $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);
    }
    // Debugging: Print the values prepared for Google Sheets
    echo "<pre>";
    print_r($values);
    echo "</pre>";
    if (!empty($values)) {
        // Prepare the request to update Google Sheets
        $body = new Google_Service_Sheets_ValueRange([
            'values' => $values
        ]);
        $params = [
            'valueInputOption' => 'RAW'
        ];

        // Update the sheet
        $range = $tableName . '!A1';
        $result = $service->spreadsheets_values->update($spreadsheetId, $range, $body, $params);

        printf("Sheet '%s' updated with %d cells.", $tableName, $result->getUpdatedCells());
    } else {
        echo "No data to write.";
    }

} catch(PDOException $e) {
    echo "Database connection failed: " . $e->getMessage();
} catch(Exception $e) {
    echo "Error: " . $e->getMessage();
    if ($e instanceof Google_Service_Exception) {
        print_r($e->getErrors());
    }
}