1

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 )); 
}
8
  • Have you explored using MATCH() support.google.com/docs/answer/3093378?hl=en
    – 24601
    Oct 7, 2020 at 19:22
  • 1
    Welcome to Web Applications. Please show what you tried and add a more specific question (i.e. if you get an error add that error textually).
    – Rubén
    Oct 7, 2020 at 19:54
  • 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 )); }```
    – 160esmith
    Oct 7, 2020 at 22:04
  • Columns-Please clarify your logic. The sheet ... has the schedule for one entire year, cells F-H show a single date & I have determined that the columns with the dates range from F:EU "F-H" suggests 3 merged cells per date; F:EU accounts for 146 columns. If each date comprises three cells, then Column EU (Column#151) would be blank and at best the relevant date would be @18 Feb 2020 (assuming a 1 January 2020 start date in Column F). You also say "I-AJ show another date": I-AJ = 28 columns, surely this is a typo.
    – Tedinoz
    Oct 9, 2020 at 1:39
  • Rows - Please clarify your logic. I have determined that ... dates ... have a Row range of 4:2900 This represents 2,897 rows; this is not a multiple of 366 days. Also, there are no divisors 2 or more that would yield a predictable number of rows per date. In other words, there is no logic to the number of rows that you have quoted.
    – Tedinoz
    Oct 9, 2020 at 1:48

1 Answer 1

0

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;
  
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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