setValue of row function need to modify from previous answer given

Jon Beckner 0
Viewed 32

Posting new question based on a previous question set value of row for previous month A function was written to setValue of rows if date is from previous month.

function hideRows() {
 const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("IAD(Tampa)");
  const vs = sh.getDataRange().getValues();
  const dtv0 = new Date(new Date().getFullYear(),new Date().getMonth() - 1,1).valueOf();
  const dtv1 = new Date(new Date().getFullYear(),new Date().getMonth(),1,0).valueOf();
  let rows = [];
  vs.forEach((r,i) => {
    let d = new Date(r[0]);
    let dv = d.valueOf();
    if(dv >= dtv0 && dv < dtv1) {
      rows.push(i+1);
      sh.getRange(i + 1,1).setValue(null);
    }
  })
}

If I wanted this formula to only setValue to dates 2 months and older I know that I can change const dtv0 = new Date(new Date().getFullYear(),new Date().getMonth() - 2,1).valueOf(); but thats grabs everything except current month. How can I have it leave last months values? Example: dates are as follows 7/9/22 7/26/22 8/20/22 and today's date is 9/7/22 I want to leave 8/20/22 alone. I am not clear how to do this with the current function.

Solution is as follows: function hideRows() { const ss = SpreadsheetApp.getActive(); const sh = ss.getSheetByName("IAD(Tampa)"); const vs = sh.getDataRange().getValues(); const dtv0 = new Date(new Date().getFullYear(),new Date().getMonth() - 1,1,new Date().getDate()).valueOf(); let rows = []; vs.forEach((r,i) => { let d = new Date(r[0]); let dv = d.valueOf(); if(dv < dtv0) { rows.push(i+1); sh.getRange(i + 1,1).setValue(null); } }) }

google-apps-script
1 Answers

Try this:

function hideRows() {
 const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Archived Videos");
  const vs = sh.getDataRange().getValues();
  const dtv0 = new Date(new Date().getFullYear(),new Date().getMonth() - 1,1).valueOf();
  let rows = [];
  vs.forEach((r,i) => {
    let d = new Date(r[0]);
    let dv = d.valueOf();
    if(dv < dtv0) {
      rows.push(i+1);
      sh.getRange(i + 1,1).setValue(null);
    }
  })
}
user30hu0
Related