8

As the title says, I do move rows a lot and the lack of default shortcut complicates things for me...

The drag option isn't practical when I need to move some rows to the bottom of a huge sheet!

I tried to us AutoHotKey, but it's a pain since it move one row at a time and takes ages to reach the bottom.

So I'm looking maybe for a JavaScript solution, maybe if someone could put me on the right path with a base example code/references that I could edit/extend later to suit my needs...

Also, any alternative solution I'm not thinking of is welcome.

Edit: From the title:

  • Up: move row(s) one row up
  • Down: move row(s) one row down
  • Top: move row(s) to the top of the sheet (all the way up)
  • End: move row(s) to the bottom of the sheet (all the way down, at the end of non-empty rows)
  • Are the rows to be moved consecutive or not? Also, the title mentions "up/down/top/end", but in the post you write only about moving to the bottom. I can guess what "top" refers to, but don't know about up/down, – user79865 Jan 20 '16 at 22:31
  • @Norma : I added some explanations. As for the rows, let's say for simplicity they are consecutive rows (even if they arent, it wont be complicated to group them before moving) – Enissay Jan 21 '16 at 1:15
5

The following script moves selected consecutive rows in one of four ways described in the question. It is triggered by custom menu items (Custom > Move rows...) which is created by onOpen function whenever the spreadsheet is opened. Unfortunately, there is no way to assign keyboard shortcuts to script functions.

The structure of functions is simple: insert rows, copy the range (including formulas and formatting), delete old copy of the range. The helper function getData eliminates the need to repeat the same data-gathering in each of four functions.

function onOpen() {
  var menu = [{name: "Move rows up", functionName: "moveUp"}, {name: "Move rows down", functionName: "moveDown"}, {name: "Move rows to top", functionName: "moveTop"}, {name: "Move rows to bottom", functionName: "moveBottom"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
}

function moveDown(){
  var d = getData(); 
  d.sheet.insertRowsAfter(d.row+d.height, d.height);
  d.selection.copyTo(d.sheet.getRange(d.row+d.height+1, 1, d.height, d.width));
  d.sheet.deleteRows(d.row, d.height);
}

function moveBottom(){
  var d = getData(); 
  d.sheet.insertRowsAfter(d.last, d.height);
  d.selection.copyTo(d.sheet.getRange(d.last+1, 1, d.height, d.width));
  d.sheet.deleteRows(d.row, d.height);
}

function moveUp(){
  var d = getData();
  d.sheet.insertRowsBefore(d.row-1, d.height);
  d.sheet.getRange(d.row+d.height, 1, d.height, d.width).copyTo(d.sheet.getRange(d.row-1, 1, d.height, d.width));
  d.sheet.deleteRows(d.row+d.height, d.height);
}

function moveTop(){
  var d = getData(); 
  d.sheet.insertRowsBefore(1, d.height);
  d.sheet.getRange(d.row+d.height, 1, d.height, d.width).copyTo(d.sheet.getRange(1, 1, d.height, d.width));
  d.sheet.deleteRows(d.row+d.height, d.height);
}              

function getData() {              
  var d = {};
  d.sheet = SpreadsheetApp.getActiveSheet();
  d.selection = d.sheet.getActiveRange();
  d.row = d.selection.getRowIndex();
  d.height = d.selection.getHeight();
  d.width = d.selection.getWidth();
  d.last = d.sheet.getLastRow();
  return d;
}
| improve this answer | |
  • Your magic code works like a charm; However I was wondring if there's a way to achieve the following: 1. moved rows should still have focus (be selected) after being moved, it's not the case now; 2. when rows are moved, it would be nice if the window scrolls down/up to follow them; The main question was answered but it would be nice if you make it perfect by adding these (pretty sure many people will find it useful too) – Enissay Feb 3 '16 at 13:00
  • Marked as accepted, fell free to add those extra points later if you feel like it :-) – Enissay Feb 5 '16 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.