Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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

3 Answers 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.

share|improve this answer

An alternative solution involves a helper script that duplicates the selected rows (or columns). The script is below, and here are the steps:

  1. Select the rows (or columns) to be inserted elsewhere.
  2. Execute "Custom > Duplicate selected rows (or columns)"
  3. Drag and drop the selection to the desired location.

All references in formulas are remapped as one would expect: if B2=A2+1 is moved to row 5 in this process, it becomes B5=A5+1.

function onOpen() {
  var menu = [{name: "Duplicate selected rows", functionName: "duplicateRows"}, {name: "Duplicate selected columns", functionName: "duplicateColumns"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
}

function duplicateRows(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var selection = sheet.getActiveRange();
  var selectedRow = selection.getRowIndex();
  var selectedColumn = selection.getColumnIndex();
  var height = selection.getHeight();
  var width = selection.getWidth();
  sheet.insertRowsAfter(selectedRow+height-1, height);
  selection.copyTo(sheet.getRange(selectedRow+height, selectedColumn, height, width));
}

function duplicateColumns(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var selection = sheet.getActiveRange();
  var selectedRow = selection.getRowIndex();
  var selectedColumn = selection.getColumnIndex();
  var height = selection.getHeight();
  var width = selection.getWidth();
  sheet.insertColumnsAfter(selectedColumn+width-1, width);
  selection.copyTo(sheet.getRange(selectedRow, selectedColumn+width, height, width));
}
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 at 8:54
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

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.