3

I have the following code that I took from this question. I am trying to get my 400+ line Google sheet to auto-scroll to the bottom of the sheet on open instead of the first row. I am getting an error for the code on line 2.

TypeError: Cannot read property "source" from undefined. (line 2, file "Code")

I have a feeling it is because I have more than one tab on my spreadsheet, but I do not know code well enough to fix it. I would like it to only work on the first tab.

    function onOpen(e) {
      var spreadsheet = e.source;
      var sheet = spreadsheet.getActiveSheet();
      var lastRow = spreadsheet.getLastRow();
    /*  if (sheet.getMaxRows() == lastRow) {
        sheet.appendRow([""]);
      }
      lastRow = lastRow + 1;
      */
      var range = sheet.getRange("A" + lastRow + ":A" + lastRow);
      sheet.setActiveRange(range);
    }
player0
  • 124,011
  • 12
  • 67
  • 124
Mark Adelman
  • 33
  • 1
  • 3
  • 2
    Error message means just that - `e.source` cannot be read as `e` isn't defined (`e` is an *argument* that has to be *passed* to function - in case of triggers it is an event object constructed during event handling) - may I ask you how you got it? Triggers aren't meant to be run in editor, they are fired as event callbacks (in your case - when a spreadsheet is open). Try opening the spreadsheet - the code should work as expected. – Oleg Valter is with Ukraine Aug 30 '19 at 17:59
  • 1
    I got the error message when I tried pressing the play button on the editor to run the script. – Mark Adelman Aug 30 '19 at 21:00
  • 1
    Hi Mark! You mean "run" button? Yes, this is exactly what you will get if you try doing so (see reason in my previous comment) - there is nothing wrong with the code (but `e` is indeed undefined when run in context of script editor) - have you tried actually running it by opening the target spreadsheet? Because I don't see why it should fail then. [This is](https://developers.google.com/apps-script/guides/triggers/events#open) how event object is structured when `open` event is emitted – Oleg Valter is with Ukraine Aug 30 '19 at 21:07
  • The auto scroll is not working. I was wondering if I need to have the name of the tab in the coding or something to make it work. As of right now it is not working. I would share the sheet, but it has peoples names and personal information on there – Mark Adelman Aug 30 '19 at 22:11
  • 1
    Mark, by tab you mean "Sheet" name (this is important, data structure in Google Sheets is: Spreadsheet -> Sheet -> Range)? The only way this code won't work is if your **target** sheet isn't the first one. And it technically works, just not on the sheet you expect it to work on, minor modification making target sheet active should help – Oleg Valter is with Ukraine Aug 30 '19 at 22:43

1 Answers1

11

The instructions that Vidar S. Ramdal gave in the referred topic and which @OlegValter has tried to explain in the comments are VERY important. Be sure to do them all.

  1. Go to Tools → Script editor and paste the following:
function onOpen(){
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheetname = ss.getSheets()[0].getName();
 // Logger.log("DEBUG: sheetname = "+sheetname)
 var sheet = ss.getSheetByName(sheetname);
 var lastRow = sheet.getLastRow();
 var range = sheet.getRange(lastRow,1);
 sheet.setActiveRange(range);
}

  1. Delete any existing code that is called onOpen(), onOpen(e) or similar.

  2. Click the Save button, then close the script editor, and the spreadsheet.

  3. Now, open your spreadsheet again. Give it a couple of seconds, and you should see the cursor drop to the Column A on last row of content on the first sheet. This will happen regardless of what sheet/row/column was active when you last used the spreadsheet.

Note:

onOpen() is not a function that can be run from the Script Editor as @OlegValter has explained. If you click "run" to execute the function, it will NOT work. It will ONLY execute when the spreadsheet is opened.

Explanation

  • ss.getSheets()[0].getName();
    getSheets() (Doc ref) gets all the sheets in the spreadsheet, and getSheets()[0] limits this to just the first sheet in the spreadsheet. getName() returns the name of the sheet. Combined, this line returns the name of the first sheet in the Spreadsheet.
  • ss.getSheetByName(sheetname);
    getSheetByName(name)(Doc ref) returns a sheet with the given name.
  • sheet.getLastRow() -
    returns the position of the last row that has content (Doc ref)
  • sheet.getRange(lastRow,1);
    getRange(row, column) returns the range with the top left cell at the given coordinates. We use lastRow as the value of the row, and 1 to indicate Column A (Doc ref)
  • setActiveRange(range) Sets the specified range as the active range in the active sheet, with the top left cell in the range as the current cell (Doc ref). In other words, the screen moves to the active cell.
Tedinoz
  • 5,911
  • 3
  • 25
  • 35