How to Activate the cell in a column with the current date in Google Sheets Script?

Question

I am looking for a simple script to press a button or use a drop down to go to the current date. I am new to scripting and this problem is definitely showing that. I already have a menu item created.

I have tried this, but it is not doing anything. I'm not sure what the issue is, whether it is formatting or another issue.:

function myFunction() {
  var ss = SpreadsheetApp.getActive();
  var sheet = SpreadsheetApp.getActiveSheet();
  var date = Utilities.formatDate(new Date(), 'GMT+5', 'dd/MM/yyyy');
  var ranges = sheet.createTextFinder(date).findAll().map(r => r.getA1Notation());
  sheet.getRangeList(ranges).activate();
}

Answer 1

try:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var date = Utilities.formatDate(new Date(), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), 'dd/MM/yyyy');
  var cell = sheet.getActiveCell();
  cell.setValue(date);
}

added SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone() to ensure the date is formatted according to the spreadsheet's time zone

if you prefer GMT within script use:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var date = Utilities.formatDate(new Date(), 'GMT+5', 'dd/MM/yyyy');
  var cell = sheet.getActiveCell();
  cell.setValue(date);
}

update

to jump to current day when dates on active sheet are in A column use:

function jumpToToday() {
  // Get the active spreadsheet and sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Get the current date and set hours to 0 to compare dates only (without time)
  var today = new Date();
  today.setHours(0, 0, 0, 0);
  
  // Get all values in column A
  var range = sheet.getRange("A:A");
  var values = range.getValues();
  
  // Loop through the values to find today's date
  for (var i = 0; i < values.length; i++) {
    var cellDate = values[i][0];
    
    // Check if the cell contains a date and if it matches today's date
    if (cellDate instanceof Date) {
      cellDate.setHours(0, 0, 0, 0);
      if (cellDate.getTime() === today.getTime()) {
        // Activate the cell with today's date and scroll to it
        var targetRange = sheet.getRange(i + 1, 1);
        sheet.setActiveRange(targetRange);
        sheet.getActiveRange().activate(); // Ensure the cell is selected
        break;
  }}}}