Google Spreadsheet - Insert cut/copied cells/rows

14
votes

Google Spreadsheet doesn't have the functionality to "insert cut cells" like in Excel.

Let's say I selected A4 to B5, hit Ctrl+X.
Then I moved the selection to A2.
Now I want to "insert cut cells", probably by inserting blank cells and moving the dotted-range to A2.
screenshot

Is there any way to do it using JavaScript on your own menu?

eg.

function insertCutOrCopiedCells(){
  var SS = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SS.getActiveSheet();
  var cell = sheet.getActiveCell();

  // How do you get the cells being cut/copied? (ie. A4 to B5)

  // We can then insert empty cells (2 cols 2 rows) at the selection (A2)
  // and move the cut cells (A4 to B5) there
}
3
google-apps-scriptgoogle-spreadsheet-api
Apps script can't listen to keyboard nor check the clipboard. – Kriggs
Yeah, but it's not only on clipboard. It's marked with dotted border on the sheet. So I was just wondering if there was a way to get that range. – Aximili
Google Sheets does have to ability to move rows (or columns), the equivalent of cut and insert. Select the whole rows, move your cursor over the row number, cursor changes into a hand, and drag. – Nigel Touch

3 Answers

21
votes

To achieve the insert cut cells feature in google sheets you simply use drag and drop. First highlight the row(s) you want to move then mouse over the row number (you will see a hand icon); then, holding your left-click, and keeping your mouse over the row numbers, you will see a dark line where the insert will take place. Let go of your mouse button and voila.

enter image description here

2
votes

You have both methods, check'em in the class Range.

moveTo(target)

Cut and paste (both format and values) from this range to the target range.

copyTo(destination)

Copies the data from a range of cells to another range of cells. Both the values and formatting are copied.

Edit:

To complete the function you'll have to use also:

insertRowsAfter(afterPosition, howMany) in class Spreadsheet

Inserts a number of rows after the given row position.

and

getActiveRange() with getRow() to check where's the selection at:

Returns the range of cells that is currently considered active. This generally means the range that a user has selected in the active sheet, but in a custom function it refers to the cell being actively recalculated.

Since you don't have direct acess to the clipboard, you'll have to set up a sidebar, or a modelessDialog, which asks for a range to copy from, and it would paste into the selected area, or the other way around, paste the current selected area onto an inputed ROW.

1
votes

I think , you can separate the function.

Copy : use getRange with getValue: link => https://developers.google.com/apps-script/reference/spreadsheet/sheet

Delete data : use the getRange with setValue = blank

example :

var Spreadsheet=
 SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName("number1"); 
 var value = Spreadsheet.getRange(1,0).getValue();  // copy  
 Spreadsheet.getRange(1,1).setValues(value); // insert
 Spreadsheet.getRange(1,0).setValues("");

You can use the metho copyTo

example :

var sheet = SpreadsheetApp.getActiveSheet();
 sheet.getRange("A:E").copyTo(sheet.getRange("F1"), {contentsOnly:true});
 }