Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 1 down vote accepted

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);
}
share|improve this answer
    
Note that this code has an interesting aspect (feature? bug?) which distinguishes it from the equivalent cut-and-paste solution. Cut-and-paste would preserve any references that other formulas have to the original cells, such that the new formulas would point to the new locations. This code does not. In my case, that's a plus, but I can see how it might be surprising. – djsadinoff Aug 8 '12 at 13:10

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();
  }
}
share|improve this answer
    
This works great, but note that it only works for entire row/column ranges, not for rectangular regions. – djsadinoff Jun 4 '15 at 8:54

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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