CODEWITHSUNDEEP
google-sheetsgoogle-apps-script-editor
NickLatu
NickLatu

Reputation: 1

How to link button to current date with a script

I've copied a script and changed it according to my own spreadsheet. But I havent reached the point yet to have the knowledge what to change in the script.

The button called "Vandaag" is what's suppose to send me to the exact date.

My current Google Spreadsheet

  function activateToday() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s=ss.getActiveSheet();
  var lr = s.getLastRow();
  var range = s.getRange(4, 2,1,lr).getValues();
   for(var i=0;i<range[0].length;i++){
    if(range[0][i].getDate()+ range[0][i].getMonth()+1 ==new  Date().getDate()+new Date().getMonth()+1 ){
     s.getRange(1,i+1).activate();
      break;
    }}}

Upvotes: 0

Views: 1046

Answers (1)

Torey Price
Torey Price

Reputation: 397

If you are open to using a hyperlink this can be done without a script. In Google sheets you can jump to a specific cell by hyperlinking a reference to the grid and range:

=HYPERLINK("#gid=0&range=A19","Jump to Cell")

If you would like the hyperlink to automatically reference today's date and find the cell, you can use the following formula in cell A1:

=HYPERLINK(SUBSTITUTE("#gid=0&range="&ADDRESS(MATCH(Today()+1,A2:A,0),1, 1),"$",""),"Jump to date")

The Match function finds today's date in the specified range, and the address function returns the cell reference to be added to the link.

Note: If your reference range starts below row 1, you will need to add the number of rows not referenced back to the today function. In the example above, since I am leaving out cell A1 I added 1.

Hope this helps

Upvotes: 1

Related Questions