Get sheet to open on today's date

182 views
Skip to first unread message

Fraser Edmond

unread,
Oct 3, 2022, 12:52:20 PM10/3/22
to Google Apps Script Community
Hi all, I have found some other conversations on this but nothing that is still open? I'm trying to get a sheet to automatically open on today's date, however I'm getting an error code (see below).

Column A on the 'Rota' sheet is set to a date format.

Any help greatly appreciated- I am new to this!

Thanks,
Fraser.

function onOpen() {
var s = SpreadsheetApp.getActive().getSheetByName("Rota");
var v =s.getDataRange().getValues();
var d = new Date().getTime();
for (var i = 0, length = v.length; i < length; i++) {
if (v[i][0].getTime() >= d) {
s.getRange(i + 1, 1).activate();
break;
}
}
}

Error
TypeError: v[i][0].getTime is not a function

CBMServices Web

unread,
Oct 3, 2022, 1:02:22 PM10/3/22
to google-apps-sc...@googlegroups.com
The error is indicating that the value is not a date.

Check your spreadsheet and ensure that all values in Column A are truly dates and not strings. You can do that by selecting the whole column and then change date format to show something different. If some of the values in column A do not change to the new format, then you found the culprit.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/50bf067a-b6e8-48bb-9253-7ea94beb76a9n%40googlegroups.com.

Messi Jini

unread,
Oct 3, 2022, 1:05:33 PM10/3/22
to Google Apps Script Community
Try casting the value as date and compare like below 

 var sheetDate = new Date(v[i][0]);
    if (sheetDate.getTime() >= d) {

Fraser Edmond

unread,
Oct 3, 2022, 1:27:08 PM10/3/22
to google-apps-sc...@googlegroups.com
Thanks for the advice, I've tried this but sadly no luck. The first row is frozen, that wouldn't affect the script would it?

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/8bb07EWm_cU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3VfVSC1%2B1J3gv2qCE1zjfLcoyCm7M%2BG7NVhknobavuaFQ%40mail.gmail.com.


--
Fraser Edmond
07788772288

Fraser Edmond

unread,
Oct 3, 2022, 1:27:40 PM10/3/22
to google-apps-sc...@googlegroups.com
Excuse my ignorance but I don't understand this I'm afraid!

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/8bb07EWm_cU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/09b09065-8b65-4fbe-b12e-5e9335fa517bn%40googlegroups.com.

Messi Jini

unread,
Oct 3, 2022, 1:42:24 PM10/3/22
to Google Apps Script Community
if the first row is frozen, then start the variable i value from 1 like below 

for (var i = 1, length = v.length; i < length; i++) {

Fraser Edmond

unread,
Oct 4, 2022, 3:49:12 AM10/4/22
to google-apps-sc...@googlegroups.com
I understand, this has done the trick! Thank you all, fantastic!

Fraser Edmond

unread,
Jan 16, 2023, 6:10:26 AM1/16/23
to google-apps-sc...@googlegroups.com
Good morning,

I've coped the spreadsheet over for the new year but now I'm experiencing an issue - see below:

Script: New spreadsheet is called 'Rota 2023'

function onOpen() {
var s = SpreadsheetApp.getActive().getSheetByName("Rota 2023");
var v =s.getDataRange().getValues();
var d = new Date().getTime();
for (var i = 3, length = v.length; i < length; i++) {
if (v[i][0].getTime() >= d) {
s.getRange(i + 1, 1).activate();
break;
}
}
}

Error:

Error
TypeError: Cannot read properties of null (reading 'getDataRange')

Thanks again for your help and patience with a complete novice!

Fraser

Ed Sambuco

unread,
Jan 16, 2023, 11:13:46 AM1/16/23
to google-apps-sc...@googlegroups.com
Variable s is null, so you do not have a handle to a google sheet. Check sheet name.
To unsubscribe from this group and all its topics, send an email to google-apps-script-community+unsub...@googlegroups.com.


--
Fraser Edmond
07788772288


--
Fraser Edmond
07788772288

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAGWfUf3yJnoyZ4-ZGDK%3DsOqkQOr3NPMCWkvCLxZ2KXcqhZ_Q%2BA%40mail.gmail.com.

CBMServices Web

unread,
Jan 16, 2023, 2:12:15 PM1/16/23
to google-apps-sc...@googlegroups.com
It looks like you don't have a sheet called Rota 2023.


Jenny Lo

unread,
Jan 18, 2023, 7:08:51 AM1/18/23
to Google Apps Script Community
Sorry to jump in on this, how would you amend the script if column B contained the dates? I think I am reading 'if  (v[i][0].getTime() >= d) {' right- would it still jump to the previous day of today? Say for example, my sheet is a day by day entry so tomorrow upon opening it for the first time it won't have an entry for tomorrow date. 

Fraser Edmond

unread,
Jan 27, 2023, 5:21:29 AM1/27/23
to google-apps-sc...@googlegroups.com
Morning all,

The sheet is called Rota 2023, I'm at a loss.

Thanks for the help anyway.
Fraser.

Dave Roeser

unread,
Jan 27, 2023, 4:48:27 PM1/27/23
to google-apps-sc...@googlegroups.com
Hi Fraser,

Would you mind posting the Sheet?

Thanks,
Dave

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

Fraser Edmond

unread,
Jan 30, 2023, 4:02:49 AM1/30/23
to google-apps-sc...@googlegroups.com
Hi,

Sure, I've made a copy of it if that doesn't defeat the purpose! 

Thanks very much.

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/8bb07EWm_cU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CADO6D4N484vYzCm8oFCZceRjj_gZ_X2F-zcRQCAJHaohpBKFOQ%40mail.gmail.com.
Copy of Rota 2023 (1).xlsx

Dave Roeser

unread,
Jan 30, 2023, 11:03:53 AM1/30/23
to google-apps-sc...@googlegroups.com
Hi Fraser,

When I open the copy I see the name of 'Rota Q1 2023' not 'Rota 2023".

Take care,
Dave

Fraser Edmond

unread,
Jan 30, 2023, 1:23:19 PM1/30/23
to google-apps-sc...@googlegroups.com
Ah fantastic, I was using the spreadsheet name rather than the sheet name! 

Brilliant, thank you Dave!

Reply all
Reply to author
Forward
0 new messages