Google-sheets – Find in range and go to cell. Trigger On form submit not working!

google sheetsgoogle-apps-script

I am using a script that checks column range B2:B for a specific value contained in a cell of another column (the value in this cell deliberately corresponds to the last value in the column B) and moves the cursor/pointer to the correspondent cell in column B. The final goal is basically to have automatically the cursor/pointer on the last value of the column.

The script works perfectly if run manually, but if triggered when a form is submitted, the script doesn't work.
Do you have any ideas why this is happening?

Link to the form: http://goo.gl/forms/n54MIhxaSw
Link to the responses file: https://goo.gl/RXNqNJ

This is the script:

function gotocell() {
  Utilities.sleep(1000);
  var sheet = SpreadsheetApp.getActiveSheet();
  var row =  SpreadsheetApp.getActiveSheet().getLastRow();
  var dateRange = sheet.getRange('B2:B');
  var dates = dateRange.getValues();
  var cursor = sheet.getRange('D2').getValue();

  // Find cursor in the dateRange
  var found = false;
  var row=dates.length-1;
  while (row >= 0 && !found) {
    var col=dates[row].length-1;
    while (col>=0 && !found) {
      // If the array cell matches 'cursor', stop looking.
      if (!(cursor > dates[row][col]) && !(cursor < dates[row][col])) {
        found = true;
        break;
      }
      col--;
    }
    if (!found)
      row--;
  }

  // Use search row & col to offset to a cell relative to dateRange start
  var active = dateRange.offset(row, col,1,1);

  // Move user's cursor to cursor's date, if it is found in dateRange
  if (found)
    sheet.setActiveRange(active);
}

Best Answer

The active range is not set at user level, it is set separately for each user session with a spreadsheet. That is, the same user can open the spreadsheet in two tabs of a browser and have two different cursors (active ranges) in each. (You'll see the other one shown in green, with your own name on it).

When your script is called manually, it affects the user session from which it is called. When it is invoked by a trigger, it affects the user session created for the event of form submission. That session is separate (and short-lived), so setActiveRange does not do anything visible in practice.

To demonstrate this, one can use the following minimal example, triggered by form submission. (So we don't have to think about nested while loops and whatnot.) 

function test() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("B2");
  sheet.setActiveRange(range);
}
Related Topic