Often when you’re using spreadsheets to power widgets your data isn’t fixed, with new columns or rows of data being added manually or via a script.
If your column or row selections include pre-populated cells your spreadsheet widgets will automatically respond as you add data to your connected spreadsheet. The following tutorial video explains how this works.
However, you might want to plot a chart of just the last week, or 30 days of data.
Plotting a rolling 7 days line chart
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.
However, 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 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:
=sort(A6:E,1,FALSE)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.
Filtering blanks out
If your sheet includes columns or rows for future dates where the data’s not yet filled in you’ll need to filter those out. To prevent blanks in this case, use the filter() function along with isblank(). This will remove any columns or rows you don’t yet have data for and then wrap that in a sort as in the example above.
=sort(filter(A2:E,ISBLANK(B2:B) = FALSE),1,FALSE)Here filter is removing any rows front your selection A2:E where the B column is blank. It’s then taking that and applying the same sort we did above.
Spreadsheet used in these examples
For reference, you can view the spreadsheet used in the above examples and use-cases.