The underlying data in a spreadsheet is rarely static, and you may expect your widgets to respond automatically as new columns or rows are added. This is where using a dynamic range can be helpful.

To set a dynamic range that automatically displays new data as new columns or rows are added, select an entire column or row, and your widget will display the data as it's added.

If you’re adding new data to the top of your spreadsheet, you'd select the first columns or rows that currently contain that data, and will continue to contain that data as more data columns or rows are added.

If new data points are added to the bottom of a spreadsheet to build a chart of the last 7 days, you’d need to update the cell selection each day. Sorting the raw data to be most recent at the top isn’t really an option because it interferes with adding new data, but you can use a formula to copy the data to a new location, sorted the right way. If your data looks like this:

All you have to do is use the sort() function. For example: =sort(A6:E,1,FALSE).

Some additional things to keep in mind:

If your sheet includes columns or rows for future dates for which the data has yet to be filled in, you’ll need to filter those out.

To prevent blanks, use the filter() function along with isblank(). For example: =sort(filter(A2:E,ISBLANK(B2:B) = FALSE),1,FALSE).

This will remove any columns or rows in your selection A2:E where the B column is blank, and then wrap that in a sort.

The example spreadsheet used for the use cases above is available to access here.