Google Spreadsheet select column current date

Question

I have a spreadsheet in which we put out planning. From column B till the last possible column there are dates on row A2. I tried to write a function that places your cursor on this cell. But I'm a little stuck and my knowledge of javascript is limited.

function onOpen() {
    getTodayRow();
};

function getTodayRow(){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('2014a');
    var rowContent = sheet.getRange('B:A2').getValues();
    var today = new Date().getDate();
    var val = 1;
    for(var n in rowContent)
    {
        if (new Date(rowContent[n][0]).getDate() == today)
        {
            val=Number(n)+1;break
        }
    }
    SpreadsheetApp.getActiveSheet().getRange('A1').setValue(val);
    // return val;
    // the +1 above is because arrays count from 0 and rows count from 1. (Number() is to avoid having 13+1=131 which is the default behavior unfortunately)
    sheet.setActiveCell(sheet.getRange(2, val)); //activate on right date
}

Is there anyone who can tell me where I went wrong?

Could you share a sample spreadsheet or explain how the sheet looks like. I was not able to understand "From column B till the last possible column there are dates on row A2" — Konstant, Apr 18 '14 at 13:26
It looks like this: https://dl.dropboxusercontent.com/u/26009685/ScreenShot035.jpg — S.J., Apr 23 '14 at 09:05
you have the same (dates) in rows too, right? I ask because everywhere you have used `rows` — Konstant, Apr 23 '14 at 09:08
so you just want the cursor to go the column with the date as part today's date. Nothing to do with the content of rows. — Konstant, Apr 23 '14 at 09:28

score 1 · Accepted Answer · answered Apr 24 '14 at 08:11

I found it and added a few more tweaks like not needing to enter the name of the sheet. You just make the sheet you use the first in line.

function onOpen() {
  getTodayRow();
};

function getTodayRow(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var rowContent = sheet.getRange(2, 2, 1, sheet.getLastColumn()).getValues()[0]; // [0] as we are using just one row i.e.
  var today = new Date();
  Logger.log(today);
  var val = 1;
  for(var n = 0; n < rowContent.length; n++) {
    var fDate = new Date(rowContent[n]);
    if (fDate.getDate() == today.getDate() && fDate.getMonth() == today.getMonth() && fDate.getFullYear() == today.getFullYear()) { // assuming its a number. If it's a formatted date you use if (new Date(rowContent[n] == today)
      val = n + 2 + 13; // + 2 because of arrays and the since the columns start from B => today
                        // + 2 + 13 to get today's date in the middle of the screen
      break;
    }
  }
  Logger.log(val);
  sheet.setActiveCell(sheet.getRange(2, val)); //activate on right date
}

thx for your help Konstant!

awesome. +1 for figuring it our yourself – Konstant Apr 24 '14 at 11:56 — Konstant, Apr 24 '14 at 11:56

score 0 · Answer 2 · answered Apr 23 '14 at 09:44

0

Here is the edited getTodayRow function that you could use. You can modify this as per your requirement.

function getTodayRow(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('2014a');
  var rowContent = sheet.getRange(2, 2, 1, sheet.getLastColumn()).getValues()[0]; // [0] as we are using just one row i.e.
  var today = new Date().getDate();
  var val = 1;
  for(var n = 0; n < rowContent.length; n++) {
    if (rowContent[n] == today) { // assuming its a number. If it's a formatted date you use if (new Date(rowContent[n] == today)
      val = n + 2; // + 2 because of arrays and the since the columns start from B
      break;
    }
  }
  Logger.log(val);
  SpreadsheetApp.getActiveSheet().getRange('A1').setValue(val);
  // return val;
  sheet.setActiveCell(sheet.getRange(2, val)); //activate on right date
}

answered Apr 23 '14 at 09:44

Konstant

2,179
16
32

I used (new Date(rowContent[n]) == today) but it still only goes to the first column in the row. Except for that it looks pretty good though :) – S.J. Apr 23 '14 at 10:24
my bad.. use `new Date(rowContent[n]).getDate()`. getDate function extracts the date part from the date. – Konstant Apr 23 '14 at 10:30
Glad it helped :) Since you are new to SO, I would request you to accept the answer. It's a good practice. You can read more about it [here](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) – Konstant Apr 23 '14 at 10:39
i can't upvote :s it says i need 15 rep. i did accept it though. but i still have a small issue too :s it doesn't scan the month, the only column selected is january – S.J. Apr 23 '14 at 10:46
Let me explain the working of `getDate`. If the date is `24-April-2014`, `getDate` will return `24`. If you have all the months and dates in the same sheet, you need to remove `getDate` for variable `today` as well as `rowContent[n]`. I assume you have all months and all dates in a single sheet. Try to log the value of variable `today` and `rowContent[n]`, you'll get there. – Konstant Apr 23 '14 at 10:53
Did everything you said, still doens't work. I have always hated javascript -__- – S.J. Apr 23 '14 at 11:15
did you log the values? any strange behavior? – Konstant Apr 23 '14 at 11:37
var today = new Date(); Logger.log(today); var val = 1; for(var n = 0; n < rowContent.length; n++) { if (new Date(rowContent[n]) == today) { // assuming its a number. If it's a formatted date you use if (new Date(rowContent[n] == today) val = n + 2; // + 2 because of arrays and the since the columns start from B break; } } the selected cell is A2 – S.J. Apr 23 '14 at 12:02
This is interesting.. I found [this](http://stackoverflow.com/questions/14363073/converting-google-spreadsheet-date-into-a-js-date-object). Basically Google Scripts date and JS Date are different and hence we are getting this. So this needs to be handled too. – Konstant Apr 23 '14 at 14:23

Google Spreadsheet select column current date

2 Answers2