Function to subtract using automatically added date in Google Sheets Script

Question

I'm fairly new to google sheets, learning things as I go, with only mild experience with coding in general. I'm working on a sheet where I want it to automatically fill in some cells whenever a new entry is added. I've gotten it to where it adds the current date in without the time, and I want the cell after it to subtract this newly added date from the current date, using setFormula so that it will update itself without needing a whole other custom formula in the script. When I do it this way, when I add a new entry, it shows as

=TODAY()-Range

in the sheet, rather than correctly replacing the reference in the code with the value. I'm not sure how to either reference the cell on its own, or correctly pull the value.

I've also tried pulling from the previous cell with offset and getValue, but it doesn't maintain the formatting I applied and need to perform the calculation. In an onEdit function, embedded inside an if statement, I have this chunk of code currently.

if(e.range.columnStart === 1 && e.range.rowStart >= 2) {
    const date = Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy");
    const dateCell = e.range.offset(0,9)
    dateCell.setValue(date);
    const daysSince = e.range.offset(0,10)
    daysSince.setFormula("=TODAY()-" + dateCell);
}

The remaining functionality not included here seems to work perfectly fine, but I can't seem to find anything appropriate to my situation.

Answer 1

Use range.getValue() or range.getA1Notation() depending what you want to achieve.

IF ((e.range.columnStart === 1) && (e.range.rowStart > 1)) {
  const dateCell = e.range.offset(0,9);
  dateCell.setValue(Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy"));
  e.range.offset(0,10).setFormula("=TODAY()-" + dateCell.getA1Notation());
}