Open google spreadsheet at date

Question

I have created a google spreadsheet. On the column A in row 1 I have used "vastzetten" (that's in dutch). The first column and row I have blocked. When I go with the cursor I can change the date and the rest of the sheet dissapeared after the block. (Look the images). Is there a way when I start the sheet it selects the date of the day then moves the rest of the sheet after the titleblock? I hope that somebody knows what I mean.

Rubén Rubén · Accepted Answer · 2016-10-13T04:35:31

You could use an installable trigger to hide columns and/or to activate the range corresponding to the current date.

For instructions on how to set an installable trigger see https://developers.google.com/apps-script/guides/triggers/installable

The following code assumes that the column headers are values of date type. If the current date is found, then the columns at the left will be hidden and the the cell below the corresponding header will be activated.

function myFunction(){
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheets()[0];
  var tz = ss.getSpreadsheetTimeZone();
  var today = Utilities.formatDate(new Date(), tz, 'YYYY-MM-dd')
  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  for(var i = 0;i<= headers.length;i++){
    if(headers[i] instanceof Date){
      if(today == Utilities.formatDate(headers[i], tz, 'YYYY-MM-dd')){
        sheet.hideColumns(2,i-1);
        sheet.getRange(2, i+1).activate();
        break;
      }
    }
  }
}

Open google spreadsheet at date

1 Answers