0

I currently have a Google script bound to my spreadsheet using an onEdit trigger to allow multiple dropdowns.

However, it is affecting my columns with dates, changing them from date format "DD/MM/YYYY" to a 5-digit number

Please let me know what I need to add to the script to stop the format from changing to Automatic.

Also, my code isn't great, I had to copy and paste and play with it to make it work, and I didn't know how to apply it to specific ranges on specific sheets, so applies to all of them. I don't know if this is causing my issue.

To note, the date columns on each sheet are different (Sheet 1 is Column 16 and 17, Sheet 2 is 6, etc.)

My Code:

function onEdit(e) {
  
  var oldValue;
  var newValue;
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var activeCell = ss.getActiveCell();
    
  if(activeCell.getColumn() == 4,5,6,7,9,10,11,18 && ss.getActiveSheet().getName()=="Sheet 1", "Sheet 2", "Sheet 3", "Sheet 4") {

      newValue=e.value;
      oldValue=e.oldValue;

      if(!e.value) {
      
        activeCell.setValue(""); 
      
      } else {

        if (!e.oldValue) {

          activeCell.setValue(newValue);

        } else {

          activeCell.setValue(oldValue+', '+newValue);

        }

     }
  }

}
1
  • Dates are numbers, simply apply the preferred date format explicitly to the column in question instead of leaving it as automatic.
    – Blindspots
    Commented Dec 11, 2023 at 21:26

1 Answer 1

0

For the date issue, explicitly apply a 'Date' number format to the column. Don't leave it as 'Automatic'.

For the sheet name and column tests, see the following example script. You can replace the actions with your own.

function onEdit(e) {

  const range = e.range;
  const sheet = range.getSheet();
  const name = sheet.getName();
  const col = range.getColumn();
  const row = range.getRow();

  const columns = [ 
    { Name: "Sheet1", Nums: [16, 17] },
    { Name: "Sheet2", Nums: [6] }
  ];
  
  const i = columns.findIndex(c => c.Name === name);
  
  if ((i > -1) && (columns[i].Nums.indexOf(col) > -1)) {

    sheet.getRange(row, col).setValue("✅");

  } else {

    sheet.getRange(row, col).setValue("❌");

  }

}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.