CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Suneel Kumar
Suneel Kumar

Reputation: 5831

Insert a cell in google spreadsheet using google apps script

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.

Upvotes: 8

Views: 15545

Answers (7)

Mark Cooper
Mark Cooper

Reputation: 71

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

Upvotes: 7

adhilde
adhilde

Reputation: 1

I was interested in moving a block of data down to create a new (empty) row at the top of my table. Knowing that the data in my top row stared in cell A7 and the last column in my table is Column H, I only move the table columns down leaving the rest of my spreadsheet where it is.

 function addNewRow() {
     var sheet = SpreadsheetApp.getActiveSpreadsheet();
     sheet.getRange("A7:H1000").moveTo(sheet.getRange("A8"));  
 }

Upvotes: -2

Yang
Yang

Reputation: 8180

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");
}

Upvotes: 2

Hann
Hann

Reputation: 713

if you want to write in he spreadsheet, you need to use the Class Sheet , GetRange function.

for example : 

 mysheet.getRange(1,5,nbrows+BNrows-1,5).setValues(myArray); // my Array concern a column

Upvotes: -2

Suneel Kumar
Suneel Kumar

Reputation: 5831

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

Upvotes: 3

Joachin Joseph
Joachin Joseph

Reputation: 343

I do not think there is way to insert a cell left/right to a cell. If your cells and address are not dynamic, you can do the following

  • Get the content on A2-Y2 range and put in an array
  • Add your new desired value on the array at index 0, I mean the first element of array
  • Insert the new array values in row 2. I mean A2-Z2.
//Get A2-Y2 in a cellFeed

    URL cellFeedUrl1 = new URI(worksheets.get(WSID).getCellFeedUrl() +"?min-row=2&max-row=2&min-col=1&max-col=1").toURL();
    CellFeed cellFeed1 = googleservice.getFeed(cellFeedUrl1, CellFeed.class);

//store in an Array

       for (CellEntry cell : cellFeed1.getEntries()) 
    {   Array_A2Y2.add(cell.getPlainTextContent());     }

//Add your desired cell Value on the first of that array

//Insert the Array items on the your required row

Upvotes: 0

Aircleaner
Aircleaner

Reputation: 25

From my experince using google spreadsheets you can only insert a whole row or column. This is done by right clicking the row/column and select add row/column and then you can select to add it below/right or above/left.

Upvotes: -1

Related Questions