How to delete specific cells with app script?

Question

My intention is to be able to delete for example cell F1 without deleting cell F2.

var sheet = ss.getSheetByName("Questionário DQSA");  
  var range = sheet.getRange("B1:U1");
  range.clearContent();

Here I clear the content of these cells but I would like to know how to delete them.

score 3 · Answer 1 · answered Jul 29 '19 at 15:55

3

You can use Range.deleteCells(Dimension):

var sheet = ss.getSheetByName("Questionário DQSA");  
  var range = sheet.getRange("F1");
  range.deleteCells(SpreadsheetApp.Dimension.COLUMNS);//F2 becomes the new F1

answered Jul 29 '19 at 15:55

TheMaster

45,448
6
62
85

Thanks, that helped! – Bernardo Jul 29 '19 at 16:15
Its working but what i wanted is that the function wouls never stop deleting the cells if they were empty – Bernardo Jul 30 '19 at 09:32
@Bernardo Kindly ask a new question. – TheMaster Jul 30 '19 at 09:34
I just did, tks – Bernardo Jul 30 '19 at 09:37

Cooper · Answer 2 · 2019-07-29T16:32:10.043

If you want to roll your own:

function deleteCell(shift) {
  var shift=shift || "left"; //left or up
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var cell=ss.getActiveCell();
  if(shift=="left") {
    var rg=sh.getRange(cell.getRow(),1,1,sh.getLastColumn());
    var vA=rg.getValues();
    var row=vA[0];
    row.splice(cell.getColumn()-1,1);
    rg.clearContent();
    sh.getRange(cell.getRow(),1,1,row.length).setValues([row]); 
  }
  if(shift=="up") {
    var rg=sh.getRange(1,cell.getColumn(),sh.getLastRow(),1);
    var vA=rg.getValues();
    var row=vA.map(function(r){return r[0]});
    row.splice(cell.getRow()-1,1);
    var col=[];
    row.forEach(function(e){
      col.push([e]);
    });
    rg.clearContent();
    sh.getRange(1,cell.getColumn(),col.length,1).setValues(col);
  }
}

Of course now that I know about that new method in range it's a lot easier to write. Thanks to @TheMaster

function deleteCell(shift) {
  var shift=shift || "left";
  var ss=SpreadsheetApp.getActive();
  var cell=ss.getActiveCell();
  if(shift=="left") {
    cell.deleteCells(SpreadsheetApp.Dimension.COLUMNS);
  }
  if(shift=="up") {
    cell.deleteCells(SpreadsheetApp.Dimension.ROWS);
  }
}

How to delete specific cells with app script?

2 Answers2