Change from manually triggered script to time-driven trigger using Google Apps script

Question

I have an app script for Google Sheets that works when I trigger it manually, but I would like it to be time driven, running automatically once an hour. I've tried setting that up using the Apps Script UI, and it looked like this:

Trigger

But I consistently get this error message:

Exception: Cannot call SpreadsheetApp.getUi() from this context. at unknown function

I also tried writing the time trigger into the script, but kept getting an error. Here's the current script, which does work fine when I trigger it manually.

    var ui = SpreadsheetApp.getUi();
    function onOpen(e){
      
      ui.createMenu("Gmail Manager").addItem("Get Emails by Label", "getGmailEmails").addToUi();
      
    }
    
    function getGmailEmails(){
      var label = GmailApp.getUserLabelByName('EmailsToBeExported');
      var threads = label.getThreads();
      for(var i = threads.length - 1; i >=0; i--){
        var messages = threads[i].getMessages();
        for (var j = 0; j <messages.length; j++){
          var message = messages[j];
          extractDetails(message);
        }
        threads[i].removeLabel(label);
      }
    }
    
    function extractDetails(message){
      var dateTime = message.getDate();
      var subjectText = message.getSubject();
      var senderDetails = message.getFrom();
      var bodyContents = message.getPlainBody();
      
      var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      activeSheet.appendRow([dateTime, senderDetails, subjectText, bodyContents]);
    }`

Answer 1

You can also maintain the usability by altering the code like so:

try {
  const UI = SpreadsheetApp.getUi(); 
} catch (err) {
  const UI = {
    alert: function() {return;},
    prompt: function() {return true;},
    button: {YES: false},
    ButtonSet: {YES_NO: false}
  }
}

You'll have to set parameters for the UI manually, but what it does it makes it so that your code runs and handles things as you expect.

I specifically put the prompt as true and the buttons as false. That way, if I am checking if they are equal to a specific button, it will always evaluate false.

e.g.

var result = UI.prompt("Some title", "Some question", UI.ButtonSet.YES_NO);
if (result == UI.Button.YES) {//always false
  //This part does not evaluate
}

You'd have to make sure that you can do this consistently throughout your script. You may like to change the assignments of the values (depending on your conditional statements) to strings, numbers, or other unique things, or change your conditional statements to include "===" instead, etc. The prompts are the trickiest ones. With this change in code, you can have the UI prompts and alerts when you run the script from within Google Sheets. It will skip them when you run it from outside, such as timed executions or other things that would activate your script externally.

Answer 2

You can not use SpreadsheetApp.getUi() from a time driven trigger. Change you triggers to: