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