Dynamic date range from cells

In the Date list of a query, you can select Custom Range (Cell reference). The query then reads its start and end dates from cells in your spreadsheet. Change the dates in the cells, and the next run uses them. You do not have to edit the query.

Example setup

In this example:

• Start date is in cell F6 (11/17/2021)
• End date is in cell H6 (12/15/2021)
• Both cells are on the Facebook Report sheet tab

With Custom Range (Cell reference), each run of the query reads the current values in those cells and uses them as the date range.

Type the cell references

After you select Custom Range (Cell reference) in Date, type the references in:

• Start date cell
• End date cell

Use this format:

• Left side of ! = sheet tab name
• Right side of ! = cell address

If your sheet tab is Facebook Report:

• Start date in F6 -> 'Facebook Report'!F6
• End date in H6 -> 'Facebook Report'!H6

Pick the cell instead

You do not have to type the references. Use the picker icon next to each field (its tooltip is Auto detect from current selection):

1. Select the cell that contains the date.
2. Select the picker icon next to the field.
3. Wait about 3 seconds. The field fills in the reference.

Why this is useful

You change the date range of a query from cells in your spreadsheet. This helps with recurring reports and templates, where the dates change often.

Related guides

• Schedule refreshes
• Manage your spreadsheets
• Google Sheets add-on overview

If you have questions, email [email protected].