CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Oleg Om
Oleg Om

Reputation: 439

Scroll through the spreadsheet to today's date using google app scripts

My google spreadsheet consists of columns:

I need a script that when you open the document will automatically scroll through the table to today's date.

Upvotes: 2

Views: 1812

Answers (2)

Cooper
Cooper

Reputation: 64100

Go to Today:

function gotoTodaysDate() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheets()[0];//most left sheet
  const shsr=2;//data start row
  const shsc=1;//date column
  const vs=sh.getRange(shsr,shsc,sh.getLastRow()-shsr+1,1).getValues();
  const dates=vs.map(function(r){return r[0];});//flattened
  const td=Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd.MM.yyyy");//current date string
  const idx=dates.indexOf(td);
  sh.getRange(idx+shsr,1).activate();
}

Installable onOpen Trigger:

function createOnOpenTrigger() {
  const ss=SpreadsheetApp.getActive();
  if(!isTrigger('gotoTodaysDate')) {
    ScriptApp.newTrigger('gotoTodaysDate').forSpreadsheet(ss.getId()).onOpen().create();
  }
}

Helper Function: Keeps you from creating more that one trigger for a given function

function isTrigger(funcName){
  var r=false;
  if(funcName){
    var allTriggers=ScriptApp.getProjectTriggers();
    for (let i=0;i<allTriggers.length;i++){
      if(funcName==allTriggers[i].getHandlerFunction()){
        r=true;
        break;
      }
    }
  }
  return r;
}

Upvotes: 3

Erik Tyler
Erik Tyler

Reputation: 9355

You may not need to have a script do that.

If your dates are in reverse order (i.e., oldest up top to newest at the bottom), and you're just trying to get to the most recent date at the bottom, you can click in cell A1 and hit the Ctrl key along with the down-arrow key (i.e., Ctrl-▼) on your keyboard, and you'll be taken there.

Upvotes: 0

Related Questions