Get Firebase data using SQL in Google Sheets

Getting data from Cloud Firebase FireStore is a tricky job. You need to write complex queries and parse through nested JSONs, to look at your Data. Our FireStore for Google Sheets connector will allow you to get your Cloud Firestore Firebase Data into Google Sheets in a nice format in no time. We have recently released an SQL query method for getting your FireStore data into Google Sheets.

Using this method you can directly query your Cloud FireStore Firebase database using your familiar SQL syntax. You don’t need to learn any new query or programming language.

Writing an SQL Query

If you are familiar with SQL, then it should easy to write your queries for Firebase too. For example you can select an entire Firestore Collection using the same way you would query a Table.

Lets say your Collection for Users looks like this

[{
  Name: "Bill Gates",
  details: {
    isActive: true,
    isPaidUser: false
  }
  City : "New York"
}.
{
  Name: "Steve Jobs",
  details: {
    isActive: false,
    isPaidUser: true
  }
  City : "Boston"
}
]

You can query this collection using this query

SELECT * from users

Which will give you the entire users Table in Google Sheets like this

If you would like to filter within nested objects and get only results where isActive is true you can write this query

SELECT * from users where `users.details.isActive` = true

which will give you a result like this

Examples

Here are some example queries that are supported in our SQL queries for Cloud Firebase FireStore

SELECT *
FROM users

SELECT name, price
FROM orders
WHERE `address.city` = 'New York'

SELECT *
FROM beverages
WHERE type = 'non-alcohol' AND price < 500 AND currency= "USD"

SELECT *
FROM people
WHERE name LIKE 'Dona%' OR name LIKE 'Tru%' 

SELECT *
FROM orders
WHERE `address.city` IN ( 'New York', 'Chennai', 'London' )

SELECT *
FROM orders
WHERE `address.city` != 'Tokyo'

SELECT *
FROM orders
WHERE refund = true

SELECT *
FROM orders
WHERE status IS NULL

SELECT AVG(price) AS averagePriceInNYC
FROM orders
WHERE `address.city` = 'New York'

SELECT type, MIN(price), AVG(price), MAX(price)
FROM beverages
WHERE productTitle LIKE 'Wine%'
GROUP BY type

SELECT *
FROM orders
WHERE `address.city` = 'San  Francisco' AND ( price < 56 OR deliveryRating > 6 )
ORDER BY price DESC, deliveryRating

SELECT *
FROM orders
WHERE price BETWEEN 240 AND 300
ORDER BY `address.city`, price
LIMIT 10

SELECT *
FROM orders
WHERE `address.city` = 'Los Angeles'
UNION
SELECT *
FROM orders
WHERE price > 100

Getting the document IDs

You can use the special field __name__ ro refer refers to the Document ID, the unique key that identifies each document in a collection. For convenience, you can also alias it like below

SELECT __name__ AS docId, price, city
FROM orders

When querying it’s also possible to use the document as a search field by using __name__ directly.

For example, you could search for all the documents whose IDs start with TMR using the following query

SELECT *
FROM cities
WHERE __name__ LIKE 'TMR%'

Collection group queries

You can easily do collection group queries in Firebase with SQL with Two Minute Reports!

You need to add the keyword GROUP to select collection group. This following query will get all documents from any collection or subcollection named “orders”.

SELECT *
FROM GROUP landmarks

For more details about Collection Group Queries read the official Firestore documentation about the same

Array membership queries

You can easily do array membership queries in Firebase using SQL with Two Minute Reports!

You need to add the CONTAINS condition in a where clause like below

SELECT *
FROM orders
WHERE tags CONTAINS 'interesting'

For more details about Collection Group Queries read the official Firestore documentation about the same

Credits :
The SQL engine for Firebase is powered by an open source project here