1

I have found a similar question here:
How to make Google Sheet jump to today's column when opened?

I want to do a similar thing, but I have the dates in row 5 not in the column. I tried several times to make it work by doing some changes, either in the first than in the second script, but it didn't work. I'm really new, so I suppose the solution is easy, but unfortunately, I am not able to solve it by myself.

The code in the question I quoted is this:

function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var range = sheet.getRange("A:A");
    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;
        }
    }
}

Please, can someone help me?

0

var range = sheet.getRange("5:5") = dates in row 5

function onOpen() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
 var range = sheet.getRange("5:5");

 var values = range.getValues(); 
 values = values[0];
 var day = 24*3600*1000;  
 var today = parseInt((new Date().setHours(0,0,0,0))/day);  
 Logger.log(today);
 var ssdate; 
 for (var i=0; i<values.length; i++) {
   try {
     ssdate = values[i].getTime()/day;
   }
   catch(e) {
   }
   if (ssdate && Math.floor(ssdate) == today) {
     sheet.setActiveRange(range.offset(0,i,1,1));
     break;
   } } }

var range = sheet.getRange("E:E") = dates in column 5

function onOpen() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
 var range = sheet.getRange("E:E");
 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;
   } } }
  • Thank you very much, it works, but... trying to open the file with the android app it causes the crash of application – Carlo Della Schiava Jan 25 at 7:20
0

I think your saying you have dates on row 5. So you can call the gotoToday() function in your onOpen() function.

function gotoToday() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sh=ss.getActiveSheet();
  var rg=sh.getRange(5,1,1,sh.getLastColumn());
  var vA=rg.getValues();
  var today=new Date(new Date().getFullYear(),new Date().getMonth(),new Date().getDate());
  for (var i=0;i<vA[0].length;i++) {
    if(new Date(new Date(vA[0][i]).getFullYear(),new Date(vA[0][i]).getMonth(),new Date(vA[0][i]).getDate())>=today){
      sh.getRange(5,i+1).activate();
      break;
    }
  } 
}
  • thank you very much. Your function works, but i don't know how to call it in the onopen function, but i think i can make it. My hope is that function can also work on android app – Carlo Della Schiava Jan 25 at 14:31
  • Just type gotoToday(); if that doesn’t work and you get an error that says you don’t have permission to do that then you may have to put it in and installable onOpen. You can read about the restrictions on simple triggers in the documentation. – Cooper Jan 25 at 15:45
  • it works fine on pc, but i still have problem with android app. I think that could be the sheet with too many data. The android app have some trouble with this file also without the script, only trying to do a research the app gets very slow and finally crash – Carlo Della Schiava Jan 25 at 17:58

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.