Deleting Cells in Google Sheets without removing a whole row

Question

I have the code as below that currently deletes the entire row if 'delete' is in the 4th cell of that row. Is it posible to just delete the 3 cells to the left as well as the cell I have written 'delete' in, rather than the whole row (I have other content in Cell B16 for example I don't want to delete?

I haven't been able to find a .deleteCell function. Is there another way to do this?

function deleteRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    if (row[4] == 'delete') { // This searches all cells in columns A (change to row[1] for columns B and so on) and deletes row if cell is empty or has value 'delete'.
      sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
};

Do you mean emptying their content rather than deleting the cells? — Simon Baars, Sep 08 '17 at 08:54
So all cells beneath the cell you'd like to delete move one cell up? You'd have to manually program that using Google Apps Script. There is no built in function. — Simon Baars, Sep 08 '17 at 09:36
How could you delete just the three cells and leave the rest of the row open? If you're deleting, You delete everything in that row or column. But you could simply clear some of the cells. — TheMaster, Sep 08 '17 at 09:38
@SimonBaars Yes, exactly. Delete and shift up. Is this an easy thing to program? — tbowden, Sep 08 '17 at 09:55

Simon Baars · Answer 1 · 2017-09-11T06:41:12.870

0

So the problem is:

How do I move all the cells beneath a given cell one row up (overwriting the given cell value)?

The code to do this would be:

function deleteCell(sheet, cellx, celly){
    var wholeSheet = SpreadsheetApp.openById(sheet).getRange("A1:F").getValues();
    for(var i = celly; i<wholeSheet.length; i++){
        wholeSheet[cellx][i] = wholeSheet[cellx][i+1];
    }
    SpreadsheetApp.openById(sheet).setValues(wholeSheet);
}

edited Sep 11 '17 at 06:41

answered Sep 08 '17 at 10:30

Simon Baars

1,877
21
38

Can't seam to get this working! I've moved the x and y around. The issue appears to be on line 2. Does this code relate to a specific column l.e only rows where 'delete' appears next to it. – tbowden Sep 08 '17 at 10:57
Oops, I see I did make a mistake there: I forgot the range. Please try this again. The "F" in line 2 should be the last column in your sheet (eg if your sheet has 3 columns, this "F" should be a "C"). – Simon Baars Sep 08 '17 at 12:09
If the shifted cells contain any formulas, they will be lost, replaced by numbers because of `setValues`. Formatting will not be moved up either. Use `copyTo` method to avoid both issues. (cc: @tbowden) – Sep 08 '17 at 17:17

Cooper · Answer 2 · 2017-09-10T23:36:38.007

Delete with Shift Up or Shift Left

This will delete a cell and shift the data up or left depending upon the shift parameter.

function delCellAndShift(row,column,shift) 
{
  if(row && column)
  {
    var shift=(typeof(shift)!='undefined')?shift:'left';
    var ss=SpreadsheetApp.getActive();
    var sh=ss.getActiveSheet();
    var rg=sh.getDataRange()
    var vA=rg.getValues();
    if(shift=='left')
    {
      var tA=vA[row-1];
      tA.splice(column-1,1);
      tA.push('');
      vA[row-1]=tA;
      rg.setValues(vA);
    }
    if(shift=='up')
    {
      var vB=Object.keys(vA[0]).map(function (c) { return vA.map(function (r) { return r[c]; }); });
      var tA=vB[column-1];
      tA.splice(row-1,1);
      tA.push('');
      vB[column-1]=tA;
      vA=Object.keys(vB[0]).map(function (c) { return vB.map(function (r) { return r[c]; }); });
      rg.setValues(vA);
    }
  }
  else
  {
    var s='Invalid Inputs for delCellAndShift.<br /><input type="button" value="Close" onClick="google.script.host.close();" />';
    var ui=HtmlService.createHtmlOutput(s);
    SpreadsheetApp.getUi().showModalDialog(us, 'Invalid Inputs')
  }
}

I tested it using the following functions to setup my data and run the main function.

function testDelCell()
{
  delCellAndShift(3,7,'up');
}

function testsetupfordelcell()
{
    var rs=10;
    var cs=10;
    var ss=SpreadsheetApp.getActive();
    var sh=ss.getActiveSheet();
    var rg=sh.getRange(1,1,rs,cs);
    var vA=rg.getValues();
    for(var i=0;i<rs;i++)
    {
      for(var j=0;j<cs;j++)
      {
        vA[i][j]=Utilities.formatString('%s,%s',i,j)
      }
    }
    rg.setValues(vA);
}

This is a sample of it shifting left:

This is a sample of it shifting up:

Deleting Cells in Google Sheets without removing a whole row

2 Answers2

Delete with Shift Up or Shift Left

Linked