Skip to content

Instantly share code, notes, and snippets.

@alecbw

alecbw/move_rows.js

Created January 30, 2023 20:57
Show Gist options
  • Save alecbw/77bcffbf23642097d39c63b720867338 to your computer and use it in GitHub Desktop.
Save alecbw/77bcffbf23642097d39c63b720867338 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. alecbw created this gist Jan 30, 2023.
    32 changes: 32 additions & 0 deletions move_rows.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,32 @@
    // Adds a menu on user opening the spreadsheet
    function onOpen() {
    options = [
    {name:"Move Rows", functionName:"moveRows"},
    ];
    SpreadsheetApp.getActiveSpreadsheet().addMenu("~ Tools ~ ", options);
    }

    function moveRows() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet();
    var tab = sheet.getActiveSheet();
    var range = tab.getActiveRange();

    var startRow = range.getRow();
    var endRow = range.getLastRow();

    var numRows = endRow - startRow + 1;

    var destinationRow = parseInt(Browser.inputBox("Enter the destination row:")) + 1;

    var data = range.getValues();

    // Insert the data at the destination row, displacing the existing rows
    tab.insertRows(destinationRow, numRows);
    tab.getRange(destinationRow, 1, numRows, range.getLastColumn()).setValues(data);

    if (startRow > destinationRow) { // e.g. youre moving it up from Row 20 to Row 10
    startRow += numRows // account for the fact that new rows have been added above
    }
    // Delete the original rows
    tab.deleteRows(startRow, numRows);
    }