How do I delete a column that contains a substring or is empty?

Question

Below is my code:

delCol tracks how many columns have been deleted (to keep the iterations aligned)

sheet is the active spreadsheet

data is the array of values from the row I'm searching through, retrieved via SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("2:2").getValues();

function deleteRows() {
  var delCol = 0;
  var data = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("2:2").getValues();
  var sheet = SpreadsheetApp.getActiveSheet();
  for(var i = 0; i < data[0].length; i++) {
    if(data[0][i-delCol].indexOf("txt-to-delete") > -1 || data[0][i-delCol] == ""){
      sheet.deleteColumn(i - delCol + 1);
      delCol++;
    }
  }
}

When I run this code, each and EVERY column is deleted.

Answer 1

Try this:

function runOne(s) {
  var s=s||'deleteme';//The substring
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  var rg=sh.getDataRange();
  var vA=transpose(rg.getValues());
  var d=0;
  for(var i=0;i-d<vA.length;i++) {
    var deleted=false;
    if(vA[i-d].join('').length==0) {
      vA.splice(i-d++,1);
      deleted=true;
    }
    var rs=vA[i-d].join(' ');
    if(!deleted && rs.indexOf(s)!=-1) {
      vA.splice(i-d++,1);
    }
  } 
  sh.clearContents();
  var oA=transpose(vA);
  sh.getRange(1,1,oA.length,oA[0].length).setValues(oA);
}

function transpose(a) {
  return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); });
}

Transpose Reference

Answer 2

When you call getValues() a 2D-Array of the data contained in the range is copied to your scripts runtime's memory. After that, if you modify the range's data (using calls such as setValues()) the data in your sheet will be modified, but not the data in your 2D-Array.

More specifically, the mistake in your code is when evaluating the if statement's condition. You are taking into account delCol when accessing your 2D-Array, when in fact, you needn't do it. See below a correction of your code:

function deleteRows() {
  var delCol = 0;
  var data = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("2:2").getValues();
  var sheet = SpreadsheetApp.getActiveSheet();
  for(var i = 0; i < data[0].length; i++) {
    if (data[0][i].indexOf("txt-to-delete") > -1 || data[0][i] == "") {
      sheet.deleteColumn(i - delCol + 1);
      delCol++;
    }
  }
}