1

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);
}
  • Google sheet is a collaborative application. If you ever open a sheet from two different account, you would see that each account has its own active range. This is to prevent editing conflict. If you open the same file from one account but separate computers, I believe the effect is the same. – daniel Feb 29 '16 at 12:08
  • @daniel Even if you open it from two tabs of the same browser, there are different cursors in each. – user79865 Feb 29 '16 at 13:01
1

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);
}
  • It is not very clear to me whether the function you suggest is supposed to be the solution. I tried to trigger it manually and it worked, but, again, if triggered on form submit it does not. – user113728 Feb 29 '16 at 14:25
  • 1
    Let me make it clear then: it is not a solution, it is a demonstration of how scripts work. The answer is in the text to be read, not in a code to be copy-pasted. – user79865 Feb 29 '16 at 14:25
  • Ok, got it. But does this also mean that there is no solution? – user113728 Feb 29 '16 at 14:27
  • 1
    Yes, that's what it means. A trigger can't move a user's cursor elsewhere. – user79865 Feb 29 '16 at 17:20
  • One work-around would be saving cursor position to the document property, and then create a timer that checks the property. Its a messy solution that, I think, is a waste of server process. – daniel Mar 1 '16 at 1:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.