I want to repeat a line item in a google sheets form response based on a date value for consecutive dates

Question

I use a google form for people to submit daily announcements. Some announcements only run for one day but some repeat over multiple weekdays. I was trying to figure out if there was a way to possibly use a google app script on the form results to create duplicate line items with the additional dates based on the number of times it should repeat

Sheet: FORM RESPONSES 1

A	B	C	D	E
Timestamp	Email Address	Date of Announcement	Announcement	Days to Repeat
8/26/2024 04:08:31	[email protected]	08/29/2024	Good luck...	3
8/28/2024 14:08:31	[email protected]	08/30/2024	Join our club...	2

What I would like to happen (or something similar):

A	B	C	D	E
Timestamp	Email Address	Date of Announcement	Announcement	Days to Repeat
8/26/2024 04:08:31	[email protected]	08/29/2024	Good luck...	3
8/26/2024 04:08:31	[email protected]	08/30/2024	Good luck...	3
8/28/2024 14:08:31	[email protected]	08/30/2024	Join our club...	2
8/26/2024 04:08:31	[email protected]	09/02/2024	Good luck...	3
8/28/2024 14:08:31	[email protected]	09/02/2024	Join our club...	2

The order isn't necessarily a priority but would like it to repeat line items on weekdays (notice the jump from 8/30-9/2)

Answer 1

You can use an array formula in a separate sheet:

={ 'FORM RESPONSES 1'!A1:E1;
   WRAPROWS(TOROW(LET(
     rng,'FORM RESPONSES 1'!A2:E, 
     arr,FILTER(RNG, LEN(INDEX(rng,,1))),
     BYROW(arr, LAMBDA(r, LET(
       n,INDEX(r,,5), dt,INT(INDEX(r,,3)),
       dts,SEQUENCE(n+CEILING(n/7,1)*2,1,dt),
       wkdaze,FILTER(dts, WEEKDAY(dts,2)<6),
       TOROW(MAP(SEQUENCE(n), LAMBDA(rw, 
         { CHOOSECOLS(r,1,2), INDEX(wkdaze,rw),
          CHOOSECOLS(r,4,5) })))))))),5) }

BYROW  CEILING  CHOOSECOLS  FILTER  INDEX  INT  LAMBDA  LEN  LET  MAP  SEQUENCE  TOROW  WEEKDAY  WRAPROWS

Source Data

Formula & Result

Explanation

The formula returns an array where the first row is the column labels from 'FORM RESPONSES 1'!A1:E1 and the remaining rows are created as follows:

1. rng stores the data range 'FORM RESPONSES 1'!A2:E
2. arr holds the values in rng after FILTER removes any empty rows.
3. BYROW maps the array arr, row-by-row, into a LAMBDA function that stores the current row in r
4. For each row r, the LAMBDA's formula:
   1. Stores the "Days to Repeat" value (Column 5) in n.
   2. dt stores the "Date of Announcement" value from Column 3, and INT ensures it doesn't include time.
   3. dts stores an array of dates returned by SEQUENCE where:
      • the first date is the value in dt
      • the length of the array (number of dates) is determined by adding the "Days to Repeat" n to an additional 2 days per week contained in n which ensures dts contains enough dates after weekends are discarded.
   4. wkdaze stores the result of filtering dts to exclude any weekend days. The FILTER condition uses WEEKDAY to convert dts to numbers from 1 to 7. Its type argument is set to 2, meaning Monday=1 and Sunday=7. Then FILTER returns only those dates from dts where the weekday number is less than 6 which excludes Saturday & Sunday.
   5. Finally, MAP is used to pass an array of numbers from 1 to n (each representing a repeated row), one-by-one into a LAMBDA function, where the current value is stored in rw.
   6. For each rw, the LAMBDA's formula creates a single-row array containing:
      1. CHOOSECOLS returns the Timestamp and Email Address from Columns 1 and 2 of r
      2. INDEX returns the date from wkdaze that is in row rw to be used as the "Date of Announcement".
      3. The last 2 columns are added by CHOOSECOLS returning the Announcement and "Days to Repeat" from Columns 4 and 5 of r
   7. The original (outer) LAMBDA that called the MAP function expects a single result for each r therefore the result is transformed to a single row using TOROW to avoid errors where the repeats are >1.
5. The result of the BYROW function is an array of rows that is as wide as the largest number of repeats x 5 columns. For example, a row that has no repeats will have 5 columns containing values, but a row that runs 3 times will have 15 columns of values. This is the result of step #4.7 where TOROW was used to return a single result for each mapped r
6. To restore the 5-column format, TOROW is again used to create a single row from the results, and then WRAPROWS warps the row after every 5 columns.