1

using the today function I have today's date in cell C4. The range of data to query is G4:af250.

My script is:

onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var dates = sheet.getRange(G4:AF250).getValues();

  var today = C4();
    }
Goto 'today'()  }
}`

I am getting error after error, instead of going to the cell in the range with today's date.

Help.

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
user3166079
  • 11
  • 2

1 Answers1

1

You are starting to get a handle on the syntax of JavaScript, so keep at it. Maybe try working your way through the learning path at CodeAcademy, to help hammer it in.

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var dateRange = sheet.getRange('G4:AF250');
  var dates = dateRange.getValues();
  var today = sheet.getRange('C4').getValue();

  // Find today in the dateRange
  var found = false;
  var row=dates.length-1;
  while (row >= 0 && !found) {
    var col=dates[row].length-1;
    while (col>=0 && !found) {
      // If the array cell matches 'today', stop looking.
      if (!(today > dates[row][col]) && !(today < dates[row][col])) {
        found = true;
        break;
      }
      col--;
    }
    if (!found)
      row--;
  }

  // Use search row & col to offset to a cell relative to dateRange start
  var active = dateRange.offset(row, col,1,1);

  // Move user's cursor to today's date, if it is found in dateRange
  if (found)
    sheet.setActiveRange(active);
}

Edit: Explanation of some key points.

This function searches the range G4:AF250 for a match to the content of C4. It's expected that the content of that cell is =TODAY(). If that assumption is correct, then the result should be that the user's cursor will be placed in the matching cell, if it exists. If no match is found, the cursor will be left where it started (A1).

Date Matching is tricky in Javascript. You can't just test for equality, so this code tests whether today is before or after the date in a target cell; if it's not before or after, then it must be the same. (There are other ways, see Compare two dates with JavaScript.)

There are no tests included here to ensure that the values being read from the spreadsheet are, in fact, Date objects. If you've got strings or numbers in C4 or the dateRange, then matches won't be made. For example, the date 1/4/2014 is not equal to the string "1/4/2014".

To test for matches, our code must loop through two dimensions of the dateRange. We could do this with for loops, from 0 up. In this case, though, we start at the maximum and work back down. This choice was handy for the definition of the active cell, later.

As always, when dealing with Spreadsheets, you need to be aware that Spreadsheet row and column references start from 1, while Javascript arrays start at 0. This means that once we read a range of values using getValues(), we have a two-dimensional array that is 0..(maxRows-1) by 0..(maxCols-1). If you eliminate the -1, you will end up trying to reference a non-existent value, and generate an error.

Community
  • 1
  • 1
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • Thank You very much for your help. You are 110% correct I am struggling with the java syntax. This code works great but it takes me to 2/8/2014 today. Do I need to remove the '-1' from the .length? – user3166079 Jan 07 '14 at 14:14
  • Tried a few "self-edits" with no luck. I am definitely missing some understanding of how the code is working. – user3166079 Jan 07 '14 at 14:20
  • Regardless of the date in the today cell, the routine always leaves the cursor at cell AD250. It should be stopping at cell V75, where 01/07/2014 is. Thanks for your help! – user3166079 Jan 07 '14 at 16:34
  • There was a bad cell reference, should be C3, not C4. Stupid error. I can share spreadsheet with you if you give me your email? – user3166079 Jan 08 '14 at 20:35
  • Does it matter if the dates are atop columns that contain some rows with text fields, some numeric fields, some merged fields? – user3166079 Jan 08 '14 at 20:37
  • Unfortunately after the change it is not working. The cursor moves to a new cell, but not the right one. – user3166079 Jan 08 '14 at 22:33
  • Old Code: if (!(today > dates[row][col]) && !(today < dates[row][col])) { New Code: if (String(dates[row][col]) == String(today)){ – user3166079 Jan 09 '14 at 13:33
  • The above change was the key to making the script do what I wanted. Thanks for your help! Now if I could figure out how to make the script work when I open the sheet on my iphone, too, I would be 100% set. Thanks again. – user3166079 Jan 09 '14 at 13:34