Google sheet cell delete at specific date

Question

there are some cells in my spreadsheet which I want to remove or delete on a specific date is over . can you help me? I already installed google script.

java programming , trigger to delete cells in specific date . waiting for response

Answer 1

function deleteOldRows() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const [hA, ...vs] = sh.getDataRange().getValues();
  const dtv = new Date().valueOf();
  let idx = {};
  let d = 0;
  hA.forEach((h, i) => idx[h] = i);
  vs.forEach((r, i) => {
    let dv = new Date(r[idx["COL1"]]).valueOf();
    if (dv < dtv) { // if you only want a specific date change < to  ==
      sh.deleteRow(i + 2 - d++)
    }
  })
}

Start Data:

	A	B	C	D	E
1	COL1	COL2	COL3	COL4	COL5
2	8/1/2023	21	14	2	24
3	8/2/2023	21	20	21	24
4	8/3/2023	17	22	23	5
5	8/4/2023	5	24	9	23
6	8/5/2023	16	3	23	14
7	8/6/2023	12	8	16	7
8	8/7/2023	21	20	14	17
9	8/8/2023	18	8	7	24
10	8/9/2023	19	19	18	16
11	8/10/2023	22	22	4	19
12	8/11/2023	2	16	18	1
13	8/12/2023	16	8	3	20
14	8/13/2023	5	1	15	21
15	8/14/2023	11	11	13	15
16	8/15/2023	22	13	3	6
17	8/16/2023	20	6	19	1
18	8/17/2023	7	20	5	24
19	8/18/2023	5	8	18	6
20	8/19/2023	4	13	12	21
21	8/20/2023	10	7	17	14
22	8/21/2023	11	6	11	11
23	8/22/2023	21	13	7	8
24	8/23/2023	9	17	21	21
25	8/24/2023	15	0	17	23
26	8/25/2023	12	17	21	10

End Data:

	A	B	C	D	E
1	COL1	COL2	COL3	COL4	COL5
2	8/13/2023	5	1	15	21
3	8/14/2023	11	11	13	15
4	8/15/2023	22	13	3	6
5	8/16/2023	20	6	19	1
6	8/17/2023	7	20	5	24
7	8/18/2023	5	8	18	6
8	8/19/2023	4	13	12	21
9	8/20/2023	10	7	17	14
10	8/21/2023	11	6	11	11
11	8/22/2023	21	13	7	8
12	8/23/2023	9	17	21	21
13	8/24/2023	15	0	17	23
14	8/25/2023	12	17	21	10

If you wish to keep today, you can do this:

function deleteOldRows() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const [hA, ...vs] = sh.getDataRange().getValues();
  let dt = new Date();
  const dtv = new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()).valueOf();
  let idx = {};
  let d = 0;
  hA.forEach((h, i) => idx[h] = i);
  vs.forEach((r, i) => {
    let dv = new Date(r[idx["COL1"]]).valueOf();
    if (dv < dtv) {
      sh.deleteRow(i + 2 - d++)
    }
  })
}

It depends upon whether or not your date values include time or not