I need to update dates in a sheet once a week.

I would like to manually run a script to do this.

Each date in column "C" will increase by a number of days in column "D" in same row. Only dates prior to a date in specific cell (F2 in example) will be updated.

See this sheet as an example: https://docs.google.com/spreadsheets/d/11f6G5_vNK5Z8UR2A_MUUXpL8awC2mJ8ozpnznWQ_anM/edit?usp=sharing

I have read many posts but am very new at these types of scripts. Any help or pointing in the right direction would be great!

Thanks, Dave

CodePudding user response：

function updateDates() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const vs = sh.getRange(2,1,sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  const dt = new Date(sh.getRange('F2').getValue());
  const dthv = new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()).valueOf();
  const oA = [];
  vs.forEach(r => {
    let d = new Date(r[2]);
    let dv = new Date(d.getFullYear(),d.getMonth(),d.getDate()).valueOf();
    if(dv < dthv) {
      oA.push([new Date(d.getFullYear(),d.getMonth(),d.getDate()   r[3])]);
    } else {
      oA.push([r[2]]);
    }
  });
  sh.getRange(2,3,oA.length,oA[0].length).setValues(oA);
}

Sheet 0 Before:

Sheet0 After:

CodePudding user response：

the script below from Copper worked like a charm.