How do i script button in google sheets to 'Jump to current date' automatically-CodePudding

I have huge sheets full of numbers - i'm looking to create a button in google sheets which allows me to jump to todays date (the dates are in a column)

i know how to create a hyper link however i want to click a button instead of text - google sheets only allows you to add script to a button and not hyperlinks.

Does anyone know how to script a button in google sheets to automatically jump to the current date on the sheet?

CodePudding user response：

Go to today's date

function gotoCurrentDate() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet Name');
  const sr = 2;//data start row
  const hA = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues().flat();//might need to edit header row
  const vs = sh.getRange(sr, 1, sh.getLastRow() - sr   1, sh.getLastColumn()).getValues();
  const dt = new Date();
  const dts = Utilities.formatDate(dt,Session.getScriptTimeZone(),"MMM dd, yyyy");//might want to change format
  const pos = { col: {}, idx: {} };
  hA.forEach((h, i) => { pos.col[h] = i   1; pos.idx[h] = i; });
  vs.forEach((r,i) => {
    let d = new Date(r[pos.idx['Date Column Header Name']]);
    let ds = Utilities.formatDate(d,Session.getScriptTimeZone(),"MMM dd, yyyy");//might wat to change format
    if(ds == dts) {
      sh.getRange(i   sr, pos.col['Data Column Header Name']  ).activate()
    }
  });
}

You can goto to

You can use buttons right on the spreadsheet but personally I think that they are a PITA.

CodePudding user response：

Just in case, here is the lite version (with a custom menu instead of a button):

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('