Insert copied cells in Google Spreadsheets

Question

I'd like to shift a set of cells downward from a selection in Google Spreadsheets. Ideally "insert copied cells" from Excel, but I can live with just the shift part, i.e. insert blank cells, shifting downward.

How can I do this?

Answer 1

Here's my current solution. It inserts a blank area the size of the selection, and shifts the relevant rows downward by one selection-height. The next step is to make it a full-fledged "insert copied cells" feature.

/* NOTE: THIS function does not preserve the semantics of formulas which point at the
   moved data.   
  In this way it is more disruptive than cut-and-paste, 
         which retargets references in existing formulas 
*/ 
function shiftCellsDown(){
  var SS = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SS.getActiveSheet();
  var selection = sheet.getActiveSelection();


  var selectedRow = selection.getRowIndex();
  var selectedCol = selection.getColumnIndex();
  var lastRow = sheet.getLastRow();

  var width = selection.getWidth();
  var height = selection.getHeight();

  var rangeToBeMoved =sheet.getRange(selectedRow, selectedCol, lastRow-selectedRow+height, width); 
  var dataToBeMoved = rangeToBeMoved.getValues();
  var row =  [];
  for ( var c =0; c< width; c++){
       row.push("");
  }
  for ( var r =0; r< height; r++){
       dataToBeMoved.splice(0,0,row);
  }

  sheet.getRange(selectedRow, selectedCol, dataToBeMoved.length,width).setValues(dataToBeMoved);
}

Answer 2

This script uses a copyTo method which copies content including formulas, remapping references along the way. Otherwise it's similar to djsadinoff's script in that it creates a blank rectangular block in the place of selected range, shifting other cells down.

In practice, I find that "insert and shift down" naturally goes with another script function, "delete and shift up" from my other answer. So I include both since they are likely to be used together. The onOpen function adds both commands to Custom menu item.

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", [
    {name: "Insert Blank and Shift Down",  functionName: "insertBlankAbove"},
    {name: "Delete and Shift Up",  functionName: "deleteAndShiftUp"}, 
  ]);
}

function insertBlankAbove() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var totalHeight = sheet.getDataRange().getHeight();
  var range = sheet.getActiveRange();
  var row = range.getRow();
  var column = range.getColumn();
  var height = range.getHeight();
  var width = range.getWidth();
  sheet.getRange(row, column, totalHeight-row+1, width)
       .copyTo(sheet.getRange(row+height, column, totalHeight-row+1, width));
  sheet.getRange(row, column, height, width).clear();
} 

function deleteAndShiftUp() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var totalHeight = sheet.getDataRange().getHeight();
  var totalWidth = sheet.getDataRange().getWidth();
  var toDelete = sheet.getActiveRange();
  var firstRow = toDelete.getRow();
  var firstColumn = toDelete.getColumn();
  var lastRow = Math.min(toDelete.getLastRow(), totalHeight);
  var lastColumn = Math.min(toDelete.getLastColumn(), totalWidth);
  var height = lastRow-firstRow+1;
  var width = lastColumn-firstColumn+1;
  if (height>0 && width>0) {
    if (totalHeight>lastRow) {
      sheet.getRange(lastRow+1, firstColumn, totalHeight-lastRow, width)
           .copyTo(sheet.getRange(firstRow, firstColumn, totalHeight-lastRow, width));
    }
    sheet.getRange(totalHeight-height+1, firstColumn, height, width).clear();
  }
}

Answer 3

For completeness, I will include the usual way of doing this, without a script.

1. Determine the number of rows to be copied
2. Select that many rows below (or above) the desired location.
3. Right-Click (or go to Insert Menu) to "Insert x Rows above" (or below).
4. This creates enough empty rows into which one can then copy and paste.

As seen in this youtube video [no affiliation].

A drawback is that one has to count the rows to be inserted. If one undercounts and does not notice the mistake right away, it can be costly.

Answer 4

To insert blank cells:

Select the range presently located where you would like the blank cells to be (it should turn blue). Left-click on the boundary of the blue zone (the cursor should change to a 'hand' and the border of the zone should turn to dashed from solid), then drag to suit.

To insert copied cells (into space made available as above):

Sselect the cells to be copied (Ctrl+c) and in the top left cell where to be pasted either Ctrl+v or Ctrl+Shift+V according to your requirement.