Open a Google sheet to a specific place based on today's date

1
votes

I am a relative newbie. I have a long list of expenses in a Google spreadsheet and I would like for it to jump to the appropriate place in this list based on the current month when I open the sheet. Each month has 200 rows, so I would like for it to do something like:

   = MONTH(TODAY())*200

but this doesn't work inside the script. I have tried pulling it from a cell that performs this function, but I don't know how to do that. In the example below, I can jump to September because I defined 

   var monthrow = 1800

But how to do it for the rest of the months based on today's date? Thank you for your help!

   function onOpen() {

   goToSheet2b()
   }

   function goToSheet2b() {

   var monthrow = 1800
   goToSheet("Expenses", monthrow, 2);
   }

   function goToSheet(sheetName, row, col) {
   var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
   SpreadsheetApp.setActiveSheet(sheet);
   var range = sheet.getRange(row, col)
   SpreadsheetApp.setActiveRange(range);
2
google-apps-scriptgoogle-sheets

2 Answers

0
votes

If I understood you correctly and if every month does indeed have 200 rows starting with January then the following should work for you. I've commented the code as well. Let me know if you have any questions.

function onOpen() {
  var d =  new Date(new Date().getTime());
  var month = d.getMonth();  //.getMonth() returns values form 0 (Jan) to 11 (Dec)
  var monthrow = month*200 + 1; //we add the 1 because if it is Jan than 0*200 = 0 + 1 = 1 or row number 1 and for the rest it will be the start of that month
  goToSheet("Expenses", monthrow, 2);
}
0
votes

Thank you!

I had to tweak order of operations. Here's the full thing that worked for me:

function onOpen() {

   goToSheet2b()
   }

   function goToSheet2b() {

   var d =  new Date(new Date().getTime());
   var month = d.getMonth();  //.getMonth() returns values form 0 (Jan) to 11 (Dec)
   var monthrow = (month+1)*200;//we add the 1 because if it is Jan than 0*200 = 0 + 1 = 1 or row number 1 and for the rest it will be the start of that month
   goToSheet("Expenses", monthrow, 2);

   }

   function goToSheet(sheetName, row, col) {
   var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
   SpreadsheetApp.setActiveSheet(sheet);
   var range = sheet.getRange(row, col)
   SpreadsheetApp.setActiveRange(range);}