Google Scripts Trigger not firing

问题:

I'm struggling to get my script to auto-run at 6AM (ish). I have the trigger set up to run this script, "Time-Driven", on a "day timer" between "6-7 am". I'm getting no failure notifications (set up to email to me immediately), but the script isn't running. It works exactly as I want it to when I manually run it, but the whole point was to automate it, so I'm not sure what I am doing wrong here. I looked up other instances, and they seem to have been fixed by deleting and re-adding the triggers, but that doesn't solve the issue for me. Is it something in my script preventing an auto-run?

function getMessagesWithLabel() {

 var destArray = new Array();
 var label= GmailApp.getUserLabelByName('Personal/Testing');
 var threads = label.getThreads(0,2);

  for(var n in threads){
        var msg = threads[n].getMessages();
        var body = msg[0].getPlainBody();
        var destArrayRow = new Array();
        destArrayRow.push('thread has '+threads[n].getMessageCount()+' messages');
          for(var m in msg){
                     destArrayRow.push(msg[m].getPlainBody());
           }
  destArray.push(destArrayRow);           
        }
Logger.log(destArray);
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
if(ss.getLastRow()==0){sh.getRange(2,1).setValue('getMessagesWithLabel() RESULTS')};
sh.getRange(2,1,destArray.length,destArray[0].length).setValues(destArray);


}

回答1:

I'm not 100% sure, but the reason for this could be that during a trigger there's no "ActiveSpreadsheet" if the script isnt directly linked to spreadsheet (As the spreadsheet is closed). So you should try using:

var ss = SpreadsheetApp.openById(id); // id is the id of the spreadsheet
                                      // https://docs.google.com/spreadsheets/d/id_is_here/
var sh = ss.getSheetByName(name); // name of the actual sheet ("Sheet 1" for example)

Otherwise i see nothing wrong with your code (other than you using label.getThreads(0,2) which sets the maximum number of threads to be brought in to 2, but i assume that's intentional)

Also, you're setting 2,1 instead of what i assume needs to be 1,1 in

if(ss.getLastRow()==0){sh.getRange(2,1).setValue('getMessagesWithLabel() RESULTS')};

回答2:

The problem is due to the use of getActiveSheet as it retrieves the sheet displayed on the UI but when your time-driven trigger runs there isn't a sheet displayed on the UI.

Replace getActiveSheet by getSheetByName or better get the sheet by it's ID (for details see Get Google Sheet by ID?)

Reference:

• https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#getactivesheet