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
add comment

1 Answer

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
add comment

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.