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.


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

  • Welcome. Please read how to and share a test sheet so as you can be easier helped. – marikamitsos May 17 '20 at 3:52
  • Welcome. Parts of your question are unclear or counter-intuitive. Would you please explain i) why is the data to be copied to sheet#2; ii) what will trigger the weekly process; iii) what is the starting point for a week (1st January, another reference date, etc)?; iv) what day of the week is the copy to be made?; v) 336 rows of data, 4 rows/week = 84 weeks. this is 32 weeks longer than a calendar year. is this an intentional deliberate time differential?; vi) until it hits the bottom-a lot of time could be invested to develop a solution for an annual event. is this really necessary? – Tedinoz May 19 '20 at 3:56
  • I made a mistake in my question. I was supposed to say 4 rows per day, not week. I'm trying to setup a webhook to discord that triggers when the 4 rows are updated. It's for a fantasy calendar where 1 day is 4 days in the fantasy world. Sorry if I was unclear. – Silencer May 20 '20 at 2:44

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.

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.
  • I tried this and it returned the whole Sheet1. – Silencer May 22 '20 at 1:42
  • Are you sure that it returned ALL of sheet 1, or all of the rows down to March 28? Anyway, I have edited the answer to provide a formula to display 4 rows per day as opposed to 4 rows per week. – Tedinoz May 22 '20 at 4:40
  • Thank you very much for working with me. I tried the new formula and it's still filling it with all of Sheet 1. – Silencer May 22 '20 at 21:29
  • Apologies, I have been without internet for a week. A can't explain the response that you are getting. Would you please share a copy of your spreadsheet, excluding private, sensitive or confidential spreadsheet data. When deciding whether to share your spreadsheet, be aware that sharing your spreadsheet will unavoidably expose your email address. – Tedinoz Jun 1 '20 at 23:32
  • @Silencer "I tried the new formula and it's still filling it with all of Sheet 1". I looked at your Test Sheet, and on 6 June 2020 (day#157) the formula fills data to row 293, "November 12". This is the expected result, based on fours days data displayed for every elapsed day of the year. – Tedinoz Jun 6 '20 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.