CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
BrendanC
BrendanC

Reputation: 1

Google Sheets script to store current cell address, copy/paste elsewhere, then reactivate the cell

I'm trying to get a script to work so that the focus returns to the current active cell, after copying and pasting elsewhere. So, store the currently active cell, copy/paste some values elsewhere on the same sheet, then return the focus to that previously active cell.

This is because the copy/paste part will be called from several places in the sheet eg from tick boxes and drop-downs, so I can't explicitly state 'go back to cell A1' or whatever (which I have managed to get to work).

This is what I currently have.

function CopyTimes() {

var spreadsheet = SpreadsheetApp.getActive();
var activeCell = spreadsheet.getActiveCell();
spreadsheet.getRange('BD25').activate();
spreadsheet.getRange('BD15:BD23').copyTo(spreadsheet.getActiveRange(),
SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange(activeCell).activateAsCurrentCell();

};

The error seems to be that the range in the last line isn't defined. I've tried lots of variation, this seems the closest to something that looks right, but it's not working.

Any/all suggestions welcome. I have a feeling this should be incredibly easy but I don't know enough about scripting to figure it out. Thanks.

Upvotes: 0

Views: 73

Answers (1)

Jukebox Jory
Jukebox Jory

Reputation: 31

I wonder if a different approach might help. In cases like, this I leave the focus where it is and manipulate the data without using the .activate() part.

See this element from a script I have. It gets the values needed for the copy and the sheet names but then makes the copy all in one go, rather than activating a range and copying the values and then pasting them back.

// copy maths down
  var tasksmaxrow        = taskssheet.getLastRow();
  var rowstocopy         = tasksmaxrow - int_uptally  - 3;
      taskssheet.getRange(int_uptally+3,7,1,15).copyTo(taskssheet.getRange(int_uptally+4,7,rowstocopy,15),SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

Upvotes: 0

Related Questions