CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-form-quiz
Bernardo
Bernardo

Reputation: 63

How to delete specific cells with app script?

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.

Upvotes: 3

Views: 9632

Answers (2)

Cooper
Cooper

Reputation: 64062

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

Upvotes: 1

TheMaster
TheMaster

Reputation: 50462

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

Upvotes: 3

Related Questions