如何在WordPress中对接Google Sheets为现有短代码提供定时更新的定价数据

Got it, let's walk through exactly how to hook up Google Sheets as your WordPress pricing data source, integrate it with your existing shortcode, and set up automatic updates via Cron. This setup will let your client edit prices directly in Sheets without touching your site code—perfect for non-technical users.

First, you need to authenticate WordPress to pull data from your Sheet. Here's how:

• Head to the Google Cloud Console, create a new project, and enable the Google Sheets API under "APIs & Services".
• Create a Service Account for the project, generate a JSON key file, and download it to your computer.
• Open your Google Sheet, click "Share", and add the service account's email (found in the JSON key) as an editor or viewer (viewer is enough for read-only pricing data; use editor if you ever need to write back to the Sheet later).
• Upload the JSON key file to your WordPress server—store it in a secure directory outside your web root if possible, or use a secrets management plugin to avoid public access.

WordPress needs the official Google Client library to communicate with the Sheets API. Use Composer to install it in your theme or custom plugin directory:

composer require google/apiclient:^2.0

If you don’t use Composer, you can download the library manually and include it in your code, but Composer is the cleaner, more maintainable approach.

Add a PHP function to your theme's functions.php file or a custom plugin that fetches and parses your pricing data. Here’s a simplified, production-ready example:

function fetch_google_sheets_pricing() {
    // Path to your service account JSON key (adjust this to match your server path)
    $key_path = '/var/www/your-site/secure/service-account-key.json';
    
    // Initialize Google Client
    require_once __DIR__ . '/vendor/autoload.php';
    $client = new Google_Client();
    $client->setApplicationName('WordPress Pricing Sync');
    $client->setScopes(Google_Service_Sheets::SPREADSHEETS_READONLY);
    $client->setAuthConfig($key_path);
    $client->setAccessType('offline');
    
    // Initialize Sheets Service
    $service = new Google_Service_Sheets($client);
    
    // Your Sheet ID (found in the Sheet URL) and data range (e.g., "Pricing!A2:B10" = rows 2-10, columns A-B in the "Pricing" tab)
    $spreadsheet_id = 'YOUR_SPREADSHEET_ID_HERE';
    $range = 'Pricing!A2:B10';
    
    try {
        $response = $service->spreadsheets_values->get($spreadsheet_id, $range);
        $values = $response->getValues();
        
        if (empty($values)) {
            return false;
        }
        
        // Format data into an associative array for easy use in your shortcode
        $pricing_data = [];
        foreach ($values as $row) {
            $pricing_data[$row[0]] = $row[1]; // [Product Name => Price]
        }
        
        // Cache the data to avoid hitting API rate limits (expires in 1 hour)
        set_transient('google_sheets_pricing', $pricing_data, HOUR_IN_SECONDS);
        
        return $pricing_data;
    } catch (Exception $e) {
        // Fallback to cached data if the API call fails
        error_log('Google Sheets API Error: ' . $e->getMessage());
        return get_transient('google_sheets_pricing');
    }
}

Adjust the $spreadsheet_id, $range, and key path to match your setup. The function uses WordPress transients to cache data, so you won’t make unnecessary API calls on every page load.

Now hook this data into your existing shortcode. Let’s assume your shortcode is [pricing_table]—modify its callback function to use the fetched pricing data:

function pricing_table_shortcode($atts) {
    // Get pricing data (uses cached data if available; fetches fresh if needed)
    $pricing_data = fetch_google_sheets_pricing();
    
    if (!$pricing_data) {
        return '<p class="pricing-error">Sorry, pricing data is unavailable right now.</p>';
    }
    
    // Build your pricing table HTML to match your existing shortcode's structure
    $html = '<div class="pricing-grid">';
    foreach ($pricing_data as $product => $price) {
        $html .= sprintf(
            '<div class="pricing-card">
                <h3>%s</h3>
                <div class="price-tag">$%s</div>
             </div>',
            esc_html($product),
            esc_html($price)
        );
    }
    $html .= '</div>';
    
    return $html;
}
add_shortcode('pricing_table', 'pricing_table_shortcode');

Tweak the HTML output to match your site’s existing design—this is just a basic example to get you started.

To keep the data fresh without manual intervention, set up a WordPress Cron job to refresh the pricing data on a schedule. Add this to your functions.php or custom plugin:

// Register the Cron event when the site loads
function register_pricing_sync_cron() {
    if (!wp_next_scheduled('update_google_sheets_pricing')) {
        // Run daily at midnight (use 'hourly', 'twicedaily', or a custom interval if needed)
        wp_schedule_event(time(), 'daily', 'update_google_sheets_pricing');
    }
}
add_action('wp_loaded', 'register_pricing_sync_cron');

// Hook the fetch function to the Cron event
function run_pricing_sync() {
    fetch_google_sheets_pricing();
}
add_action('update_google_sheets_pricing', 'run_pricing_sync');

If you need a custom interval (e.g., every 6 hours), you can register one with the cron_schedules filter.

• First, add your shortcode to a test page and confirm it displays the pricing data correctly.
• Manually trigger the Cron job to test updates: use a plugin like WP Crontrol to run the update_google_sheets_pricing event, or add a temporary debug hook to run it via a URL.
• Edit a price in Google Sheets, wait for the Cron to run, and verify the site updates automatically.

Pro Tips #

• Security: Never commit your service account JSON key to version control, and set strict file permissions on the server so only WordPress can access it.
• Error Logging: The example includes error_log() calls to track API failures—check your server’s error log if pricing data isn’t updating.
• Data Validation: Add checks in the fetch_google_sheets_pricing() function to ensure prices are valid numbers (e.g., is_numeric($row[1])) to avoid display issues.

内容的提问来源于stack exchange，提问作者The_fatness