Script to scroll Google Sheet works, until I activate another cell. Why?

Question

I have a Google Sheet where the top 3 rows, including column headers, are frozen. When user data is entered in cell A1, my script copies it to a cell in a different row of the spreadsheet, and activates that cell so it will be visible to the user, even if that row had been scrolled out of view. All that works properly, until I add the next line of code -to reactivate cell A1 for the next user input.

Here's the sample script I am using to troubleshoot the problem. I am very new to Google Apps Script. Any help will be appreciated.

function onEdit(e) { 
    // Practice script: scroll to show 
    // a cell where new data was just added

    var range = e.range;
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];  
    var cellAddress = e.range.getA1Notation(); 
    var inputCell = 'A1';
    var inputData = sheet.getRange(inputCell).getValue(); // get user input       

     // If edited cell was A1, scroll to cell (5,5) and record user input     
     if (cellAddress === inputCell) {
     sheet.getRange(5,5).activate(); 
     sheet.getRange(5,5).setValue(inputData);

     // then reactivate inputCell

     // If the following line is executed, the sheet won't scroll to (5,5) 
     // sheet.getRange(1,1).activate();
  }
}

Try using SpreadsheetApp.flush(); between your .activate() lines. — ross
Ross -that helped. It made the scroll work, but now the last line doesn't activate (1,1). — TucsonTerry
That brought back the problem I started with, it doesn't scroll, but does activate the inputCell (1,1). — TucsonTerry

utphx utphx · Accepted Answer · 2019-09-29T00:40:24

If I am understanding your question correctly, upon editing A1 you want to:

scroll to a cell (5,5), wait, then scroll back to A1

If so, add a delay using Utilities.sleep(3000) - this will wait 3 seconds:

//If the following line is executed, the sheet won't scroll to (5,5) 
SpreadsheetApp.flush();
Utilities.sleep(3000)
sheet.getRange(1,1).activate();

Script to scroll Google Sheet works, until I activate another cell. Why?

1 Answers