Apps Script: Increment dates in format day/month/year for multiple columns-CodePudding

ISSUE:
I have 4 columns: A, B, C and D. Column A is filled on a daily basis with dates in format day/month/year. Now I want to make sure that anytime dates are written in column A, these dates are copied to columns B, C and D and incremented there by 1 day.

Example:
Let´s say the user has just written the dates 18/10/2021, 19/10/2021 and 20/10/2021 in 3 different rows in column A in format day/month/year. These dates should be copied and incremented by 1 day in each of the next columns, so that I can have following results:

Column B: 19/10/2021, 20/10/2021 and 21/10/2021
Column C: 20/10/2021, 21/10/2021 and 22/10/2021
Column D: 21/10/2021, 22/10/2021 and 23/10/2021

QUESTION:
The script below can copy just 1 date from column A and increment it by 1 day in column B. But how can I ensure that it does the same thing in columns B, C and D for more than just 1 row?

// Get date from column A, increment it by 1 day and write this incremented date in colum B
function incrementDateInGermanFormat() {
  var ss = SpreadsheetApp.getActiveSheet();
  var date = new Date(ss.getRange(1, 1).getValue());
  var newDate = new Date(date.setDate(date.getDate()   1));
  var newFormattedDate = Utilities.formatDate(newDate, Session.getScriptTimeZone(), "dd/MM/yyyy");
  ss.getRange(1, 2).setValue(newFormattedDate)
}

Thank you so much in advance for your help!!!

CodePudding user response：

That can be done with a plain vanilla spreadsheet formula. Put this in cell B1:

=arrayformula( 
  if( 
    isnumber(A1:A), 
    { A1:A   1, A1:A   2, A1:A   3 }, 
    iferror(1/0) 
  ) 
)

Format the result columns B:D as Format > Number > Date.

If you need to do this in Apps Script for some reason not mentioned in your question, use something like this:

function incrementDates() {
  const datesRange = SpreadsheetApp.getActiveSheet().getRange('A1:A');
  const dates = datesRange.getValues().flat();
  const result = dates.map(date => {
    if (!date || !date.getDate) {
      return [null, null, null];
    }
    const dayNumber = date.getDate();
    return [
      new Date(date.setDate(dayNumber   1)),
      new Date(date.setDate(dayNumber   2)),
      new Date(date.setDate(dayNumber   3)),
    ];
  });
  datesRange.offset(0, 1, result.length, result[0].length)
    .setValues(result);
}

The date format used in the spreadsheet does not come into play in this. See this answer for an explanation of how date and time values work in spreadsheets.