Google-sheets – Google Sheet jump to current date on Open (very large sheet)

google sheetsgoogle-apps-script

I understand that this question has been asked before but most users seem to only have the dates in one column or a limited number. The sheet I am trying to work with has the schedule for one entire year and the dates are in cells that have been combined, ie cells F-H show a single date, cells I-AJ show another date. I have determined that the columns with the dates range from F:EU and have a Row range of 4:2900; starting with row 4 then moving down to row 55, 106,157,208 etc until they reach row 2809.

Is there anyway to jump to todays date when opening this sheet?

Failing script

I have been trying to use something that was posted by another user but I cannot get it to work due to the fact the sheet covers so many columns and rows for the dates.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var s = ss.getSheetByName("Annual Schedule"); // change to sheet containing dates 
  var r = s.getRange("F2").getValue(); //change A1 to cell containing =match formula 
  s.setActiveSelection(s.getRange("A" + r )); 
}

Best Answer

You have a spreadsheet with dates for the entire year in various columns in the range "F:EU" and rows 6-2900. When the spreadsheet is opened, you wish to automatically move the cursor to the column corresponding to "todays" date.

  • Enter =Today() in Cell A3.

    • The date should be in the same format at the dates on the sheet.

  • Enter the following code in the Script editor.

  • Add the script jumptotoday to your button.

function jumptotoday() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "demo";
  var sheet = ss.getSheetByName(sheetname);

  // todays date is in cell A3
  //note use of DisplayValue
  var thisdate = sheet.getRange("A3").getDisplayValue();
  // Logger.log("DEBUG: todays date = "+thisdate)  
  
  // the dates in the spreadsheet as advised:
  // columns F:EU, Row range of 4:2900
  // Column F = 6; Column EU = 151; 
  // rows start at row#4 then increment by 51 row (row #55,106,157, 208, etc)

  var startRow = 4;
  var endRow = 2900;
  var startCol = 6;
  var endCol = 51;
  var rowIncrement = 51;

  var dateRange = sheet.getRange(startRow, startCol, (endRow-startRow+1),(endCol-startCol+1));
  // Logger.log("DEBUG: the date range = "+dateRange.getA1Notation());
  
  // get the data
  var dates = dateRange.getDisplayValues();

  // loop through the data, one row at a time. test for match in each row
  for (var i = 0; i<(endRow-startRow-1);i += rowIncrement){
  
    // indexOf match is index of first character of the match, otherwise no match = -1
    var match = (dates[i].indexOf(thisdate));
    if (match !=-1){
      // Logger.log("DEBUG: i="+i+", match = "+match);
      var rowMatch = i+startRow;
      var colMatch = match+startCol;
      // Logger.log("DEBUG The match is row "+rowMatch+", the column is "+colMatch);

      // create a range for the matched date.
      var cellrange = sheet.getRange(rowMatch, colMatch);
      // Logger.log("DEBUG: the date location is "+cellrange.getA1Notation());
    
      // get current cell location
      var currentCell = sheet.getCurrentCell();
      var currentRow = currentCell.getRow();
      var currentCol = currentCell.getColumn();
      // Logger.log("DEBUG: the current cell is "+currentCell+", row = "+currentRow+", and column = "+currentCol)

      // calculate the offsets to thge row match  
      var rowoffset = rowMatch-currentRow;
      var colOffset = colMatch-currentCol;

      // got to the matching cell
      sheet.getCurrentCell().offset(rowoffset, colOffset).activate();
      
      break;
    }
 
  }

  return;
  
}
Related Topic