3

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?

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);
}
  • 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
1

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();
  }
}
  • 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
0

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.

0

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.

  • as you can see, this is obviously more clumsy than the two-click method usable in excel. – djsadinoff Oct 25 '17 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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