When retrieving data from Cloud Firestore / Firebase using Two Minute Reports, you might encounter situations where you need to make daily changes to your queries. Whether itās tweaking the WHERE clause in your fireSQL query or modifying the Structured query, manually editing queries can be time-consuming.
Fortunately, thereās a solution: Using cell references in your Structured query and FireSQL you can dynamically update your queries based on values from the Spreadsheetās cell value. Hereās how you can do it:
Steps
1. Connect to your data source and set up your query as usual. If you havenāt set up your data source yet, follow this guide.
2. Imagine you have a Structured Query like the one below, and you want to dynamically change the filter condition.
Original query:
![](https://twominutereports.com/wp-content/uploads/2023/11/Screenshot-2023-11-27-095811.png)
3. Instead of specifying the filter value in the query, you can place it in any of your Google Sheets and reference that cell in your SQL query.
![](https://twominutereports.com/wp-content/uploads/2023/11/Screenshot-from-2023-11-11-13-26-54.png)
The cell references should be in the format ā+++SHEET_NAME!CELL+++ā. This informs Two Minute Reports that thereās a cell reference in your query.
For this example, letās say your sheet name is āOrders Reportā and your cell is āB1ā. For this, your cell reference will be in the format ā+++āOrder Reportā!B1+++ā.
Modified query:
![](https://twominutereports.com/wp-content/uploads/2023/11/Screenshot-2023-11-27-094458.png)
Now, when the query runs, Two Minute Reports will get the status from cell āB1ā in the āOrders Reportā sheet and replace the cell reference in your SQL query.
4. You can do the same in FireSQL as follows:
Original query:
![](https://twominutereports.com/wp-content/uploads/2023/11/Screenshot-2023-11-27-100117.png)
Modified query:
![](https://twominutereports.com/wp-content/uploads/2023/11/Screenshot-2023-11-27-185323.png)
Important Notes:
- Be sure to enclose the cell reference with +++ on both sides to signal Two Minute Reports that itās a dynamic value.
- If your sheet name has spaces like āOrders Reportā, enclose it in single quotes (ā).
- You canāt specify a range of cells, such as ā+++āOrders Reportā!A2:A5+++ā.
By using this feature, you can dynamically adjust values in your SQL query, fetching specific rows with an āactiveā status or changing the sorting column from ācreated_atā to āupdated_atā.
We hope this feature and guide are helpful. If you have any more questions or need assistance, donāt hesitate to reach out by creating a support ticket here.