I have the following Google Sheets function:

function addRow() {
   var destinationSheet = SpreadsheetApp.getActiveSheet();
   var currentRow = destinationSheet.getActiveCell().getRow();

  // Insert the row
  destinationSheet.insertRowBefore(currentRow);

  // Copy the source to destination (currentRow is the blank line)
  var sourceRange = destinationSheet.getRange(currentRow   1, 1, 1, destinationSheet.getLastColumn());
  var targetRange = destinationSheet.getRange(currentRow - 0, 1, 2, destinationSheet.getLastColumn());
  
  // Fill upwards
  sourceRange.autoFill(targetRange, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  SpreadsheetApp.getUi().alert(destinationSheet.getActiveCell().getRow());
};

It creates a blank row above the current one and copies the current row into the blank one. This works.

The cursor (visually) ends up in the new row. In other words, visually, when I do the insertRowBefore, the cursor stays where it is and the rows below move down.

However, when I then type into the cell (first column) without moving the cursor beforehand, it types into the cell where the cursor is but the moment I press Return, the data "jumps" to the cell below as if I had been typing there. It is as if the cursor is actually in the row below and the UI is out of sync. The alert, however, "tells" me I am in the right row (the one that was empty and then filled).

Interestingly, if I re-run the function without moving the cursor in the UI, I notice that the active row has incremented by one. So, I think that the UI moves the cursor after the function has finished but fails to show it graphically.

My question is: how do I either:

1. Get the UI to show the active cursor location correctly? OR, ideally
2. Prevent this "behind the scenes" movement of the cursor so that it remains in the correct cell (column 1 of the row that was added).

CodePudding user response：

The Google Apps Script Spreadsheet Service hasn't a methods to control the cursor position, but it is able to set the the active cell. One way among many is the following:

function setCurrentCell(){
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName('Sheet1');
  const range = sheet.getRange('B2'); // This should be a single cell
  range.activateAsCurrentCell();
}

Another way is to use activate() on a Range object. The current cell will be top left cell in the range.

Reference

• https://developers.google.com/apps-script/reference/spreadsheet/range#activateAsCurrentCell()
• https://developers.google.com/apps-script/reference/spreadsheet/range#activate

Related

• Google sheets, make active range the top left cell of the window

CodePudding user response：

I fixed it by adding the following 3 rows to the end of the function:

  destinationSheet.getRange(currentRow - 1, 1, 1, destinationSheet.getLastColumn()).activate();
  destinationSheet.getRange(currentRow, 1, 1, destinationSheet.getLastColumn()).activate();
  destinationSheet.getRange(currentRow, 1).activate();

This is the only way I managed to get it working. It:

1. Activates the preceding row
2. Activates the current row
3. [Optional] Activates the first cell of the current row.

Then when I type into the cell, it does not drop down a row when I hit enter. Note that for the first 2 lines of extra code, it is necessary to select more than the first column - hence why the whole row is selected. The problem is therefore resolved after the first 2 lines of code. The third is just cosmetic to select only the first column in the row.

I do not know why this works and why other variants do not work.