How to retrieve rows of data that loop through the whole sheet

Question

In Google Sheets I want to be able to copy four rows at a time to a new sheet but every day the four rows change. Each row is 11 cells long.

Example:

Day 1: Rows 1-4

Day 2: Rows 5-8

etc until it hits the bottom and then returns to the top to go again.

My demo sheet https://docs.google.com/spreadsheets/d/1qH1_LplzOaCpiqbN7vTi7ycYK4rWBGPY8dkc3UN7FBA/edit?usp=sharing

Answer 1

Try this. Insert this is cell A2 of Sheet2:

=query('Sheet 1'!A2:K337,"Select * limit "& (weeknum(today(),1)*4))

It uses the Limit clause to display four days for each week of the calendar year.

EDIT:
4 days per "day" Try this. Insert this is cell A2 of Sheet2:

=query('Sheet 1'!A2:K337,"Select * limit "& mod(days(today(),date(year(today()),1,1)),84)*4)

This solution also uses the "Limit clause", but the key is in the use of mod to calculate the number of days to display.

• date(year(today()),1,1) - this returns the starting date in the calendar year. Note the use of year(today()) - this ensures that the starting date will automatically roll over to the start of the following calendar year.
• days(today(),date(year(today()),1,1)) - this calculates the number of days between "today" and the first day of the calendar year.
• mod(days(today(),date(year(today()),1,1)),84)- "mod" returns the remainder of a division calculation. In this case, the number of elapsed days is divided by 84. The value of "84" is derived by taking the total number of data-days (336) divided by 4 (display 4 rows per day) - this result is the number of days it takes to get to the bottom of the list. So "mod(days,84)" returns the number of days in the next annual cycle.
• The solution requires that 4 days of data is displayed for each days of the year. So, the formula multiplies the mod result by 4.