Is it possible to run a function everyday with time-driven trigger without having an active spreadsheet?

Question

I have a function that updates cells and sends emails when called. I would like to run it everyday without having to open the spreadsheet (the getActiveSpreadsheet() method).

So I tried to call it using server side method openById(). But the function doesn't fire when the spreadsheet is not open.

Is there a way to run it with the spreadsheet closed?

Here is the code:

function updateNote() {
var ss = SpreadsheetApp.openById("MySpreadsheetID");
//do things
};`

function createTimeDrivenTriggers() {
// Trigger fonction everyday at 10.35am   
  ScriptApp.newTrigger('updateNote')
      .timeBased()
      .atHour(10)
      .nearMinute(35)
      .everyDays(1)
      .create();
};

I know you created the trigger from code, but does the trigger show up in the trigger menu? https://blog.rudolphk.co.za/hacky-tip-use-google-sheets-to-run-scheduled-recurring-cronjobs-4259f5a41345 — frithjof, Oct 03 '19 at 10:10
Make sure you've cleared all filters on the top. Also, see whether you have a trigger at Edit> Current project triggers — TheMaster, Oct 03 '19 at 10:11
It's possible! Review the active triggers list. I usually prefer to create the triggers directly in the G Suite Developer Hub... — jbra95, Oct 03 '19 at 10:54

score 2 · Accepted Answer · edited Nov 05 '21 at 05:52

Answer:

You can create an installable trigger to run every day without needing to have your Spreadsheet open.

Details:

Using a time-based installable trigger you can have your function run daily, though as per the documentation:

The time may be slightly randomized — for example, if you create a recurring 9 a.m. trigger, Apps Script chooses a time between 9 a.m. and 10 a.m., then keeps that timing consistent from day to day so that 24 hours elapse before the trigger fires again.

If this is not a problem for you, then you can do this from the Apps Script Editor for your project - follow the path Edit > Current project's triggers which will open the triggers page for the project in a new tab.

In the bottom left, click on the + Add Trigger button to bring up the Add Trigger modal - use hte following properties:

Choose which function to run: updateNote()
Choose which deployment should run: Head
Select event source: Time-driven
Select type of time based trigger: Day timer
Select time of day: 10am to 11am

If running at exactly 10:35 is important to you:

You can follow the same steps as above, but with slightly different properties:

Choose which function to run: createTimeDrivenTriggers()
Choose which deployment should run: Head
Select event source: Time-driven
Select type of time based trigger: Day timer
Select time of day: 9am and 10am

With this, you'll be creating your time-based trigger to run at 10:35 on a daily basis, always running before the trigger needs to fire.

Is it possible to run a function everyday with time-driven trigger without having an active spreadsheet?

1 Answers1

Answer:

Details:

References:

Linked