Problem with setValue returning Range - want to pull "MMM, yy" from a date as TEXT only

Question

I'd love your help with this because I keep making parts of it work but then I try to fix one problem and it causes another sigh

I have a test sheet here where I have already attempted this in Apps Script. Unfortunately I couldn't paste my script here for some reason, but I've put it on a new tab in the Test Sheet.

Basically I have a nested on Edit formula. col A is Date col B is Month col C is Duration (trigger column)

What I'm trying to do: Any edit in Duration column

• Col A receives today's date UNLESS a date is already typed in
• col B pulls the month from Col A to display as text "MMM, yy"

I've managed t oget this almost working a couple of times but I run into some issues:

• Sometimes set Value returns "Range"
• set Value displays what I want but the actual cell data shows the date
• the formula works if a new date is entered by the script but not on existing dates

As I say any help would be freaking amazing TIA.

Answer 1

I think you're struggling because it's all inside that onEdit. You may consider dividing them in two different things. onEdit and the rest. I joined your other two functions, but you can obviosly separate them again:

function onEdit(e) 
{
  if(e.range.getColumn() == 3){
  setDates();}
}

function setDates(){
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getActiveSheet()
  var editrow = sheet.getActiveRange().getRow()
  var today = sheet.getRange(editrow,1)
  var value = new Date(new Date())
  var month = sheet.getRange(editrow,2)

  if(today.getValue() !== "") {
    month.setValue(today.getValue()).setNumberFormat("MMM-yy")
     }
  else {
    today.setValue(value).setNumberFormat('dd/MM/yyyy')
    month.setValue(value).setNumberFormat("MMM-yy").setNumberFormat('@') //now you change it to text with that last condition
    }
  }