Search code examples
google-sheetsgoogle-apps-scripttriggers

setActiveRange does not work via time-based trigger

I want to use a time-based trigger for executing the following script.

The idea behind it is to show the spreadsheet on a screen and automatically jump every minute to another part of the sheet (as when you click on an internal hyperlink).

So when setting up the time-based trigger, the script does not throw any error and the viewportCount will also change accordingly with every minute -> only the active range will not change at all (therefore it does not show other parts of the sheet).

When triggering the script manually or via e.g. onEdit trigger, everything works fine and the active range changes. Do you have any idea why the time-based trigger seems not work in combination with setActiveRange?

function autoJump() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = SpreadsheetApp.setActiveSheet(ss.getSheets()[0]);
  var autoPlayConfig = sheet.getRange('P7').getValue();
  var viewportCount = sheet.getRange('Q7').getValue();
  var range1 = sheet.getRange("A1");
  var range2 = sheet.getRange("A30:A31");
  var range3 = sheet.getRange("A53:A54");

  if (autoPlayConfig == true) {

   switch(viewportCount) {
   case 1:
        sheet.getRange('Q7').setValue(viewportCount+1);
        sheet.setActiveRange(range1);
        break;    
   case 2:
        sheet.getRange('Q7').setValue(viewportCount+1);
        sheet.setActiveRange(range2);
        break; 
   case 3:
        sheet.getRange('Q7').setValue(1);
        sheet.setActiveRange(range3);;
        break; 
   default:
        sheet.setActiveRange(range1);
   }
  }    
}

Thanks and best regards, Daniel

Solution

  • Refering to documentation of setActiveRange:

    Sets the specified range as the active range, with the top left cell in the range as the current cell.

    The spreadsheet UI displays the sheet that contains the chosen range and selects the cells defined in the chosen range.

    But from other hand time-based trigger runs without any UI available, so using setActiveRange makes no effect.

    Edit (added workaround):

    Possible workaround is to create sidebar and use setTimeout via plain JavaScript as equivalent to time-based trigger. Here's demo example:

    Code.gs:

    function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('My Menu')
      .addItem('Show sidebar', 'showSidebar')
      .addToUi();
}

function showSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('sidebar').setTitle('My Sidebar');
  SpreadsheetApp.getUi().showSidebar(html);
}

function autoJump() {
  // sample function that just will jump down one cell 
  SpreadsheetApp.getActiveRange().offset(1, 0).activate();
}

    sidebar.html:

    <!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <script>
    // call autoJump function every 2 seconds
    setInterval(function(){
      google.script.run.autoJump();
    }, 2000);
    </script>
  </body>
</html>