如何在Google表格侧边栏展示SQL查询MySQL数据库得到的用户资料？

Alright, let's walk through how to build exactly what you're asking for: a Google Sheets add-on that connects to a MySQL database for fuzzy user searches and displays results in a sidebar. Here's a step-by-step breakdown with code examples:

First, you'll need a table to store user data. Let's create a basic users table with common fields:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20)
);

• Key Note: To let Google Apps Script connect to your MySQL instance, use either Google Cloud SQL (recommended for easy authentication and security) or configure your self-hosted MySQL server to allow external connections (always use SSL and strong credentials!).

We'll split this into two parts: the frontend sidebar UI and the backend Apps Script logic that talks to MySQL.

2.1 Build the Sidebar UI #

Create an HTML file (name it Sidebar.html) for the sidebar with a search input, button, and results area:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
      .sidebar-container { padding: 1rem; }
      #search-input { width: 100%; padding: 0.5rem; margin-bottom: 1rem; border: 1px solid #ddd; border-radius: 4px; }
      #search-btn { background-color: #1a73e8; color: white; border: none; padding: 0.5rem 1rem; border-radius: 4px; cursor: pointer; }
      #results-area { margin-top: 1.5rem; }
      .user-card { border: 1px solid #eee; padding: 0.8rem; margin-bottom: 0.8rem; border-radius: 4px; }
      .user-name { font-weight: bold; margin: 0 0 0.3rem 0; }
    </style>
  </head>
  <body>
    <div class="sidebar-container">
      <input type="text" id="search-input" placeholder="输入名字关键词搜索（比如J）">
      <button id="search-btn">搜索用户</button>
      <div id="results-area"></div>
    </div>

    <script>
      // Handle search button click
      document.getElementById('search-btn').addEventListener('click', () => {
        const keyword = document.getElementById('search-input').value.trim();
        const resultsArea = document.getElementById('results-area');
        
        if (!keyword) {
          resultsArea.innerHTML = '<p>请输入搜索关键词</p>';
          return;
        }

        // Call backend Apps Script function
        google.script.run
          .withSuccessHandler(renderResults)
          .searchUsers(keyword);
      });

      // Render search results in the sidebar
      function renderResults(users) {
        const resultsArea = document.getElementById('results-area');
        
        if (users.length === 0) {
          resultsArea.innerHTML = '<p>未找到匹配的用户</p>';
          return;
        }

        let resultsHtml = '';
        users.forEach(user => {
          resultsHtml += `
            <div class="user-card">
              <h4 class="user-name">${user.name}</h4>
              <p>邮箱: ${user.email || '无'}</p>
              <p>电话: ${user.phone || '无'}</p>
            </div>
          `;
        });
        
        resultsArea.innerHTML = resultsHtml;
      }
    </script>
  </body>
</html>

2.2 Backend Apps Script Logic #

Now create the main .gs script file to handle database connections and search logic:

// Show the sidebar when the user opens the sheet
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('用户资料查询')
    .addItem('打开查询侧边栏', 'showSidebar')
    .addToUi();
}

// Load the sidebar HTML
function showSidebar() {
  const sidebarHtml = HtmlService.createHtmlOutputFromFile('Sidebar')
    .setTitle('用户资料搜索');
  SpreadsheetApp.getUi().showSidebar(sidebarHtml);
}

// Search users in MySQL with fuzzy matching
function searchUsers(keyword) {
  // Retrieve database credentials securely (use PropertiesService instead of hardcoding!)
  const scriptProps = PropertiesService.getScriptProperties();
  const dbUrl = scriptProps.getProperty('DB_URL');
  const dbUser = scriptProps.getProperty('DB_USER');
  const dbPass = scriptProps.getProperty('DB_PASS');

  let users = [];

  try {
    // Connect to MySQL
    const conn = Jdbc.getConnection(dbUrl, dbUser, dbPass);
    
    // Use prepared statement to prevent SQL injection
    const query = `SELECT name, email, phone FROM users WHERE name LIKE ?`;
    const preparedStmt = conn.prepareStatement(query);
    preparedStmt.setString(1, `%${keyword}%`); // Fuzzy match: any position containing the keyword
    
    const resultSet = preparedStmt.executeQuery();

    // Parse results into an array of user objects
    while (resultSet.next()) {
      users.push({
        name: resultSet.getString('name'),
        email: resultSet.getString('email'),
        phone: resultSet.getString('phone')
      });
    }

    // Clean up resources
    resultSet.close();
    preparedStmt.close();
    conn.close();

  } catch (error) {
    console.error('Database query error:', error);
    // Return empty array on error to avoid breaking the sidebar
    return [];
  }

  return users;
}

// Run this once to store your database credentials (then comment it out!)
function saveDbCredentials() {
  const scriptProps = PropertiesService.getScriptProperties();
  scriptProps.setProperties({
    DB_URL: 'jdbc:mysql://YOUR_MYSQL_HOST:3306/YOUR_DB_NAME?useSSL=true',
    DB_USER: 'YOUR_DB_USERNAME',
    DB_PASS: 'YOUR_DB_PASSWORD'
  });
}

• Never hardcode credentials: Use PropertiesService to store sensitive data like database URLs, usernames, and passwords (run saveDbCredentials() once, then comment it out).
• Prevent SQL injection: Always use prepared statements (PreparedStatement) instead of concatenating SQL strings—this stops malicious users from injecting harmful SQL.
• Restrict database access: If using Cloud SQL, configure IAM permissions to only allow your Apps Script project to connect. For self-hosted MySQL, whitelist Google's IP ranges and enforce SSL.

1. In the Apps Script editor, run onOpen() to add the menu to your Google Sheet.
2. Open the sheet, click the new "用户资料查询" menu, and select "打开查询侧边栏".
3. Test fuzzy search: type "J" and you should see all users with names containing "J" (Jack, John, Jules, etc.).
4. To share the add-on with others, click Publish > Deploy as add-on and follow the prompts to set up versions and permissions.

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