How to Automatically Refresh MySQL Data in Google Sheets Every Hour (Without Babysitting a Script)

Google Apps Script can automatically pull MySQL data into Google Sheets on a schedule using JDBC and time-driven triggers. The setup is free and requires no third-party tools. The catch: Google's 6-minute execution limit, a massive IP whitelist requirement, and plaintext credential storage mean most teams hit a wall within a week of shipping it. This guide covers the exact setup, every failure mode, and when a managed connector makes more sense than maintaining the script yourself.

The Problem: MySQL Data Goes Stale

Data analysts and operations teams who live in Google Sheets often need fresh database data without waiting for a developer to run an export. The gap between "the database is updated every hour" and "our Sheets still shows last week's numbers" is where bad decisions get made.

"Our sales team has been working from a Google Sheet that I manually refresh every Monday morning. I finally snapped and tried to automate it, three days later I'm debugging firewall rules and I still don't have a working trigger." (r/googlesheets)

"I got Apps Script JDBC working. It runs fine when I test it manually. The trigger fails every single time with 'Exceeded maximum execution time.' The query takes 7 minutes. The limit is 6." (Stack Overflow, 4.2K views)

"We whitelisted all 60+ CIDR ranges from goog.txt. Our security team nearly revolted. Then Google added new ranges and our connection started failing intermittently. We had to do it again." (Hacker News thread on Sheets automation)

These three failure modes (setup complexity, execution timeouts, and infrastructure friction) explain why "automatically refresh MySQL data in Google Sheets" is one of the most-searched, least-solved data problems in the spreadsheet ecosystem. The solution exists. It just has more sharp edges than the tutorials mention.

Method 1: Apps Script JDBC with Time-Driven Triggers (Free)

Apps Script's JDBC service connects directly to MySQL databases over a standard JDBC driver. No middleware, no third-party account, just Google infrastructure talking to your database.

The Basic Connection

Open your Google Sheet, go to Extensions > Apps Script, and create a new script:

// Store credentials in Script Properties - never hardcode them.
// Go to Project Settings > Script Properties to set these values.
const SCRIPT_PROPS = PropertiesService.getScriptProperties();

function getMySQLConnection() {
  const dbUrl  = SCRIPT_PROPS.getProperty('DB_URL');
  const dbUser = SCRIPT_PROPS.getProperty('DB_USER');
  const dbPass = SCRIPT_PROPS.getProperty('DB_PASS');

  if (!dbUrl || !dbUser || !dbPass) {
    throw new Error('Missing database credentials in Script Properties.');
  }

  // Connection string format: jdbc:mysql://HOST:PORT/DATABASE
  return Jdbc.getConnection(dbUrl, dbUser, dbPass);
}

function refreshMySQLData() {
  const ss     = SpreadsheetApp.getActiveSpreadsheet();
  const sheet  = ss.getSheetByName('Data') || ss.insertSheet('Data');
  const conn   = getMySQLConnection();

  try {
    const stmt    = conn.createStatement();
    stmt.setMaxRows(10000);  // Protect against runaway result sets
    const results = stmt.executeQuery('SELECT * FROM your_table ORDER BY id DESC');
    const meta    = results.getMetaData();
    const numCols = meta.getColumnCount();

    // Write headers
    const headers = [];
    for (let i = 1; i <= numCols; i++) {
      headers.push(meta.getColumnName(i));
    }

    // Collect all rows
    const rows = [headers];
    while (results.next()) {
      const row = [];
      for (let i = 1; i <= numCols; i++) {
        row.push(results.getString(i));
      }
      rows.push(row);
    }

    // Write to sheet in one batch - much faster than row-by-row
    sheet.clearContents();
    sheet.getRange(1, 1, rows.length, numCols).setValues(rows);

    results.close();
    stmt.close();
  } finally {
    conn.close();  // Always close connections
  }
}

Set your Script Properties (Project Settings > Script Properties in the Apps Script editor):

Setting Up the Trigger

The trigger is what makes the refresh automatic. You can create it in the UI or in code. The code approach is more reproducible:

function createHourlyTrigger() {
  // Delete any existing triggers for this function first
  // to avoid stacking duplicates if you re-run setup
  const existingTriggers = ScriptApp.getProjectTriggers();
  existingTriggers.forEach(trigger => {
    if (trigger.getHandlerFunction() === 'refreshMySQLData') {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  // Create a new hourly trigger
  ScriptApp.newTrigger('refreshMySQLData')
    .timeBased()
    .everyHours(1)
    .create();

  Logger.log('Hourly trigger created successfully.');
}

Run createHourlyTrigger() once. From that point forward, refreshMySQLData will execute every hour automatically, even when you are not logged in, even on weekends.

To verify: go to the Triggers panel in the Apps Script editor (the clock icon in the left sidebar). Your trigger should appear there with its next scheduled run time.

Paginating Around the 6-Minute Limit

Apps Script enforces a hard 6-minute (360-second) execution limit per run. If your MySQL query + data writing takes longer than that, the script is killed mid-run and your sheet ends up with partial or no data.

The workaround is pagination: process a fixed number of rows per trigger execution and track your position in Script Properties.

const PAGE_SIZE = 2000;  // Tune this based on your query speed

function refreshMySQLDataPaginated() {
  const ss    = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Data') || ss.insertSheet('Data');
  const conn  = getMySQLConnection();
  const props = PropertiesService.getScriptProperties();

  // Get current offset - reset to 0 if this is the first page
  let offset = parseInt(props.getProperty('DB_OFFSET') || '0', 10);

  // On the first page, clear the sheet and write headers
  if (offset === 0) {
    sheet.clearContents();
  }

  try {
    const stmt = conn.prepareStatement(
      'SELECT * FROM your_table ORDER BY id LIMIT ? OFFSET ?'
    );
    stmt.setInt(1, PAGE_SIZE);
    stmt.setInt(2, offset);

    const results = stmt.executeQuery();
    const meta    = results.getMetaData();
    const numCols = meta.getColumnCount();

    // Write headers on the first page only
    if (offset === 0) {
      const headers = [];
      for (let i = 1; i <= numCols; i++) {
        headers.push(meta.getColumnName(i));
      }
      sheet.getRange(1, 1, 1, numCols).setValues([headers]);
    }

    const rows = [];
    while (results.next()) {
      const row = [];
      for (let i = 1; i <= numCols; i++) {
        row.push(results.getString(i));
      }
      rows.push(row);
    }

    if (rows.length > 0) {
      const startRow = offset + 2;  // +1 for header, +1 for 1-indexed
      sheet.getRange(startRow, 1, rows.length, numCols).setValues(rows);
    }

    results.close();
    stmt.close();

    // Advance or reset the offset
    if (rows.length === PAGE_SIZE) {
      props.setProperty('DB_OFFSET', String(offset + PAGE_SIZE));
    } else {
      // Fewer rows than PAGE_SIZE means we've reached the end
      props.setProperty('DB_OFFSET', '0');
      Logger.log('Full refresh complete. Total rows: ' + (offset + rows.length));
    }

  } finally {
    conn.close();
  }
}

Wire up the paginated version with a more frequent trigger (every 5–10 minutes) and it will work through large datasets across multiple executions. The tradeoff: your sheet shows partial data during the refresh cycle, which may be acceptable for most dashboards.

Method 2: IP Whitelisting, The Infrastructure Tax

This is where most teams discover the hidden cost of the free approach.

Apps Script does not run from a fixed IP address. It runs from Google's cloud infrastructure, which spans dozens of CIDR ranges listed at https://www.gstatic.com/ipranges/goog.txt. As of mid-2026, that list contains over 60 IPv4 CIDR blocks plus a dozen IPv6 ranges.

To allow Apps Script to reach your MySQL database, you must whitelist all of them: in your firewall, your AWS security group, your RDS inbound rules, your GCP Cloud SQL authorized networks, or wherever your database enforces IP-level access control.

A representative sample of what you are whitelisting:

8.8.4.0/24
8.34.208.0/20
23.236.48.0/20
34.64.0.0/11
34.96.0.0/12
35.184.0.0/13
35.192.0.0/14
64.233.160.0/19
66.102.0.0/20
74.125.0.0/16
104.154.0.0/15
104.196.0.0/14
130.211.0.0/16
142.250.0.0/15
172.217.0.0/16
209.85.128.0/17
216.58.192.0/19
... (and 45+ more, plus IPv6)

The problems this creates:

1. Security team friction. Authorizing 60+ CIDR blocks covering hundreds of millions of IPs is a hard sell to anyone who cares about network security. Many security teams refuse entirely, which ends the project.

1. The list changes. Google adds and removes ranges without a deprecation policy. Your connection starts failing intermittently because a new Apps Script server came up on an IP outside your whitelist. You find out when someone notices the sheet stopped updating.

1. AWS security groups have limits. Each AWS security group has a default limit of 60 inbound rules. Google's IP list exceeds that. You may need to request a limit increase or split across multiple security groups: for one automation.

1. IPv6 incompatibility. Many database hosts do not support IPv6 inbound rules. You whitelist the IPv4 ranges, but Apps Script might route through an IPv6 address that day, and the connection fails with no useful error.

If IP whitelisting is a blocker for your team, skip to Method 5.

Method 3: Credential Security in Script Properties

Script Properties are the standard way to store database credentials in Apps Script. The approach looks like this in your editor: Project Settings > Script Properties > Add Script Property.

const props = PropertiesService.getScriptProperties();
const dbUrl  = props.getProperty('DB_URL');
const dbUser = props.getProperty('DB_USER');
const dbPass = props.getProperty('DB_PASS');

This is better than hardcoding credentials in your source code. However, understand what Script Properties actually are:

• They are not encrypted at rest. Google stores them as plaintext key-value pairs. Anyone with editor access to the Apps Script project can read them.
• They are visible in execution logs if you accidentally log them. A stray Logger.log(props.getProperties()) call exposes all credentials in a log that any project editor can view.
• They travel with the script if it is copied. If someone duplicates the Apps Script project, they get your credentials.

For higher-security environments, consider:

• Creating a read-only MySQL user with access only to the specific tables the script needs. Compromised credentials on a read-only account cannot modify data.
• Using Cloud Secret Manager via the Apps Script UrlFetchApp to retrieve credentials at runtime rather than storing them in Script Properties.
• Rotating credentials periodically and updating Script Properties when you do.

The read-only user approach is the minimum viable improvement. It takes five minutes in MySQL:

CREATE USER 'sheets_reader'@'%' IDENTIFIED BY 'your-password';
GRANT SELECT ON your_database.your_table TO 'sheets_reader'@'%';
FLUSH PRIVILEGES;

Method 4: Handling Schema Changes Silently Breaking Your Sheet

Apps Script JDBC does not validate your query against the current database schema before running. When a developer renames a column, drops a table, or changes a data type, the script fails silently, or worse, succeeds with wrong data.

Common failure modes:

Column rename: Your query SELECT created_at FROM orders returns an error after a developer renames the column to created_date. The trigger fires, gets a JDBC error, logs it to the Apps Script execution log (which you may not be watching), and leaves the sheet unchanged. Your sheet now shows data from the last successful run (potentially hours or days old) with no indication it is stale.

Column added: A new column appears in the database. Your query uses SELECT *, so the new column appears in your sheet, but it shifts all the columns to the right. Every VLOOKUP, INDEX/MATCH, and reference formula downstream breaks.

Column removed: Your query explicitly names a dropped column. The script errors. The sheet goes stale silently.

Type change: A VARCHAR column becomes an INT. Numeric values come through fine but edge cases (nulls, empty strings) start erroring or writing as empty cells.

Defensive patterns:

function refreshWithSchemaGuard() {
  const conn        = getMySQLConnection();
  const targetSheet = 'Data';
  const query       = 'SELECT id, name, email, amount, created_at FROM orders WHERE created_at >= NOW() - INTERVAL 30 DAY';

  try {
    const stmt    = conn.createStatement();
    const results = stmt.executeQuery(query);
    // ... write to sheet
    
    // Write a "Last updated" timestamp so staleness is visible
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const metaSheet = ss.getSheetByName('_meta') || ss.insertSheet('_meta');
    metaSheet.getRange('A1').setValue('Last successful refresh');
    metaSheet.getRange('B1').setValue(new Date());

  } catch (e) {
    // On failure, notify rather than silently failing
    MailApp.sendEmail({
      to: Session.getActiveUser().getEmail(),
      subject: '[Sheets Refresh] MySQL sync failed',
      body: 'The MySQL refresh script failed with error: ' + e.message
    });
    throw e;
  } finally {
    conn.close();
  }
}

The timestamp pattern is the most important: add a "Last refreshed" cell to your sheet so users can see at a glance whether the data is current.

Method 5: Third-Party Connectors (Managed)

If the DIY approach has too many moving parts for your team, managed connectors handle the infrastructure layer for you.

What a managed connector handles that Apps Script does not:

• Static outbound IPs (whitelist one or two addresses, not 60+ CIDR ranges)
• Encrypted credential storage with proper secrets management
• Scheduling that does not depend on Apps Script execution limits
• Schema change detection and alerting
• Retry logic when the database is temporarily unavailable

Supermetrics. Primarily a marketing analytics connector. MySQL support is available but the product is oriented toward marketing data sources. Pricing starts high for non-marketing use cases.

Coefficient. Good Google Sheets integration with MySQL support. Solid scheduling. Requires a Coefficient account and installs as a Sheets add-on.

brooked.io. Purpose-built for connecting databases to Google Sheets. Handles MySQL scheduling, provides static IPs for whitelisting, encrypts credentials at rest, and detects schema changes. The free tier covers most individual and small-team use cases; paid plans add higher refresh frequencies and additional workbooks.

Comparison Table

Troubleshooting

"Failed to establish a database connection. Check connection string, username and password."

This is the most common and least informative error in Apps Script JDBC. Work through this checklist in order:

1. Confirm the connection string format: jdbc:mysql://HOST:PORT/DATABASE. Note jdbc:mysql://, not mysql://.
2. Confirm your database is listening on port 3306 (or whatever port you specified). The JDBC service cannot connect to ports below 1025.
3. Confirm you have whitelisted the IPs from https://www.gstatic.com/ipranges/goog.txt. Even one missing CIDR range can cause intermittent failures since Apps Script routes from different servers on different runs.
4. Test the credentials manually using a MySQL client from a machine with internet access.
5. If using AWS RDS, confirm the instance has "Public accessibility" set to Yes and the VPC security group allows inbound TCP on port 3306 from the Google IP ranges.

"Exceeded maximum execution time"

Your script ran for longer than 6 minutes. See the pagination section. Also consider:

• Adding a WHERE clause to limit rows (date ranges are the most common fix)
• Creating a database view that pre-aggregates data so the query returns fewer rows
• Running the refresh off-peak when the database is less loaded

"The trigger runs but the sheet does not update"

The trigger ran but the script errored. Go to Apps Script > Executions (the list icon in the left sidebar) and look at the most recent executions. The error message is there. Common cause: the sheet name changed, or Script Properties were cleared.

"Data appears in the sheet but some cells are empty"

Likely a NULL in your database. Apps Script renders NULL values as empty strings. If you need to distinguish NULL from empty string in your sheet, use conditional formatting or a helper column.

"TLS handshake failed" or SSL-related errors

Apps Script requires TLS 1.2 or higher. If your MySQL server is configured to allow TLS 1.0 or 1.1 and does not support 1.2+, the connection will fail. Update your MySQL TLS configuration, or add ?useSSL=false to the connection string for internal databases where encryption is not required.

The Bottom Line

Apps Script JDBC is a legitimate, production-viable way to automatically refresh MySQL data in Google Sheets. The code works. The triggers work. The cost is zero. The problems are operational: 60+ IP ranges to whitelist, a 6-minute execution ceiling that requires pagination for large datasets, unencrypted credential storage, and silent failures when your database schema changes.

If you have one developer who owns this automation and your database is cloud-hosted with a manageable IP whitelist policy, the Apps Script approach is worth building. Budget two to three hours for setup and debugging, add a "Last refreshed" timestamp to your sheet, and set up email alerts on script failure.

If your security team baulks at whitelisting Google's IP ranges, if you need sub-hourly refresh, if you have multiple sheets pulling from the same database, or if this automation needs to be reliable without active maintenance: a managed connector is the right call.

brooked.io is built for exactly this use case: connect MySQL to Google Sheets in under five minutes, schedule automatic refreshes, and let the infrastructure layer handle IPs, credentials, and schema changes. The free tier supports one connection with daily refresh: enough to validate whether the workflow meets your needs before committing to anything.

Troubleshooting quick reference

Frequently asked questions