CODEWITHSUNDEEP
javascriptdategoogle-sheets
SamXam
SamXam

Reputation: 65

Jump to current date cell (dates are in one row) Google Sheets

I have a google sheets spreadsheet. Row 2 contains dates e.g. 25/08/2020, 26/08/2020 going across many columns. Is there a script I can run to make it jump to the cell containing the current date when the document is first opened?

I know there is OnOpen() method which you define and it runs on opening the document, however, it is getting the code that actually works that's proving difficult.

Note: I have looked at Google spreadsheet / docs , jump to current date cell on Open but the solutions don't work (I assume due to me having my dates all in one row).

I don't know javascript really well, I understand a little of the basics. Any help would be much appreciated.

Thanks

Upvotes: 0

Views: 815

Answers (3)

Marios
Marios

Reputation: 27380

Solution:

While the other solutions might work for now, when working with Dates is recommended to consider display values instead. It is also highly recommended to get rid of old for loops and var declarations.

This will be a more futureproof solution:

function onOpen() {
    
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh =ss.getActiveSheet();
  
  const today = new Date(); 
  const today_year= today.getFullYear();
  const today_month = addZero(today.getMonth()+1);
  const today_day = addZero(today.getDate());
  const today_date =   today_day.toString() + "/" + today_month.toString() + "/" + today_year.toString();
  
  function addZero(i) {
  if (i < 10) {
    i = "0" + i;
  }
  return i;
}

  const dates = sh.getRange(2,1,1,sh.getLastColumn()).getDisplayValues().flat(1);
  dates.forEach((d,index)=>{ 
     if (d===today_date){
        sh.getRange(2,index+1).activate();}});
 
}

References:

getDisplayValues()

Upvotes: 0

Matt S
Matt S

Reputation: 86

You can use the code that was provided in the answer you cited in your question, you just need to change a couple of things:

  1. Make it look in a row, rather than a column (note that the data array is changing the second array dimension, rather than the first); and
  2. Make it look in a specific row, rather than just hardcoded to the first (you could just, instead of 0, have the array use a variable "row"; instead, I just had the code pull the data for only the row with dates - this is faster for very large spreadsheets).
    function onOpen() {
      var row = 8;  //set this to be the row with the dates
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sh = ss.getActiveSheet();
      var data = sh.getDataRange();
      var datesrow = sh.getRange(row,data.getColumn(),row,data.getWidth()).getValues();
      var today = new Date().setHours(0,0,0,0);
      for(var n=0;n<datesrow[0].length;n++){
        var date = new Date(datesrow[0][n]).setHours(0,0,0,0);
        if(date==today){break};
      }
      console.log(n);
      n++;
      sh.getRange(row,n).activate();
    }

Upvotes: 1

Maciek
Maciek

Reputation: 131

The code you found at Google spreadsheet / docs , jump to current date cell on Open does not work for you as it only checks the first column.
I modified this code a little to search for dates on a row.
Change rowWithDates variable as needed.

function onOpen() { // runs automatically
  
  var rowWithDates = 2; // change as needed
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var range = sh.getDataRange()
  var data = range.getValues();
  var today = new Date().setHours(0,0,0,0);
  var diffref = today;
  var diff;
  var idx;
  for(var n=0;n<range.getWidth();n++){
    var date = new Date(data[rowWithDates-1][n]).setHours(0,0,0,0);
    diff=today-date;
    if(diff==0){break}
    Logger.log("diffref = "+diffref+" today-date = diff = "+diff);
    if(diff < diffref && diff > 0){idx=n ; diffref=diff}
    }
  if(n==data.length){n=idx}
  n++;
  sh.getRange(rowWithDates, n).activate();
}

Upvotes: 2

Related Questions