如何从Google Apps Script连接WordPress的MySQL数据库及解决‘ReferenceError: Lib is not defined’错误

Hey there! Let's tackle your problem step by step. First, the ReferenceError: Lib is not defined error pops up because you’re trying to use a non-existent Lib object—Google Apps Script has a built-in Jdbc service you can use directly for this purpose, no external libraries required.

1. Fix the Core Connection Error #

Here’s the corrected version of your connection function, with invalid references removed and best practices added:

var server = "your-mysql-server-ip";
var dbName = "your-database-name";
var username = "your-db-username";
var password = "your-db-password";
var port = 3306; // Default MySQL port, adjust if yours is different

function createConnection() {
  try {
    // Fixed typo: "jbdc" → "jdbc" in the URL format
    var url = "jdbc:mysql://" + server + ":" + port + "/" + dbName;
    // Use built-in Jdbc service directly (no Lib needed)
    var conn = Jdbc.getConnection(url, username, password);
    var stmt = conn.createStatement();
    
    // Updated table name to standard WordPress post table (adjust if your prefix differs)
    var rs = stmt.executeQuery("SELECT * FROM wp_posts LIMIT 100");
    
    // Log metadata to confirm connection works
    var metaData = rs.getMetaData();
    var numCols = metaData.getColumnCount();
    console.log("Fetched " + numCols + " columns from wp_posts");
    
    conn.close();
  } catch (e) {
    // Add error handling to debug connection failures
    console.error("Error connecting to database: " + e.toString());
  }
}

Key fixes here:

• Removed the invalid Lib.getConnection(Jdbc) call—replace it with Jdbc.getConnection() directly
• Fixed a typo in the JDBC URL: "jbdc" → "jdbc"
• Updated the table name to wp_posts (standard for WordPress; tweak if your database uses a custom prefix)
• Added try/catch blocks to catch and log errors more effectively

2. Extend to Write Results to a Google Sheet #

Since your goal is to save query results to a spreadsheet, here’s an expanded function that handles that:

function writeQueryResultsToSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var headers = [];
  var rows = [];

  try {
    var url = "jdbc:mysql://" + server + ":" + port + "/" + dbName;
    var conn = Jdbc.getConnection(url, username, password);
    var stmt = conn.createStatement();
    // Target specific columns for clarity (adjust as needed)
    var rs = stmt.executeQuery("SELECT ID, post_title, post_date FROM wp_posts LIMIT 100");
    
    // Extract column headers
    var metaData = rs.getMetaData();
    var numCols = metaData.getColumnCount();
    for (var i = 1; i <= numCols; i++) {
      headers.push(metaData.getColumnName(i));
    }
    
    // Extract row data
    while (rs.next()) {
      var row = [];
      for (var j = 1; j <= numCols; j++) {
        row.push(rs.getString(j));
      }
      rows.push(row);
    }
    
    // Write data to the sheet
    sheet.clearContents();
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
    sheet.getRange(2, 1, rows.length, numCols).setValues(rows);
    
    console.log("Successfully wrote " + rows.length + " rows to the sheet");
    conn.close();
  } catch (e) {
    console.error("Error: " + e.toString());
  }
}

3. Critical Pre-Requisite: Allow Google's IPs to Access Your Database #

Most WordPress hosting providers block external database connections by default. To let Google Apps Script connect:

• Navigate to your hosting provider’s database firewall settings
• Add these Google JDBC IP ranges to the allowed list:
  • 66.102.1.0/24
  • 66.102.2.0/24
  • 64.233.160.0/19
  • 64.233.192.0/19
  • 64.233.232.0/21
  • 64.233.239.0/24
  • 64.233.178.0/23
  • 72.14.192.0/18
  • 74.125.0.0/16
  • 209.85.128.0/17
  • 216.239.32.0/19
• Ensure your database user has permission to connect from these IPs (not just localhost)

4. Set Up Scheduled Execution #

To run the function automatically on a schedule:

1. In the Google Apps Script editor, click the clock icon (Triggers) in the left sidebar
2. Click "Add Trigger"
3. Configure the trigger:
   • Choose function: writeQueryResultsToSheet
   • Choose deployment: Head
   • Select event source: Time-driven
   • Pick your preferred schedule (e.g., daily, hourly)
4. Click "Save"

内容的提问来源于stack exchange，提问作者Michal