Google Apps Script: Open the sheet on 'Todays' Date

0
votes

I've got an annual calendar split by week, and a whole bunch of content cells in between each week. Essentially, B2:F2 has Jan 1 to Jan 5, and then B15:F15 has Jan 9 to Jan 15, for example.

I'm trying to get this sheet to open on the cell containing Todays date, but the below script allows me to search for 'Today' in just one column (D:D). I'm not able to get it to work on the entire sheet.

So far I have:

function onOpen() {
 var menu = [{name: "Jump to today's date", functionName: "jumpToDate"}];
 SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
 jumpToDate();
}

function jumpToDate() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
  var range = sheet.getRange("D:D");
 var values = range.getValues();  

 var day = 24*3600*1000;  
 var today = parseInt((new Date().setHours(0,0,0,0))/day);  
 var ssdate; 
 for (var i=0; i<values.length; i++) {
   try {
     ssdate = values[i][0].getTime()/day;
   }

   catch(e) {
   }
   if (ssdate && Math.floor(ssdate) == today) {
     sheet.setActiveRange(range.offset(i,0,1,1));
     break;
   }    
 }
  Logger.log(ssdate);
}

This seems to only work on column D, but every time I try to expand the range in this line: It doesn't work. 

  var range = sheet.getRange("Invoice!B2:F500");

Does anyone know how I could make this work?

NOte: I did not write this script, I got it off this thread :)

Thank you!

Edit:

We only go off a 5 day week (Monday to Friday)

Here's what the sheet looks like: https://docs.google.com/spreadsheets/d/1JQxXy-ErVKATh5cUCD7u6QLQkaDyXfhhGuMKs_xgbh4/edit?usp=sharing

And here's an image: spreadhseetexample

2
google-apps-scriptgoogle-sheets
Please provide an image of your spreadsheet. - Cooper
Please explain how how 7 days fit into 5 cells for B15:F15 - Cooper
Where are Jan 6,7 and 8? - Cooper
Hi @Cooper! Thanks for helping out. I've put this link in for you, tell me if it makes sense? docs.google.com/spreadsheets/d/… - Kenneth Lobo
I sorry but I no longer following links to spreadsheets from this site. An image would be great though. - Cooper

2 Answers

0
votes

The code you're using only works for a single column range and you want to use a multiple column range. I recommend you to implement a solution using TestFinder class. Here's an example for your case:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

// Creates  a text finder.
var textFinder = sheet.createTextFinder(today);

// Returns the first occurrence cell of 'today' in the sheet.
var firstOccurrenceRange = textFinder.findNext();

// Sets the active range
sheet.setActiveRange(firstOccurrenceRange)
0
votes

Jumping to a date

var dt is today's date so if you want to test this on your current calendar then use the line that I commented out. The parameters are fullyear, month, day where month is 0-11 and day is 1 - 31. It's all in the JavaScript reference for Date object.

Be careful with the date formatting because they have to be exact matches and remember to use getDisplayValues() not getValues() for the comparison.

You probably will need to create an installable onOpen trigger and don't use the name onOpen() otherwise you will get more than one trigger which may cause problems. I tested this code in legacy ES5 also called Rhino

function jumpToDate() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var sc=2;
  var sr=2
  var dt=new Date();
  //var dt= new Date(2020,0,28);//used this for testing
  var da=dt.getDay();
  var ds=Utilities.formatDate(dt,Session.getScriptTimeZone(), "EEEEEEE, MMMMMMM dd, yyyy");
  var rg=sh.getRange(sr,sc+da-1,sh.getLastRow()-sr+1,1);
  var vs=rg.getDisplayValues();  
  var today=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate());
  for(var i=0;i<vs.length;i++) {
    if(ds==vs[i][0]) {
      sh.getRange(i+sr,sc+da-1).activate();  
      break;
    }
  }
}

Javascript Date