How To Automatically Delete A Google Script Trigger That Runs Every DAy

Question

I have this trigger that I want to run every morning at 9am.

function copyFormulasToValues() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var source = ss.getSheets()[1];
var destination = ss.getSheets()[2];
var range = source.getRange("A1:C6015");
range.copyValuesToRange(destination, 1, 3, 1, 6030);

ScriptApp.newTrigger("myFunction")
.timeBased()
.atHour(9)
.nearMinute(1)
.everyDays(1)
.create();

The issue is, after a few days, my triggers fill up and it can't run anymore without me manually clearing them. I know there's a way to build in a function that essentially replaces the old trigger (by way of deletion) with the new trigger each day.

This post here gives an example of how to do it, but I just can't seem to get it to work. Would anybody be able to help me write this so that it deletes the existing trigger and replaces it with the new one each day? I'm a beginner so any help is much appreciated!!

Cooper raises a good point, maybe you just need a time based trigger - https://developers.google.com/apps-script/reference/script/clock-trigger-builder — iansedano, Feb 11 '21 at 14:01

score 0 · Answer 1 · answered Feb 11 '21 at 06:49

Both of these methods will create a single trigger and replace a single trigger. If you need to create and replace multiple triggers, the code would be slightly different.

Method 1 Creates a new trigger, and replaces any existing triggers that are using the same event handler ("myFunction" in your example). This assumes that you won't change the name of your handler function between executions and that you don't have multiple triggers running with the same handler. It is the weaker of the two methods for those reasons. However, it is slightly easier to understand since it isn't using any services that aren't already in your code sample.

// this is the function that gets called by your trigger
function myFunction() {
  Logger.log("I am an event handler"); 
} 

function replaceTrigger(handlerName) {
  const currentTriggers = ScriptApp.getProjectTriggers(); // get the projects triggers
  const existingTrigger = currentTriggers.filter(trigger => trigger.getHandlerFunction() === handlerName)[0]
  if (existingTrigger) ScriptApp.deleteTrigger(existingTrigger) // delete the existing trigger that uses the same event handler
  // create a new trigger 
  if (existingTrigger[0])
  ScriptApp.newTrigger(handlerName)
  .timeBased()
  .atHour(9)
  .nearMinute(1)
  .everyDays(1)
  .create();
}

replaceTrigger('myFunction')

Method 2 Store a trigger ID when you create a trigger, then use that ID to delete it. The benefit of using this method is that it will allow you to change the name of your handler function, and it won't have side effects on other triggers in your project. It uses the PropertiesService, which you may be unfamiliar with.

function myFunction() {
  Logger.log("I am an event handler"); 
}
function replaceTriggerWithId(handlerName) {
  // read the existing trigger id from script properties and delete it if it exists
  const currentTriggers = ScriptApp.getProjectTriggers(); // get the projects triggers
  const existingTriggerId = PropertiesService.getScriptProperties().getProperty('triggerId');
  if (existingTriggerId) {
    const existingTrigger = ScriptApp.getProjectTriggers().filter(trigger => trigger.getUniqueId() === existingTriggerId)[0];
    if (existingTrigger) ScriptApp.deleteTrigger(existingTrigger)
  }
  // create a new trigger and store its id in script properties
  const triggerId =
   ScriptApp.newTrigger(handlerName)
    .timeBased()
    .atHour(9)
    .nearMinute(1)
    .everyDays(1)
    .create()
    .getUniqueId()
  PropertiesService.getScriptProperties().setProperty('triggerId', triggerId);
}

replaceTriggerWithId('myFunction');

You could set replaceTrigger or replaceTriggerWithId to run on a time trigger. When they are triggered they will delete any triggers that were defined with the same handler name (method 1), or whichever trigger was last defined by the trigger handler (method 2). After deleting the old trigger, they will create a new one.

References:

ScriptApp

PropertiesService

Ahlicoding.com · Answer 2 · 2022-07-21T13:42:51.763

@RayGun 's answer method 2 seems Error , so I fixed up a little bit:

 function myFunction() {
      Logger.log("I am an event handler"); 
    }

function replaceTrigger(handlerName) {
  const currentTriggers = ScriptApp.getProjectTriggers(); // get the projects triggers
  const existingTrigger = currentTriggers.filter(trigger => trigger.getHandlerFunction() === handlerName)[0]
  if (existingTrigger) ScriptApp.deleteTrigger(existingTrigger) // delete the existing trigger that uses the same event handler
  // create a new trigger 
  //if (existingTrigger[0])  Yes, I commented this
    ScriptApp.newTrigger(handlerName)
   .timeBased()
   .atHour(9)
   .nearMinute(1)
   .everyDays(1)
   .create()
   .getUniqueId()
  console.log('Create New Triger:...');
}

function main(){
  replaceTrigger('myFunction')
}

Don't forget: you must Run 'main' function , and then 'myFunction' will always be created only once, forever...

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Jul 25 '22 at 19:16

How To Automatically Delete A Google Script Trigger That Runs Every DAy

2 Answers2