Time trigger for script

[Ryan Schlabach]

Hello everyone, I have been trying to get a time trigger to activate a script in a google sheet. The copyDataToNextEmptyCell() script works fine when triggered in the apps script but I have not been able to get it to work on a time trigger. 

I'm new to writing any kind of code and need a little help figuring this one out. 

This is where I am at at the moment. 

Appreciate any help on this. 

[Laurie Nason]

Couple of things:

1. Is there any reason that you are creating the trigger from code? rather than doing it from the appscript interface?
2. When you are running a trigger, you will need to specify the exact sheet that you are expecting the script to work on - I don't think that the concept of getActiveSheet() works in this context.

Laurie

--
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/945c626b-e8c3-4903-b3b3-6ceefcf5e62cn%40googlegroups.com.

[Ryan Schlabach]

When running the script with an app script trigger it will not run on a time based trigger. It will run with an edit or change trigger but when set with time it says it has run successfully but does not change any data on the sheet. 

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/SRHTCUiFPAw/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/CA%2BA7ZWL7ujj2uhSeq_AHSKQuxpd_CFVxPvkr6MC_dD1%3DDr4-CA%40mail.gmail.com.

--

Ryan Schlabach

Panhandle Door Inc.

208-267-1973

P.O. Box 1696

Bonners Ferry, ID. 83805

[Edward Friedman (Eddie)]

To expand on what Laurie said, when the script is run via a time-driven trigger, it may be that you cannot use SpreadsheetApp.getActiveSheet() to get the sheet that you're looking for because there is no active sheet when it is run on a time trigger. With the edit and change triggers, a user has the spreadsheet open and is looking at (and made changes/edits to) the active sheet, so the code will work in those cases. However, Google doesn't have your spreadsheet open to a specific sheet when the time trigger goes off.  

To confirm that this is the problem, you can check your execution log for errors that will say something to the effect of "sheet not defined" or "getActiveSheet is not a valid method of undefined". 

To fix this, you'll need to explicitly name the sheet you're trying to get. You can do that with the code below:

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); //this should work even in the time-based trigger context because it is a container-bound script

var sheet = spreadsheet.getSheetByName('PUT THE NAME OF YOUR SHEET IN HERE');

If, for some reason, SpreadsheetApp.getActiveSpreadsheet() doesn't work, you could also use SpreadsheetApp.openById() (ref))or SpreadsheetApp.openByUrl() (ref).

[Ryan Schlabach]

That helped me wrap my head around it. Used the .getActiveSpreadsheet(); and getSheetByName();  and its running good. 

Thanks Laurie and Eddie for the help on this. 

[Edward Friedman (Eddie)]

I'm glad that helped!

[Laurie Nason]

You're welcome! (and thanks Eddie for explaining the issue much better than me!)

Laurie

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ca8f82dd-5fd8-462f-9d6f-3049efaf427bn%40googlegroups.com.