Insert a cell in google spreadsheet using google apps script

Question

I am working on a Google spreadsheet. I want to know if there is a way to insert a cell in the spreadsheet using apps script.

Example: Assume there are some data from A2 to Y2.

I want to insert a cell A2 with some data. So now there will be data from A2 to Z2.

Answer 1

This will shift everything from A2 to the right by 1 column, leaving A2 empty and ready to insert the new data.

Do be aware that this will also copy across the formatting of A2. So make sure any borders etc. are placed on A1 and not A2.

function addNewCell() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getRange("A2").insertCells(SpreadsheetApp.Dimension.COLUMNS);
}

And if you wish to shift A2 across multiple cells, use a range

ss.getRange("A2:C2").insertCells(SpreadsheetApp.Dimension.COLUMNS);

Obviously this particular code will move them 3 across

Answer 2

I got my answer, there is no way to insert a cell.

Answer 3

There's no function to directly insert a single cell, but we can do a move and write:

• Move range A2:Y2 to B2:Z2.
• Write the new value to A2.

For example:

function insertCell() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange("A2:Y2").moveTo(sheet.getRange("B2"));
  sheet.getRange("A2").setValue("New");
}