How to Sync MySQL Data to Google Sheets on a Schedule

The simplest way to sync MySQL to Google Sheets on a schedule is Google Apps Script. A script inside the spreadsheet opens a connection to the database, runs a query, writes the result to a sheet, and repeats automatically every hour or every morning with a time-driven trigger. If the database can't be opened to the internet, reverse the direction: the server sends the data to Sheets.

The situation usually looks like this. A point-of-sale app, an order system, or a website stores its data in MySQL. The owner and the finance team don't want to open phpMyAdmin; they want numbers they can filter and chart straight away in Google Sheets. Until now, someone exports a CSV by hand every day. That's what we want to get rid of.

Pick the direction first

For most small and mid-sized businesses, the first approach is the fastest to get running. The second is safer, but needs server access and a bit more technical work.

Approach one, Apps Script with JDBC

According to the Apps Script documentation, the JDBC service can connect to MySQL, Google Cloud SQL, SQL Server, Oracle, and PostgreSQL. Three requirements are worth noting:

• Google's IP ranges must be allowed in the database firewall, using the list linked from that documentation.
• The database port must be 1025 or higher. MySQL's default port, 3306, qualifies.
• The connection needs at least TLS 1.2, because TLS 1.0 and 1.1 are disabled.

Here's an example script that copies the last 30 days of orders into a tab named Data. The first row of that tab holds the column headers.

// Store credentials in Project Settings > Script properties, not in the code
function syncMysqlToSheet() {
  const props = PropertiesService.getScriptProperties();
  const conn = Jdbc.getConnection(
    props.getProperty('DB_URL'), // e.g. jdbc:mysql://HOST:3306/DB_NAME
    props.getProperty('DB_USER'),
    props.getProperty('DB_PASS')
  );
  const stmt = conn.createStatement();
  const rs = stmt.executeQuery(
    'SELECT id, order_date, customer, total FROM orders ' +
    'WHERE order_date >= CURDATE() - INTERVAL 30 DAY'
  );

  const columnCount = rs.getMetaData().getColumnCount();
  const rows = [];
  while (rs.next()) {
    const row = [];
    for (let i = 1; i <= columnCount; i++) row.push(rs.getString(i));
    rows.push(row);
  }
  rs.close();
  stmt.close();
  conn.close();

  const sheet = SpreadsheetApp.getActive().getSheetByName('Data');
  const oldRows = Math.max(sheet.getLastRow() - 1, 1);
  sheet.getRange(2, 1, oldRows, columnCount).clearContent();
  if (rows.length) sheet.getRange(2, 1, rows.length, columnCount).setValues(rows);
}

// Run once to set up an hourly schedule
function setUpSchedule() {
  ScriptApp.newTrigger('syncMysqlToSheet').timeBased().everyHours(1).create();
}

The steps:

1. Open the spreadsheet, choose Extensions > Apps Script, and paste the script above.
2. Fill in DB_URL, DB_USER, and DB_PASS under Project Settings > Script properties.
3. Change the query to match your tables, then run syncMysqlToSheet once to check the result and grant permissions.
4. Run setUpSchedule once. From then on, the sync runs by itself every hour.

The script overwrites the old contents each time it runs, so the Data tab always holds the latest copy. If you need history, change the last part so it appends new rows instead of overwriting. The values are written as text, so number columns may need reformatting in Sheets before you use them in formulas.

Limits to know about

The Apps Script quotas that matter most here:

An hourly sync that finishes in a minute uses only about 24 minutes a day, so it's fine even on a regular Gmail account. The thing to watch is a query that pulls too many rows. Fetch only the columns and date range you need, because spreadsheets also have a cell limit. I cover that limit, and when Sheets stops being enough, in When Google Sheets Is Enough as a Web App Database, and When to Move On.

Security checks to do first

Opening the database to Google's IP ranges means it can be reached from the internet. Here's what I'd suggest before switching the sync on:

• Create a dedicated read-only MySQL user, limited to the tables or views you need. Don't use root or the application's own user.
• Use a view to hide sensitive columns, such as customer phone numbers or addresses, if the team doesn't need them.
• Limit firewall access to Google's IP ranges only, rather than opening it to every address.
• Keep credentials in Script properties, and limit who can edit the script. Anyone who can edit the script can read the credentials.
• Control who can view the spreadsheet. Once data is copied to Sheets, it follows that file's sharing settings. If the Google account that owns it is compromised, the data is exposed too, as I cover in If Your Company's Google Account Got Hacked Tomorrow Morning, What Would You Lose?.

If you can't meet any one of these, go with the second approach: the server pushes the data to Sheets, and the database stays closed.

When this sync isn't enough

A scheduled copy works well for reports and monitoring. But if the team starts editing data in Sheets and wants those changes to flow back into MySQL, that's two-way sync, and the risk is much higher. At that point it usually makes more sense to build a small input page that writes straight to the database, or a dashboard that reads the database directly. If the goal is a routine report, see also Your Team Stays Up Every Month-End Compiling Reports: When Is It Worth Automating?.

Wrapping up

For reports refreshed every hour or every morning, Apps Script with JDBC is enough and can be up and running in an afternoon. What makes it safe or not isn't the script, but the database user, the firewall, and who can open the spreadsheet.

If you'd like data from your system to land in Google Sheets or a dashboard automatically, with no more manual exports, tell me which system you use.

The photos in this article are from Unsplash, by Domaintechnik (main photo) and Carlos Muza, used under the Unsplash License.