NeuroAgent

How to Set Up Daily Google Sheets Triggers in Google Apps Script

Fix Google Apps Script daily triggers: Set timezone, use nearMinute(), debug and fix code for reliable daily execution.

#automation-tools#google-apps-script#google-sheets-automation#google-workspace#script-triggers#time-based-triggers#timezone-settings

Is my Google Sheets trigger correctly configured for daily script execution?

I created a script in Google Sheets that should automatically output a cell value every day. The script seems to work correctly, but the trigger set to run daily at 9:00 doesn’t activate. Can you help me determine what’s wrong?

Here’s the script code:

javascript
/**
 *
 */
function createTrigger() {
  ScriptApp.getProjectTriggers().forEach(
    (trigger) =>
      trigger.getHandlerFunction() === 'saveData' &&
      trigger.getEventType() === ScriptApp.EventType.CLOCK &&
      (ScriptApp.deleteTrigger(trigger) ||
        console.info(`Tirgger ${trigger.getUniqueId()} was deleted`))
  );
  // every minutes for testing
  // ScriptApp.newTrigger('saveData').timeBased().everyMinutes(1).create();
  // at 17 o'clock every days
  ScriptApp.newTrigger('saveData').timeBased().atHour(9).everyDays(1).create();
}

/**
 *
 */
function saveData() {
  const book = SpreadsheetApp.openById(
    '1a32sJMp-zd7boprGAqAyw68fWpxtuzQmowdjjHTVwQk'
  );
  const sheet = book.getSheetByName('Лист2');
  const value = sheet.getRange('M4').getValue();
  book.getSheetByName('Лист5').appendRow([new Date(), value]);
  console.info(`saveData was called successful`);
}

What needs to be fixed to make the script run automatically every day at the specified time?

NeuroAgent

The main issue is the lack of timezone configuration and the nearMinute() method in the trigger configuration. Your script doesn’t run automatically because the trigger uses the system timezone without specifying an exact minute for execution, causing time discrepancies.

Contents

Main Issues with Triggers in Google Apps Script

Your code contains several common errors that prevent the trigger from working properly:

  1. Missing timezone configuration - Triggers use the Google Apps Script system timezone, which may differ from your spreadsheet’s timezone
  2. No nearMinute() method - Without specifying an exact execution minute, the trigger may fire with a deviation of up to ±15 minutes
  3. Incorrect trigger deletion logic - Your deletion function may not work properly

As noted in the Google documentation, an invalid timezone string causes the script to fail during execution.

Setting Timezone for Triggers

The most common issue with triggers is timezone mismatch. Google Sheets and Google Apps Script may use different timezones by default.

javascript
// Incorrect - without timezone specification
ScriptApp.newTrigger('saveData').timeBased().atHour(9).everyDays(1).create();

// Correct - with timezone specification
ScriptApp.newTrigger('saveData')
  .timeBased()
  .atHour(9)
  .everyDays(1)
  .inTimezone('Europe/Moscow') // Specify your timezone
  .create();

It’s important to use correct timezone strings in IANA format (e.g., ‘Europe/Moscow’, ‘America/New_York’). As mentioned on Stack Overflow, the inTimezone() method is essential for precise timing configuration.

Using the nearMinute() Method

The nearMinute() method is critically important for precise trigger execution time. Without it, Google Apps Script adds a random deviation of up to ±15 minutes.

javascript
// Incorrect - without minute specification
ScriptApp.newTrigger('saveData')
  .timeBased()
  .atHour(9)
  .everyDays(1)
  .inTimezone('Europe/Moscow')
  .create();

// Correct - with specific minute
ScriptApp.newTrigger('saveData')
  .timeBased()
  .atHour(9)
  .nearMinute(0) // Execute exactly at 9:00
  .everyDays(1)
  .inTimezone('Europe/Moscow')
  .create();

According to the official documentation, the nearMinute() method determines the execution minute (plus or minus 15 minutes). If this method is not called, a random minute value is used.

Checking and Debugging Existing Triggers

Your createTrigger() function has a logical error in the deletion condition. The current implementation may not work correctly:

javascript
// Problematic code
ScriptApp.getProjectTriggers().forEach(
  (trigger) =>
    trigger.getHandlerFunction() === 'saveData' &&
    trigger.getEventType() === ScriptApp.EventType.CLOCK &&
    (ScriptApp.deleteTrigger(trigger) ||
      console.info(`Tirgger ${trigger.getUniqueId()} was deleted`))
);

Corrected version:

javascript
function createTrigger() {
  // Get all project triggers
  const triggers = ScriptApp.getProjectTriggers();
  
  // Delete existing triggers for saveData
  triggers.forEach((trigger) => {
    if (trigger.getHandlerFunction() === 'saveData' && 
        trigger.getEventType() === ScriptApp.EventType.CLOCK) {
      ScriptApp.deleteTrigger(trigger);
      console.info(`Trigger ${trigger.getUniqueId()} was deleted`);
    }
  });
  
  // Create new trigger
  ScriptApp.newTrigger('saveData')
    .timeBased()
    .atHour(9)
    .nearMinute(0)
    .everyDays(1)
    .inTimezone('Europe/Moscow') // Specify your timezone
    .create();
  console.info('New trigger created successfully');
}

Solution: Corrected Script Code

Here’s the complete corrected version of your script addressing all issues:

javascript
/**
 * Creates or updates a trigger for daily script execution
 */
function createTrigger() {
  // Delete existing triggers for saveData
  const triggers = ScriptApp.getProjectTriggers();
  triggers.forEach((trigger) => {
    if (trigger.getHandlerFunction() === 'saveData' && 
        trigger.getEventType() === ScriptApp.EventType.CLOCK) {
      ScriptApp.deleteTrigger(trigger);
      console.info(`Trigger ${trigger.getUniqueId()} was deleted`);
    }
  });
  
  // Create new trigger at 9:00 daily with timezone specification
  ScriptApp.newTrigger('saveData')
    .timeBased()
    .atHour(9)
    .nearMinute(0) // Exact time without deviation
    .everyDays(1)
    .inTimezone('Europe/Moscow') // IMPORTANT: Specify your timezone
    .create();
  console.info('New trigger created for 9:00 daily');
}

/**
 * Main function for saving data
 */
function saveData() {
  try {
    const book = SpreadsheetApp.openById(
      '1a32sJMp-zd7boprGAqAyw68fWpxtuzQmowdjjHTVwQk'
    );
    const sheet = book.getSheetByName('Лист2');
    const value = sheet.getRange('M4').getValue();
    book.getSheetByName('Лист5').appendRow([new Date(), value]);
    console.info(`saveData executed successfully at ${new Date()}`);
  } catch (error) {
    console.error(`Error in saveData: ${error.message}`);
    // Send error notification
    MailApp.sendEmail('your-email@example.com', 'Google Apps Script Error', 
                     `Error in saveData: ${error.message}\nStack: ${error.stack}`);
  }
}

Additional Checks and Recommendations

  1. Consumer quota check: If you have a consumer Google account, the total execution time limit for all triggers is 90 minutes per day. As noted on Stack Overflow, exceeding this limit can cause executions to be missed.

  2. Spreadsheet timezone check: Ensure that the timezone in your Google Sheets settings matches the one specified in the trigger. You can change the spreadsheet timezone in File → Spreadsheet settings.

  3. Trigger testing: For testing, use a minute trigger:

javascript
// Temporary trigger for testing
ScriptApp.newTrigger('saveData').timeBased().everyMinutes(1).create();

  1. Execution log check: Check the script execution logs in the Execution → Triggers section of the Apps Script editor.

  2. Error handling: Add error handling to the saveData() function to track issues.

  3. Permission check: Ensure the script has necessary permissions to access the spreadsheet.

Sources

  1. Class ClockTriggerBuilder | Apps Script | Google for Developers
  2. Setting the Timezone for timed triggers - Stack Overflow
  3. Time-Driven triggers not working properly - Stack Overflow
  4. Google Apps Script Simple Time Triggers - YouTube
  5. Change the time zone of your Google Apps Script Project - Yagisanatode

Conclusion

The main issues in your script were related to missing timezone configuration and the nearMinute() method. To fix them:

  1. Add the .inTimezone('Europe/Moscow') method with your specified timezone
  2. Use the .nearMinute(0) method for exact execution time
  3. Correct the logic for deleting existing triggers
  4. Add error handling to the main function
  5. Check execution limits for your account type

After these changes, the trigger should correctly run the script every day at 9:00 AM in the specified timezone.

How to check if a Google Apps Script trigger is working?Why does a Google Apps Script trigger not run at the specified time?How to change the timezone for a Google Apps Script trigger?How to delete and create a new trigger in Google Apps Script?What execution limits exist for Google Apps Script triggers?How to add error handling to a function called by a trigger?
Ask NeuroAgent