Spreadsheet jump to current date issue

Question

Hello dear community,

I am unfortunately not able to figure out where's the issue in @Serge insas AppScript to jump to Today's cell in my own spreadsheet.

Fixed potential issue with different timezones date format, however, the script jumps to the last cell as: sh.getRange(5,sh.getLastColumn()).activate(); indicates, but not to Today's cell.

Important to mention that my Spreadsheet is constructed horizontal, here is the link

Here is my version:

function JumpToToday() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
    sh.getRange(5,sh.getLastColumn()).activate();
  var data = sh.getDataRange("E5:NE5").getValues();
  var offsetToSS = new Date('22/02/2022').setHours(0,0,0,0);
  var today = parseInt((new Date().setHours(0,0,0,0)-offsetToSS)/86400000,10)+1;
  for(var n=0;n<data[0].length;n++){
    var date = data[0][n];
    Logger.log("date = "+data[0][n]+" =? "+today);
    if(date==today){break};
  }
  n++;
  sh.getRange(1,n).activate();
}

Can the issue be due to the script going first to last column and then starts counting from n=0?

I think your problem is you are performing math on a Date object. Once you have a date object such as `offsetToSS` use `offsetToSS.valueOf()`. Same for other dates you are working with — TheWizEd, Feb 22 '22 at 15:42
This was my thought too, but according to @Serge insas approach this is the ONLY way to subtract the Offset value from `today()` — Shavlen, Feb 22 '22 at 15:50

score 1 · Answer 1 · answered Feb 22 '22 at 16:15

1

Well I learned something new today. Date.valueOf() and Date.setHours(0,0,0,0) return the same, the value of date in milliseconds. But your date is invalid.

new Date("22/02/2022") returns Invalid date.

function dummy() {
  try {
    var offsetToSS = new Date('02/22/2022').valueOf();
    console.log(offsetToSS);
    var offsetToSS = new Date('02/22/2022').setHours(0,0,0,0);
    console.log(offsetToSS);
    var today = parseInt((new Date().setHours(0,0,0,0)-offsetToSS)/86400000,10)+1;
    console.log(today);
  }
  catch(err) {
    console.log(err);
  }
}

8:14:09 AM  Notice  Execution started
8:14:09 AM  Info    1645506000000
8:14:09 AM  Info    1645506000000
8:14:09 AM  Info    1
8:14:09 AM  Notice  Execution completed

answered Feb 22 '22 at 16:15

TheWizEd

7,517
2
11
19

Interesting approach, where can I see the additional values logs? – Shavlen Feb 23 '22 at 12:45
There is an error in your original post `new Date("22/2/2022")` gives me invalid date error. – TheWizEd Feb 23 '22 at 13:03
Yeah but when I ran the script there were no errors, how can I see them to know for future implication? – Shavlen Feb 23 '22 at 13:25
As I'm developing and debugging a script that can be run from the script editor I wrap my code in a `try { } catch(err) { }` as I've show above. That was how my dummy function alerted me to the "22/02/2022" error. If you are running from the menu you can use `SpreadsheetApp.getUi().alert(err);` in the catch block. – TheWizEd Feb 23 '22 at 13:56

score 1 · Accepted Answer · answered Feb 22 '22 at 19:13

To get to the current date in your sheet.

You have to count the number of days from January 1 up to this day.
Use the number of days in the col parameter of Sheet.getRange(row, col, numRows, numCol) + the number of column used before the January 1 cell.

Here I created a code that will activate the cell with the current date with the help of user2501097's answer here:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var snoopiSheet = ss.getSheetByName("Snoopi");
  var range = snoopiSheet.getRange(5, getCurrentDayofYear() + 5, 1,1);
  range.activate();
}


function getCurrentDayofYear(){
  var date = new Date();
  return (Date.UTC(date.getFullYear(), date.getMonth(), date.getDate()) - Date.UTC(date.getFullYear(), 0, 0)) / 24 / 60 / 60 / 1000;
}

Output:

Note: I used Snoopi sheet in the example above, I added + 5 in the 2nd parameter of getRange() because the start of the count is in the 5th column or January 1. Also, when using multiple Sheets, make sure to use ss.getSheetByName(name) this is to prevent script from applying the code to different sheets which might happen if you use getActiveSheet()

References:

Thank you, @Nikko J. for the deep dive into this DATE format questions, I find it rather weird that simple Spreadsheet formulas can produce quick & accurate date calculations but with AppScripts it makes thing lot more complicated. — Shavlen, Feb 23 '22 at 12:43
However, I've tried various ways to implement this script into my Spreadsheet and had no success - BTW my timezone is (UTC +1). — Shavlen, Feb 23 '22 at 12:44
EDIT: I got the script running perfect on "RUN", just had to adjust the triggers - works like a charm, thank you very much again Nikko! — Shavlen, Feb 23 '22 at 12:56

Spreadsheet jump to current date issue

2 Answers2

References: