google-apps-scriptgoogle-sheets

How to show a default current date in a cell in Google Sheet where data validation is used for selecting date?


I have a cell in Google sheet where a user can select a date. I did it using data validation based on the criteria of Is Valid Date. Now I would like to show/use a default today's date in the (yyyy-mm-dd) format in that cell when the Google spreadsheet is opened. I used the following code inside onOpen():

  var spreadsheet = SpreadsheetApp.getActive();
  var dashboard = spreadsheet.getSheetByName("Dashboard");
  dashboard.getRange("C4").setValue(now);
  //dashboard.getRange("D4").setValue(now);

Please note that the cell is C4. But it does not work. So how to solve it easily?


Solution

  • This seemed to work for me:

    I used a different but you can modify as needed

    function datetesting() {
      const ss=SpreadsheetApp.getActive();
      const sh=ss.getActiveSheet();
      let rule=SpreadsheetApp.newDataValidation().requireDate().build();
      sh.getRange(1,9,10,1).setDataValidation(null);
      sh.getRange(1,9,10,1).setDataValidation(rule);
      sh.getRange(1,9).setValue(new Date());
    }