How can I Copy the date value from Column C to Column F for each row that matches criteria?-CodePudding

Trying to create a Google App Script for Sheets that will do the following:

Copy Date found in column C to column F
IF: Date in column C is <= C1.
AND: E = "Yes"
For all rows in sheet range A6:F

Example sheet,

/	A	B	C	D	E	F
1		Mark received if before:	12/15/2021
2
3
4
5	INVOICE No	CUS No	Invoice Date	JOBamount	PayAtService	Received date
6	1	3	12/10/2021	$15.00	Yes
7	2	1	12/6/2021	$15.00	Yes
8	3	21	12/14/2021	$45.00	Yes
9	4	20	12/14/2021	$15.00	Yes
10	5	13	12/15/2021	$12.00	No
11	6	14	12/15/2021	$10.00	No
12	7	12	12/15/2021	$12.00	No
13	9	9	12/15/2021	$30.00	No
14	10	1	12/15/2021	$15.00	Yes
15	11	18	12/15/2021	$12.00	No
16	12	17	12/15/2021	$25.00	No
17	13	15	12/15/2021	$12.00	Yes
18	14	16	12/15/2021	$20.00	No
19	15	19	12/15/2021	$30.00	Yes
20	16	4	12/16/2021	$15.00	No
21	17	5	12/16/2021	$7.00	Yes
22	18	6	12/16/2021	$5.00	Yes
23	19	7	12/16/2021	$7.00	No
24	20	8	12/16/2021	$6.00	Yes
25	21	10	12/20/2021	$15.00	No
26	22	23	12/20/2021	$30.00	Yes
27	23	13	12/29/2021	$12.00	Yes
28	24	12	12/29/2021	$12.00	Yes
29	26	9	12/29/2021	$30.00	Yes
30	27	15	12/29/2021	$12.00	Yes
31	28	16	12/29/2021	$20.00	No
32	29	23	12/29/2021	$30.00	No

(This seems like it would be simple but haven't quite figured out how)

I tried something like this but I think I am limiting my range (want to all rows in sheet to be in range) and it is also simply full of errors...

function ReceivedDate() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('sheet1');
  var vs = sh.getRange(6, 1, 500, 6).getValues() //getRange(row, column, numRows, numColumns) but this needs to be whole sheet from row 6 down
  var cutoffDate = new Date(sh.getRange('F2').getValue());
  
  vs.forEach(row => {
    if (row[5] != 'Yes'  && new Date(row[3]).valueOf() <= cutoffDate) 
      vs(row, 6).setValue(row, 3);
  }
}

CodePudding user response：

I do not know if you want a solution in AppScript or just a solution. But for your requirement above, a simple formula as below should work.

=IF(AND(DATEVALUE(C6) <= DATEVALUE($C$1), E6 = "Yes"),C6,"")

CodePudding user response：

Try this.

function ReceivedDate() {
   var ss = SpreadsheetApp.getActive();
   var sh = ss.getSheetByName('sheet1');
   var vs = sh.getRange(6, 1, 500, 6).getValues() //getRange(row, column, numRows, numColumns) but this needs to be whole sheet from row 6 down
   var cutoffDate = new Date(sh.getRange('C1').getValue());
   vs.forEach((row,index)=> {
      if (row[4] == 'Yes'  && new Date(row[2]) <= cutoffDate){
            sh.getRange(index 7,6).setValue(row[2]);
        }  
   })
}