=SQL()

Documentation for =SQL() Custom Formula for Google Sheets

The =SQL() custom formula allows you to run SQL queries directly in Google Sheets, pulling data from MySQL, SQL Server, or Oracle databases. This can be extremely useful for retrieving, updating, or analyzing your database data directly within your spreadsheet.

How to Use the =SQL() Custom Formula

The =SQL() formula has the following structure:

=SQL(dbType, host, port, dbName, userName, userPwd, query)

Let's break down what each of these parameters means:

1. dbType: The type of the database. Currently, we support mysql, sqlserver (for Microsoft SQL Server), and oracle (for Oracle database).

2. host: The host of the database. This is typically a URL or an IP address.

3. port: The port number for the database. Usually, it's 3306.

4. dbName: The name of the database.

5. userName: The username for database authentication.

6. userPwd: The password for database authentication.

7. query: The SQL query to interact with the database.

Examples

Example 1: Simple SELECT Query

Let's say you want to pull all data from a table named employees in your MySQL database. You can use the SELECT query:

=SQL("mysql", "example.rd2.amazonaws.com", 3306, "database name", "username", "user password", "SELECT * from employees")

This will pull in all the data from the employees table in your MySQL database into your Google Sheet.

Example 2: SELECT Query with WHERE Clause

If you want to pull data from a specific row, you can include a WHERE clause in your query:

=SQL("mysql", "example.rd2.amazonaws.com", 3306, "database name", "username", "user password", "SELECT * from employees WHERE id = 1")

This will pull in the data from the row where id is 1 in the employees table.

Remember, the =SQL() formula is a powerful tool that can greatly enhance your Google Sheets' capabilities. However, always ensure you have the necessary permissions to use the database, and be careful not to share sensitive information like usernames and passwords.