Create dynamic spreadsheet widgets

Learn how to create dynamic spreadsheet widgets that automatically display new data as it's added to your sheet.

June 11, 2025

Set a dynamic range

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.

Display data for specific time periods

Plot a rolling 7-day line chart

Data added to the top of the sheet

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.

Data added to the bottom of the sheet

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:

• 1 tells it to sort by the first column and FALSE to sort in descending rather than ascending order.
• Make sure to include the whole column in your selection, not just the data you have already. i.e. A2:A instead of A2:A10.
• To build your chart of rolling 7 days, select the top 7 rows of data in your newly sorted table.

Filter blank data out

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.

Example spreadsheet

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