google sheets - App Script runs fine from menu or editor but not from time-driven trigger - Stack Overflow

The following script will hide any rows on a sheet if Col Q has "YES" in it.

function AutoHidingTechnique() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.showRows(1, sheet.getLastRow());
  var range = sheet.getRange("Q1:Q" + sheet.getLastRow());
  var values = range.getValues();
  var numRows = range.getNumRows();

  for (var i = 0; i < numRows; i++) {
    if (values[i][0] === "YES") {
      sheet.hideRows(i+1);
    }

The script used to work perfectly (for months) on an hourly time-driven trigger.

Lately the execution log affirms the script is still working fine, but the rows are not being hidden anymore.

When I run the script manually, however from the menu or editor, it does hide the rows.

The fact that the time-driven script used to work perfectly and the script still works perfectly when ran manually leaves me at a loss as to how get the time-driven hourly trigger to actually do what it says it is doing.

Why is that and how can I fix it?

The following script will hide any rows on a sheet if Col Q has "YES" in it.

function AutoHidingTechnique() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.showRows(1, sheet.getLastRow());
  var range = sheet.getRange("Q1:Q" + sheet.getLastRow());
  var values = range.getValues();
  var numRows = range.getNumRows();

  for (var i = 0; i < numRows; i++) {
    if (values[i][0] === "YES") {
      sheet.hideRows(i+1);
    }

The script used to work perfectly (for months) on an hourly time-driven trigger.

Lately the execution log affirms the script is still working fine, but the rows are not being hidden anymore.

When I run the script manually, however from the menu or editor, it does hide the rows.

The fact that the time-driven script used to work perfectly and the script still works perfectly when ran manually leaves me at a loss as to how get the time-driven hourly trigger to actually do what it says it is doing.

Why is that and how can I fix it?

• google-sheets
• google-apps-script
• triggers

Share Improve this question Follow edited Feb 2 at 18:24 Wicket 38.8k99 gold badges8080 silver badges195195 bronze badges asked Jan 31 at 22:58 sbraddsbradd 922 bronze badges 4

• 1 When run from a trigger the active sheet is Spreadsheet.getSheets()[0]. – Cooper Commented Feb 1 at 0:53
• 1 @Cooper's statement is correct. You may debug what's going on by having a console.log(sheet.getName()); to see which sheet is being returned as the trigger runs. Try changing getActiveSheet() to getSheetByName(name) to see if that works. – Saddles Commented Feb 1 at 1:11
• Thanks @Cooper and @Saddles! It's working again properly now. :) – sbradd Commented Feb 1 at 2:40
• If your question is resolved please post the solution as an answer so that other people in the community, who may have the same concern as you, will know that theirs can be resolved. – Lime Husky Commented Feb 3 at 17:53

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Posting a Comment as an Answer

When we use trigger the active sheet is Spreadsheet.getSheets()[0] to make things work as intended to use the specific sheet that we wanted we need to specify what sheet were gonna use by using getSheetByName(name) instead of getActiveSheet().

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // returns the first sheet of the spreadsheet.

var sheet = SpreadsheetApp.getActive().getSheetByName(name); // returns the specific sheet of the spreadsheet.

Reference:

• Class SpreadsheetApp