Get sheet to open on today's date

[Fraser Edmond]

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]

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]

Try casting the value as date and compare like below 

 var sheetDate = new Date(v[i][0]);

    if (sheetDate.getTime() >= d) {

[Fraser Edmond]

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...@gmail.com

[Fraser Edmond]

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]

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]

I understand, this has done the trick! Thank you all, fantastic!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/03ec3f7a-4c23-4d32-baf6-6121ab5199a1n%40googlegroups.com.

[Fraser Edmond]

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')

onOpen

@ Code.gs:3

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

Fraser

[Ed Sambuco]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/03ec3f7a-4c23-4d32-baf6-6121ab5199a1n%40googlegroups.com.

--

Fraser Edmond

07788772288

fraser...@gmail.com

--

Fraser Edmond

07788772288

fraser...@gmail.com

--

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]

It looks like you don't have a sheet called Rota 2023.

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.

[Jenny Lo]

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]

Morning all,

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

Thanks for the help anyway.

Fraser.

[Dave Roeser]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAGWfUf36Ktb3YdwcvF_xnPhf4A%3Ds9BG8ZPrR8bD-zVZeFh%3DOWg%40mail.gmail.com.

[Fraser Edmond]

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.

[Dave Roeser]

Hi Fraser,

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

Take care,

Dave

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAGWfUf2uNhNuM%3DAB1R0_es1Gcpzd4XZz_tu36RnVmfVKAiqAeA%40mail.gmail.com.

[Fraser Edmond]

Ah fantastic, I was using the spreadsheet name rather than the sheet name! 

Brilliant, thank you Dave!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CADO6D4OGQ%2B9o%3DveK_3Ov-vGYmvjD_B4r7NsVw2iZvGegMLZn9A%40mail.gmail.com.