Connect Google Sheets to MySQL: 5 Reliable Methods

You don’t connect Google Sheets to MySQL just because it’s neat. You do it because finance needs a daily bookings roll-up, marketing wants campaign ROAS by SKU, ops teams are fixing SKUs and prices in bulk, or sales is editing territories without opening phpMyAdmin. The job is predictable: get data in/out, safely, on schedule, without a weekend fire drill. Here’s a practical, operator’s guide to wire Google Sheets and MySQL the right way - five reliable patterns, trade-offs, and copy-paste snippets so you can ship this in days, not months.

Before you touch a line of code or pay for a connector, answer two questions: what direction is the data flowing, and how fresh does it need to be? Those two decide 80% of your architecture and cost.

Direction first. Read-only (MySQL to Sheets) is easiest: reporting, QA lists, one-time exports, or analyst sandboxes. Write-back (Sheets to MySQL) is where people get burned - duplicates, partial writes, missing keys, and security holes. Two-way sync is the hardest: you need clear ownership of fields, conflict resolution, and idempotent jobs.

Latency next. If “daily by 7am” is good enough, your options are wide. If you need updates every 5 minutes, you’ll likely use Apps Script triggers, an automation tool, or a small service on Cloud Run. True real-time (seconds) isn’t a Sheets thing; Sheets is a workbook, not a streaming app.

If your database runs on Google Cloud SQL for MySQL, Google Apps Script can connect directly using a special JDBC URL. This is the least moving parts approach for pulling data into a Sheet, and it works well for nightly or hourly jobs with moderate result sets (thousands, not millions, of rows).

Why Cloud SQL? Security and connectivity. Apps Script can’t reliably connect to random on-prem MySQL without brittle IP allowlists or tunnels. With Cloud SQL, authentication and networking are native, and you avoid exposing MySQL to the public internet.

High-level steps: create a dedicated read-only MySQL user, write an Apps Script to run your SELECT, paste values into the Sheet, and schedule a time-driven trigger. Keep secrets in Script Properties, not hard-coded.

Step-by-step: Apps Script read from Cloud SQL

1) In your Cloud SQL instance (MySQL), create a database user with SELECT only on the target tables. 2) In Sheets, Extensions → Apps Script. 3) In Script Properties, store DB name, user, and password. 4) Paste the code below and adjust project/region/instance.

function loadMySQLToSheet() {
  const props = PropertiesService.getScriptProperties();
  const project = 'YOUR_GCP_PROJECT';
  const region = 'YOUR_INSTANCE_REGION'; // e.g., us-central1
  const instance = 'YOUR_INSTANCE_NAME';
  const dbName = props.getProperty('DB_NAME');
  const user = props.getProperty('DB_USER');
  const pass = props.getProperty('DB_PASS');
  const url = 'jdbc:google:mysql://' + project + ':' + region + ':' + instance + '/' + dbName;

  const conn = Jdbc.getCloudSqlConnection(url, user, pass);
  const stmt = conn.prepareStatement(
    'SELECT id, sku, price, updated_at FROM products WHERE updated_at >= DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY updated_at DESC LIMIT 5000'
  );
  const rs = stmt.executeQuery();

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('mysql_products') ||
                SpreadsheetApp.getActiveSpreadsheet().insertSheet('mysql_products');
  sheet.clearContents();

  const headers = ['id', 'sku', 'price', 'updated_at'];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  const rows = [];
  while (rs.next()) {
    rows.push([rs.getLong(1), rs.getString(2), rs.getBigDecimal(3), rs.getTimestamp(4)]);
  }
  if (rows.length) sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);

  rs.close(); stmt.close(); conn.close();
}

Trigger it via Triggers → Add Trigger → Time-driven (e.g., every hour). Keep dataset sizes reasonable; Sheets tops out around 10 million cells, but your users will tap out much sooner. If you routinely need 200K+ rows, skip to the BigQuery method below.

Pros, cons, and when to pick this

Pros: fewest components, native to Google, easy scheduling, no extra bills beyond Cloud SQL and Sheets. Cons: limited to Cloud SQL (or trickier setups), and Apps Script quotas can bite you if you run too frequently or process huge datasets. Pick this when you own the database stack and nightly/hourly reads are fine.

Security note: never give more than SELECT. Add a view in MySQL that pre-filters the data you need so a compromised Sheet can’t wander into customer PII or sensitive tables.

Cost: Cloud SQL instance cost + your time. Good ROI if the data lives in GCP already.

If your MySQL runs on-prem or another cloud and you can’t - or don’t want to - open it to the public internet, put a tiny web service between Sheets and MySQL. Host it on Cloud Run or Cloud Functions, talk to your database over a VPN/Private Service Connect or SSH tunnel, and expose a single HTTPS endpoint to Apps Script. The service does the SQL work; the Sheet just fetches JSON.

This pattern scales to complex logic (joins, filters, pagination), gives you proper observability, and keeps secrets out of the Sheet. It also handles write-backs more safely because you can validate inputs server-side before touching MySQL.

Architecture: Apps Script → UrlFetchApp → HTTPS endpoint (Cloud Run) → MySQL (private). Add authentication (signed token) so random calls can’t hit your endpoint.

Example: Node.js on Cloud Run + Apps Script fetch

Minimal Node.js service:

// index.js (Node 18)
import express from 'express';
import mysql from 'mysql2/promise';

const app = express();
app.use(express.json());

app.get('/products', async (req, res) => {
  try {
    const token = req.header('x-api-key');
    if (token !== process.env.API_KEY) return res.status(401).json({error: 'unauthorized'});

    const conn = await mysql.createConnection({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASS,
      database: process.env.DB_NAME,
      ssl: { rejectUnauthorized: true }
    });
    const [rows] = await conn.execute(
      'SELECT id, sku, price, updated_at FROM products ORDER BY updated_at DESC LIMIT 5000'
    );
    await conn.end();
    res.json(rows);
  } catch (e) {
    console.error(e);
    res.status(500).json({error: 'server_error'});
  }
});

app.post('/products/upsert', async (req, res) => {
  try {
    const token = req.header('x-api-key');
    if (token !== process.env.API_KEY) return res.status(401).json({error: 'unauthorized'});

    const items = req.body.items || [];
    const conn = await mysql.createConnection({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASS,
      database: process.env.DB_NAME,
      ssl: { rejectUnauthorized: true }
    });
    const sql = 'INSERT INTO products (id, sku, price) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE sku=VALUES(sku), price=VALUES(price)';
    for (const it of items) {
      await conn.execute(sql, [it.id, it.sku, it.price]);
    }
    await conn.end();
    res.json({ok: true, count: items.length});
  } catch (e) {
    console.error(e);
    res.status(500).json({error: 'server_error'});
  }
});

app.listen(process.env.PORT || 8080);

Apps Script to read:

function pullFromApi() {
  const url = 'https://YOUR_CLOUD_RUN_URL/products';
  const apiKey = PropertiesService.getScriptProperties().getProperty('API_KEY');
  const res = UrlFetchApp.fetch(url, {
    method: 'get',
    headers: { 'x-api-key': apiKey },
    muteHttpExceptions: true
  });
  if (res.getResponseCode() !== 200) throw new Error(res.getContentText());
  const data = JSON.parse(res.getContentText());

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('api_products') ||
                SpreadsheetApp.getActiveSpreadsheet().insertSheet('api_products');
  sheet.clearContents();
  const headers = Object.keys(data[0] || {});
  if (!headers.length) return;
  const rows = data.map(obj => headers.map(h => obj[h]));
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
}

This approach is durable. You can add caching, pagination, and field-level guards over time, without teaching everyone SQL or handing out database credentials.

Pros, cons, and when to pick this

Pros: secure, works with any MySQL, production-grade logging and auth. Cons: requires a small service and dev time. Pick this when security matters, you need write-back, or you can’t use Cloud SQL.

Tip: protect the service with a signed JWT or API key in a private Secret Manager; rotate it quarterly.

Cost: pennies per 100K requests on Cloud Run in most orgs; you pay with engineering time up front.

If your team prefers drag-and-drop over code, tools like Make (Integromat), n8n, and Zapier can do dependable row-by-row syncs. They have native MySQL and Google Sheets modules. You can poll MySQL changes, add/update rows in Sheets, or watch for row edits in Sheets and push to MySQL.

These flows are easy to start and easy to abuse. Keep payloads small, design idempotent steps (run twice, same result), and enforce a unique key so you don’t create duplicates. Expect to manage rate limits - Sheets and MySQL modules often write row-by-row.

Typical patterns: hourly export of new orders to Sheets; write price corrections from a Sheet to MySQL with “INSERT ... ON DUPLICATE KEY UPDATE”; send error logs to email or Slack when a run fails.

Make/n8n blueprint outline

- Trigger: Cron every 15 minutes. - MySQL: Select rows WHERE updated_at > last_run_at. - Google Sheets: Upsert by key (via Find + Update in Sheets or a custom function). - Store last_run_at in a data store to avoid reprocessing. - Slack/Email step on errors.

When should you pick this? Non-critical ops, fast time-to-value, or when product/ops teams want ownership without engineering. For mission-critical, high-volume syncs, move to a proper service (Method 2) or ELT (Method 4).

Security: store MySQL credentials in the tool’s encrypted vault. Restrict permissions to minimum needed tables/actions.

Sheets chokes at scale. When you routinely need 100K+ rows, do not shovel them into Sheets. Land MySQL into BigQuery using an ELT tool (Airbyte, Fivetran, Stitch, Hevo) or your own batch job, then point Google Connected Sheets at BigQuery. Users keep the familiar spreadsheet UI; the heavy lifting happens in the warehouse.

Connected Sheets gives pivot tables, formulas, and charts on top of BigQuery tables without downloading the data into a Sheet. You can schedule refreshes, control access via BigQuery permissions, and avoid the performance tax of 400K embedded cells.

Flow: ELT job replicates MySQL → BigQuery every hour. Analysts use Connected Sheets to build dashboards and ad hoc views. Write-back, if needed, happens via a small service (Method 2) because Connected Sheets is read-focused.

Pros, cons, and when to pick this

Pros: scale, security, governed access, cheap storage/compute. Cons: additional moving parts and cost; write-back requires a separate path. Pick this when your teams want Sheets-like analysis on big data and you already invest in a warehouse.

Cost: ELT tool fee (or engineering time if DIY) + BigQuery (usually inexpensive for moderate use) + minimal Sheets overhead.

Extra: You can materialize pre-joined, pre-aggregated views in BigQuery so every Connected Sheet is fast and consistent across teams.

Sometimes you don’t need a system. You need a CSV by Friday. In that case: export from MySQL (CLI mysqldump --tab, MySQL Workbench, or a simple SELECT INTO OUTFILE), then import into Sheets (File → Import → Upload). It’s not pretty, but it’s defensible for audits and one-time projects.

For monthly jobs, script it: a cron job runs a SELECT to CSV and drops it into Google Cloud Storage. A small Apps Script or a Cloud Function then pulls the latest file into a Sheet on schedule. It’s uglier than an API but cheaper to stand up than a full integration.

Risk: stale data and manual steps creep back in. If the export becomes “important,” upgrade to one of the methods above.

Write-back is where projects succeed or crash. Don’t let anyone with edit rights hammer your primary database. Control input, validate, and be explicit about ownership: which fields are mastered in Sheets vs. in the app?

Set a unique key in Sheets - ideally the MySQL primary key (id) or a natural key (sku). No key, no write. Use a dedicated service account for write-backs with only INSERT and UPDATE rights on allowed tables/columns. Reject deletes from Sheets unless you truly mean it.

Implement upsert logic on the server, not in the Sheet. MySQL gives you a safe default:

INSERT INTO products (id, sku, price)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE
  sku = VALUES(sku),
  price = VALUES(price);

In Apps Script, batch 100–500 rows per request to your service endpoint to avoid timeouts. Validate types (numbers as numbers, dates as ISO strings). If a row fails, log it in a separate tab with the error and do not proceed silently.

A good integration is boring. Boring means monitored. For Apps Script, add a simple logger: write start/end times, row counts, and success/failure into a "runs_log" tab after each job. Use time-driven triggers for predictable schedules.

For Cloud Run/Functions, push logs to Cloud Logging and set alerting policies on error rates or latency. Add a health endpoint ("/healthz") and schedule a checker (Cloud Scheduler cURL) to catch cold-start or auth issues early.

In automation tools, send summarized run results to Slack or email. Don’t page the team for transient 429s; do page for consecutive failures beyond a threshold (e.g., 3 in a row).

Keys. Pick one. If the source doesn’t have a durable unique key, create one (e.g., concat of org_id + external_id). Without it, you can’t upsert safely, and “compare row-by-row” becomes guesswork.

Dedupe. Before you paste into Sheets, dedupe in SQL using ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) in MySQL 8 or GROUP BY + MAX(updated_at) in older versions. Cleaner inputs mean fewer Sheet-side headaches.

Schema drift. Someone adds a column in MySQL and your Apps Script breaks because headers changed. Make your scripts resilient: read headers dynamically when possible, or lock the schema by selecting explicit columns. Add a canary test that compares expected headers to what you pulled and throws a clear error.

Principle of least privilege. Separate read-only and write roles; scope them to specific tables. Rotate passwords or keys quarterly. Never hard-code secrets; use Script Properties or Secret Manager.

Connectivity. Avoid exposing MySQL to the public web. Prefer private networking, Cloud SQL, or a proxy service you control. If you must expose a port, use SSL, strict IP allowlists, and fail2ban. Better: keep MySQL private and expose a web API instead (Method 2).

Auditing. Log who changed what. For write-backs, include a user identifier from the Sheet (editor email) in the payload and store it in an audit table. You’ll thank yourself during the first dispute.

If you need a read-only, hourly pull from a Cloud SQL database, Apps Script is near-zero cost and quick. If your database is elsewhere or you need write-back, a tiny Cloud Run service is safer and more future-proof. If a non-technical ops lead owns the workflow and volume is modest, Make/n8n buys speed with acceptable trade-offs.

Costs compound when nobody owns the system. Document where secrets live, who can edit the Sheet, and what happens on failure. A two-page runbook saves the weekend. Treat your data flows like a product: version them, test them, and review them quarterly.

Integrations shouldn’t require a 10-person agency retainer. One senior operator can plan, build, and run this end-to-end - database roles, Apps Script, Cloud Run, and dashboarding - then hand you the runbook.

Anton Chisliuk · AI Marketing Ops runs on an operator model: one senior builder ships CRM, outbound, automation, and content systems without the account-manager layer. It’s not theory - on B2B SaaS Cleverence, we went from 0 to 80K organic visits/month and ~2K inbound leads/month by building the plumbing first, then scaling content and ops. If you want the full backstory and economics, read the team’s story here: about Anton Chisliuk · AI Marketing Ops. Leads, sales, content, and systems - one senior operator.

- Finance: daily revenue by channel. Pull orders and ad cost into BigQuery; expose via Connected Sheets for CFO review, with a drill-down tab for anomalies.

- Marketing ops: campaign UTM QA. Read last 7 days of sessions and orders; join against a UTM dictionary maintained in Sheets; write corrected UTM mappings back to MySQL for clean reporting downstream.

- Sales ops: territory fixes. Sales leadership edits a small Sheet of rep-to-zip assignments; a Cloud Run service validates rows and writes to MySQL once per day, logging changes by user email.

1) Lock the tab. Protect formula columns; allow edits only in the input range. 2) Validate at the edge: Data → Data validation (numbers, lists, regex for SKUs). 3) Show the key: display the immutable ID in the first column so humans know what they’re editing.

4) Batch writes. A sidebar button triggers an Apps Script that collects only changed rows, posts them to your API, and prints a result (success/error) beside each row. 5) Guardrails: the API double-checks referential integrity (e.g., category_id exists) before writing.

Code sketch for the sidebar button:

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Sync').addItem('Push Changes', 'pushChanges').addToUi();
}

function pushChanges() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('edits');
  const range = sheet.getDataRange();
  const values = range.getValues();
  const headers = values.shift();

  const changed = values
    .map((row, i) => ({ i: i + 2, row }))
    .filter(r => r.row[headers.indexOf('dirty_flag')] === true)
    .map(r => ({
      _row: r.i,
      id: r.row[headers.indexOf('id')],
      sku: r.row[headers.indexOf('sku')],
      price: Number(r.row[headers.indexOf('price')])
    }));

  if (!changed.length) return SpreadsheetApp.getUi().alert('No changed rows.');

  const apiKey = PropertiesService.getScriptProperties().getProperty('API_KEY');
  const res = UrlFetchApp.fetch('https://YOUR_API/products/upsert', {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify({ items: changed }),
    headers: { 'x-api-key': apiKey },
    muteHttpExceptions: true
  });

  if (res.getResponseCode() !== 200) throw new Error(res.getContentText());
  SpreadsheetApp.getUi().alert('Synced ' + changed.length + ' rows.');
}

This keeps Sheets as the UI, not the database. Humans edit rows; your service decides what’s legal; MySQL stays healthy.

Your Sheets↔MySQL sync often feeds CRM routing, attribution, or product catalogs. If you need clean ownership, push data into CRM via its API and let the CRM own contact/company objects. If you’re modernizing pipeline or attribution while you wire this integration, this is a good moment to sanity check your CRM data model and permissions.

If you don’t have a clean place for revenue and pipeline definitions to live, fix that first. Clean integrations on top of dirty models just move the mess around. We routinely pair data plumbing work with a light CRM cleanup so everything downstream - ads, email, revenue reports - stops arguing with itself.

If that’s on your roadmap, here’s where teams usually start: a quick, scoped CRM implementation pass while the data layer is being built.

- Timeouts in Apps Script: limit result sets or paginate via your API. - Broken headers: pin explicit column lists in SQL. - Permission denied: confirm the MySQL user’s grants and SSL settings. - Quotas: batch writes/reads; avoid running every minute unless you must.

- Duplicates: enforce a UNIQUE index in MySQL and use upserts, not blind inserts. - Stale data: maintain a last_run checkpoint and pull only deltas. - Mixed types in Sheets: coerce types before sending to MySQL; reject nonsense early.

When in doubt, cut surface area: fewer columns, fewer moving parts, clearer owners. Most issues come from “we’ll just mirror the whole table into Sheets” decisions.

There isn’t one “right” way to connect Google Sheets and MySQL - there’s a right way for your latency, security, and ownership needs. If you’re on Cloud SQL and only need reads, Apps Script JDBC is simple and strong. If you need write-back or you can’t expose your DB, a tiny Cloud Run service gives you control and safety. For non-technical teams and modest volumes, Make/n8n works fast. For scale, go ELT to BigQuery and use Connected Sheets.

Pick the smallest viable pattern, give it an owner, and write a runbook. Your teams get the editability and speed of Sheets; your systems keep the integrity of a database. If you want a quick sanity check on your approach, book a free Growth Audit and we’ll map the fastest, safest path for your use case.

Article by Anton Chisliuk.

- Can I connect Google Sheets directly to a non–Cloud SQL MySQL with Apps Script?

Technically yes using standard JDBC, but it’s brittle and often insecure because Apps Script runs from Google infrastructure with no fixed IP to allowlist. The safer path is Cloud SQL (native) or a secure web service on Cloud Run that talks to your database privately.

- How many rows can I realistically sync into Sheets?

Sheets supports up to ~10 million cells, but usability drops well before that. For ongoing work, keep it under ~50–150K cells per tab. If you need 100K+ rows regularly, move the data to BigQuery and use Connected Sheets.

- What’s the easiest way to push edits from Sheets back to MySQL?

Don’t write directly from Sheets to MySQL. Post edited rows to a small API (Cloud Run) that validates and runs an upsert in MySQL. This lets you enforce business rules, log changes, and avoid SQL injection or broken data types.

- Can Make, n8n, or Zapier handle two-way syncs?

Yes for modest volumes and clear keys. You’ll typically poll MySQL for changes and update Sheets row-by-row, and watch Sheets for edits and upsert into MySQL. For higher volumes or stricter SLAs, build a small service instead.

- How do I secure credentials in Apps Script?

Use Script Properties (PropertiesService) for API keys and DB creds. Do not hard-code secrets. Limit access to the script, rotate credentials quarterly, and avoid sharing the spreadsheet widely if the script has elevated permissions.

- What’s the best way to avoid duplicates on write-back?

Use a UNIQUE index in MySQL on the key you consider canonical (id or sku). Write with INSERT ... ON DUPLICATE KEY UPDATE so replays are safe. In Sheets, show the key in column A so users don’t accidentally create a new row instead of editing the existing one.

- How do I schedule automatic refreshes?

In Apps Script, add a time-driven trigger (every hour/day). In Cloud Run, schedule a Cloud Scheduler job that pings your endpoint. In Make/n8n, use cron-like triggers. Always log row counts and last successful run to spot silent failures.

- Should I use Connected Sheets or import data directly into a tab?

If the dataset is large or many users need governed access, use Connected Sheets with BigQuery. If the dataset is small and tied to a specific workflow (e.g., a daily QA list), importing into a tab via Apps Script is fine.