How to Import Google Sheet Data into MySQL Database Using PHP

This article will guide you through the process of importing data from a Google Sheet into a MySQL database using PHP. The provided PHP script uses the Google Sheets API and a direct HTTP request to fetch data from the sheet and insert it into a MySQL database.

Prerequisites
Google API Key:

You need a Google API key to access the Google Sheets API.
To get one, visit the Google Cloud Console, enable the Google Sheets API, and create an API key.
Google Sheet:

Create a Google Sheet with the data you want to import. Ensure the first row contains column headers.
PHP Environment:

Set up PHP on your server or local machine.
Install Composer for dependency management.
MySQL Database:

Ensure a MySQL database is set up, and you have a table ready to receive the data.
Steps
Step 1: Create a Google Sheet
Open Google Sheets and create a new spreadsheet.

Add data with column headers such as Name, Email, Phone, and Country.

Note the Spreadsheet ID from the URL:

bash
Copy code
Here, the Spreadsheet ID is:

Copy code
1Y2vCCPcU9KQsVYzn9216ZEtmuBhxfEKc6XRRkOfLPGg
Make the sheet publicly accessible:

Click Share Get Link Anyone with the link can view.
Step 2: Create MySQL Table
Prepare a MySQL table to store the data from the Google Sheet. Use the following SQL:

sql
Copy code
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255),
phone VARCHAR(255),
country VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Step 3: Install PHP Dependencies
Use Composer to include required libraries:

bash
Copy code
composer require google/apiclient
Step 4: PHP Script
Use the following PHP script to fetch data from the Google Sheet and insert it into the MySQL database:

php
Copy code
?php
// Include Composer autoload

use Google\Client;
use Google\Service\Sheets;

// Google Sheets setup
$apiKey = 'LmKOhpCj5jLuT38H8toWL8'; // Replace with your API key
$spreadsheetId = '1Y2vCC16ZEtmuBhxfEKc6XRRkOfLPGg'; // Replace with your spreadsheet ID
$range = 'Sheet1!A1:D'; // Adjust the range based on your sheet's data

// Fetch data from Google Sheets

$response = file_get_contents($url);
$data = json_decode($response, true);

if (isset($data['values'])) {
$values = $data['values'];

// MySQL database connection
$servername = "localhost";
$username = "root";
$password = "";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn-connect_error) {
die("Connection failed: " . $conn-connect_error);
}

foreach ($values as $row) {
// Skip header row if needed
if ($row[0] == 'Name') {
continue;
}

$name = $row[0] ?? null;
$email = $row[1] ?? null;
$phone = $row[2] ?? null;
$country = $row[3] ?? null;

// Insert into MySQL database
$stmt = $conn-prepare("INSERT INTO users (name, email, phone, country) VALUES (?, ?, ?, ?)");
$stmt-bind_param("ssss", $name, $email, $phone, $country);
$stmt-execute();
}

echo "Data imported successfully from Google Sheet!";
} else {
echo "Error fetching data.";
}
?
Code Explanation
Google Sheets API Setup:

The script uses the API key and file_get_contents to make a GET request to the Google Sheets API.
It fetches the specified range (Sheet1!A1:D) from the spreadsheet.
Data Parsing:

The data is retrieved in JSON format and parsed into an array.
MySQL Connection:

A connection to the MySQL database is established using mysqli.
Data Insertion:

The script iterates through the data and inserts it into the users table.
Skipping Headers:

The first row (headers) is skipped during the insertion.
Step 5: Run the Script
Place it in your web server’s document root.
Access the script via your browser or run it from the CLI:
bash
Copy code
Common Issues
API Key Errors:

Ensure the API key is valid and has access to the Google Sheets API.
Access Denied:

Make sure the sheet is publicly accessible.
SQL Errors:

Verify the MySQL table structure matches the data format in the sheet.
Rate Limits:

Google Sheets API has usage limits. Consider enabling billing if you exceed the free quota.