1

So I should start by saying I'm new at Javascript/programming. I understand a good amount of it but don't really know what I'm doing when it comes to actually writing it.

I'm trying to use google script so that when I open my spreadsheet, the cursor begins in the last row, so that I can use a combination of keyboard maestro and apple script to enter text. Problem is, I can't seem to get anything to work. I've tried to use what they have here: Faster way to find the first empty row but nothing seems to actually work properly. The function runs no problem, but it never actually does anything. I've used the suggested functions along with some of the developer ones of 'getLastrow" but nothing goes.

I think I have the second part down, in that to get it to work when I open the document I set a trigger 'on open', but I can't actually test it until I get the main function working.

Many thanks,

Community
  • 1
  • 1
Luke_
  • 11
  • 1
  • 1
  • 2
  • `...it never actually does anything.` What do you expect it to do? It's getting a reference to the first empty row (empty cell in column A, actually), to be used by a script. It doesn't make any visible change on the UI. – Mogsdad Nov 27 '14 at 01:58
  • Oh, I sorta thought that could have been the problem, but was too lost at the time to realize it. Would there be a way to make google scripts select the box to allow information to be typed into it? – Luke_ Nov 27 '14 at 03:47

1 Answers1

5

The referenced question provides scripts with a reference to the first empty row in a spreadsheet. (...although some of those solutions find the first empty cell in column A, which isn't necessarily an empty row. But I digress.)

In a spreadsheet-attached script with access to the User Interface, you can affect the active view of the spreadsheet with several functions:

Using whatever technique you wish to identify the "empty row" in the active spreadsheet, this function will move the selection to it, using .setActiveSelection():

/**
 * Place the current user's cursor into the first cell of the first empty row.
 */
function selectFirstEmptyRow() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  sheet.setActiveSelection(sheet.getRange("A"+getFirstEmptyRowWholeRow()))
}

The getFirstEmptyRowWholeRow() function, if you're curious:

/**
 * Mogsdad's "whole row" checker.
 */
function getFirstEmptyRowWholeRow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  var row = 0;
  for (var row=0; row<values.length; row++) {
    if (!values[row].join("")) break;
  }
  return (row+1);
}
Community
  • 1
  • 1
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • About "whole row" checker : How about the native getLastRow()(+1 of course) ? Doesn't it actually return what the OP was looking for ? Except if there is indeed a discontinuity in the sheet, then your solution would point this 'hole'... what do you think ? – Serge insas Nov 27 '14 at 22:59
  • That is a key point of course: `"first empty" !== "bottom of data + 1"`. Since that distinction had been made in the referenced question, I assumed he wanted what he asked for. If the last row is the target, though, then `selectFirstEmptyRow()` need only call that instead. – Mogsdad Nov 27 '14 at 23:44