Managing data from a database can be overwhelming especially for non-developers. One simple solution is to export MySQL data to Google Sheets, where it’s easier to read, share, and visualize.
In this tutorial, you’ll learn how to export MySQL data to Google Sheets step-by-step using PHP and a cron job, without relying on expensive third-party tools.
Why export MySQL data to Google Sheets?
A client of mine recently needed a way to generate dashboards from MySQL data, but without touching the database. The solution? Sync the database with Google Sheets and visualize the data with built-in charts.
For non-technical users or teams that already work in spreadsheets, this approach makes daily reporting and monitoring much easier.
What are the alternatives?
There are several no-code tools and SaaS platforms that can automate this task, such as:
While powerful, they often come with subscription fees and limitations. If your needs are basic or you prefer a custom solution, using PHP and Google’s API is ideal.
What you’ll need
Before we start, make sure you have:
- A Google account
- A Linux server with MySQL installed
- PHP installed on your server
- Composer (for managing PHP packages)
- Access to cron jobs on your server
Set up access to Google Spreadsheet
The first step is to allow your application to connect and write changes to your Google Spreadsheet.
Create a new project in the Google Cloud Console.
After creating your project, activate the Google Sheets API to enable writing to your spreadsheet.
Create a service account (no verification required)
There are several ways to authenticate with Google services to read or write files. In this case, we’ll use a simple and fast method that works well for our needs. If you’d like to explore other options, check out the Google Cloud authentication docs.
A service account is essentially an email address that has the necessary permissions to make changes via your application using a JSON key. Let’s create one now.
How to create a service account:
In Google Cloud Console, go to IAM & Admin → Service Accounts.
The following options are optional, you can leave them blank and click Done!
This should create a service account similar to this:[email protected]
The exact address will depend on your project name and the name you gave the service account.
How to download the JSON key
Next, let’s download the JSON key that will allow our PHP application to authenticate with the service account and access our spreadsheet.
After creating the service account, click on it to open the details screen. Go to the “Keys” tab, then click the dropdown and select “Create new key”. When prompted to choose a key type, select JSON.
Google Cloud will immediately generate and download the key to your computer. Save it in a safe place, you’ll need it to connect PHP with Google Sheets.
Add the service account email to your spreadsheet and grant editor access
Once your service account is created, you need to add its email address to the Google Spreadsheet where the MySQL data will be imported.
Grant it Editor access, just like you would with a regular user. If you haven’t created the spreadsheet yet, you can do so now using the same Google account linked to your Google Cloud project.
How to get the spreadsheetID?
In the URL of your Google Sheets file, you’ll find the ID after /d/ and before /edit?. Copy and paste this into your PHP script.
Creating your PHP application
Now it’s time to create the PHP application that will connect to your MySQL database and export the data to Google Sheets using the service account we just configured.
<?php
require 'vendor/autoload.php';
use Google\Client;
use Google\Service\Sheets;
use Google\Service\Sheets\BatchUpdateSpreadsheetRequest;
use Google\Service\Sheets\ValueRange;
// Database configuration
$dbHost = "dbhost"; //Replace for your database host
$dbUser = "dbuser"; //Replace for your database user
$dbPass = "dbpass"; //Replace for your database password
$dbName = "databasename"; //Replace for your database name
// Google Spreadsheet ID (replace with your own ID)
$spreadsheetId = "spreadsheetID";
// List of tables to exclude
$excludedTables = ['table1', 'table2'];
// Function to get Google client
function getClient()
{
$client = new Client();
$client->setApplicationName('MySQL to Google Sheets');
$client->setScopes([Sheets::SPREADSHEETS]);
$client->setAuthConfig('service_account.json'); // Make sure the service account has access
return $client;
}
// MySQL connection
$mysqli = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
if ($mysqli->connect_error) {
die("Connection error: " . $mysqli->connect_error);
}
// Get all tables from the database
$tablesResult = $mysqli->query("SHOW TABLES");
if (!$tablesResult) {
die("Error getting tables: " . $mysqli->error);
}
// Configure Google Sheets API
$client = getClient();
$service = new Sheets($client);
// Get existing sheets
$spreadsheet = $service->spreadsheets->get($spreadsheetId);
$existingSheets = [];
foreach ($spreadsheet->getSheets() as $sheet) {
$existingSheets[] = $sheet->getProperties()->getTitle();
}
// Iterate over each table and update or create the sheet
while ($table = $tablesResult->fetch_array()) {
$tableName = $table[0];
// Check if the table is in the exclusion list
if (in_array($tableName, $excludedTables)) {
echo "Skipping table: $tableName\n";
continue;
}
// Check if the sheet already exists
if (!in_array($tableName, $existingSheets)) {
try {
$batchRequest = new BatchUpdateSpreadsheetRequest([
'requests' => [['addSheet' => ['properties' => ['title' => $tableName]]]]
]);
$service->spreadsheets->batchUpdate($spreadsheetId, $batchRequest);
echo "Sheet created: $tableName\n";
} catch (Exception $e) {
echo "Error creating sheet: {$e->getMessage()}\n";
continue;
}
} else {
echo "Sheet $tableName already exists. Overwriting...\n";
}
// Get headers
$result = $mysqli->query("SELECT * FROM `$tableName` LIMIT 1");
if (!$result) {
echo "Error getting data from $tableName: {$mysqli->error}\n";
continue;
}
$columns = $result->fetch_fields();
if (empty($columns)) {
echo "Table $tableName has no valid columns. Skipping.\n";
continue;
}
$headers = array_map(fn($col) => $col->name, $columns);
$data = [$headers];
// Configuration for batch processing
$batchSize = 500; // Adjust as needed
$offset = 0;
// END
while (true) {
// Get data in batches using LIMIT and OFFSET
$query = "SELECT * FROM `$tableName` LIMIT $batchSize OFFSET $offset";
$result = $mysqli->query($query);
if (!$result) {
echo "Error getting data from $tableName: {$mysqli->error}\n";
break;
}
if ($result->num_rows === 0) {
echo "No more data to export in $tableName.\n";
break;
}
while ($row = $result->fetch_assoc()) {
$cleanRow = array_map(function ($value) {
return $value === null ? "" : (string) $value;
}, array_values($row));
$data[] = $cleanRow;
}
// END
// Upload data to Google Sheets in batches
$body = new ValueRange(['values' => $data]);
try {
$service->spreadsheets_values->update(
$spreadsheetId,
"{$tableName}!A" . ($offset + 2),
$body,
['valueInputOption' => 'RAW']
);
echo "Exported " . count($data) . " records from offset $offset in $tableName\n";
} catch (Exception $e) {
echo "Error exporting data to $tableName: {$e->getMessage()}\n";
break;
}
// Increment offset and clear data for next batch
$offset += $batchSize;
$data = [$headers];
// END
}
}
// Close connection
$mysqli->close();
echo "Export completed.";
?>
How the MySQL to Google Sheets export script works
Let’s break down the PHP script that automates how to export MySQL data to Google Sheets. This script reads tables from a database, creates corresponding sheets if they don’t exist, and exports the data in batches(500).
1. Load dependencies and configure credentials
The script begins by loading the Google Client library using Composer:
require 'vendor/autoload.php';
It sets up database credentials and your spreadsheet ID. You also define which tables should be excluded from the export:
$dbHost = "dbhost"; // Database host
$dbUser = "dbuser"; // Database user
$dbPass = "dbpass"; // Database password
$dbName = "databasename"; // Database name
$spreadsheetId = "spreadsheetID"; // Target Google Sheet
$excludedTables = ['table1', 'table2']; // Tables to skip
2. Authenticate with Google Sheets API
The function getClient() creates a Google Sheets API client using a service account JSON key:
$client->setAuthConfig('service_account.json');
Make sure the service account has edit access to the spreadsheet.
3. Connect to the MySQL database
The script connects to your MySQL database using mysqli and fetches a list of all tables:
$mysqli = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
$tablesResult = $mysqli->query("SHOW TABLES");
4. Prepare the Google Sheets environment
The script retrieves existing sheets in the spreadsheet to avoid duplicates:
$spreadsheet = $service->spreadsheets->get($spreadsheetId);
5. Loop through each table and export data
For each table in the database:
- If it’s excluded, it’s skipped.
- If a sheet for the table doesn’t exist, it creates one.
- It pulls the column headers and data in batches of 500 rows.
- Each batch is written to the corresponding sheet using the Sheets API.
$service->spreadsheets_values->update(
"{$tableName}!A" . ($offset + 2),
$body,
['valueInputOption' => 'RAW']
);
This batch system ensures the script can handle large datasets without memory issues.
6. Final cleanup
Once all tables are processed, the database connection is closed and a completion message is shown:
$mysqli->close();
echo "Export completed.";
Set up a cron job in Linux
To automate the data export from MySQL to Google Sheets, you can schedule your PHP script using a cron job on your Linux server.
Creating a Cronjob
* * * * * /usr/bin/php /var/www/domain.com/index.php > /dev/null 2>&1
Possibles error logs to monitor
It’s important to monitor your script’s output and error logs in case something fails during the export from MySQL to Google Sheets.
1 – “Connect failed: Access denied for user ‘root’@’localhost’ (using password: YES)”
I solved in this way: I logged in with root username
mysql -u root -p -h localhost
I created a new user with
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'new_pass';
Then I created the database
CREATE DATABASE shop;
I granted privileges for new user for this database
GRANT ALL PRIVILEGES ON shop.* TO 'newuser'@'localhost';
Then I logged out root and logged in new user
quit;
mysql -u newuser -p -h localhost
2 – Increase the number of write requests per minute per user in Google
If you’re working with large datasets or syncing frequently, you may need to increase the write requests per minute per user limit in your Google Cloud project. You can request a quota increase in the Google Cloud Console under IAM & Admin → Quotas.
—
Exporting MySQL data to Google Sheets using PHP isn’t just about saving time, it’s about empowering your team with real-time, accessible information. Whether you’re building dashboards, automating reports, or simplifying collaboration, this solution gives you full control without relying on expensive tools.
Have you implemented something similar? Or are you thinking of taking this further?
Leave a comment below, I’d love to hear how you’re using PHP and Google Sheets together!
Leave a Reply