Delete columns based on certain header values Google Apps Script

Question

I am trying to delete certain columns based on the criteria. Like in this sheet https://docs.google.com/spreadsheets/d/1-P_OkgLeWhWJrOHcmC2sjQVgAoasoRiJ31VnKooHer0/edit?usp=sharing

the columns that contains '_confidence' or '_estimatedconfidence' or '_estimatedconfidence accuracy' needs to be deleted. I am trying to use this code:

function deleteColumns() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var dataRange = sheet.getRange("A1:A20");
var data = sheet.getRange("A1:A20");
var values = data.getValues();
var numRows = values.length;
var numCols = values[0].length;

for (var col = numCols-1; col > 0; col--) {
 for (var row = 0; row < numRows; row++) {
  switch (values[row][col]) {      
    case "_confidence":
    case "_estimatedconfidence":
    case "_estimatedconfidence accuracy":
      sheet.deleteColumn(col+1);
      continue;                       
      break;
   }
  }
 }
}

I have modified this code from the original source. This script doesn't returns anything.

P.S. I have not included all the columns to the sheet above. There can be upto 100 columns for processing.

var dataRange = sheet.getRange("A1:A20"); var data = sheet.getRange("A1:A20"); var values = data.getValues(); ???? All values you are testing our from column A. — ScampMichael
Each of the case statements must have instructions to delete the column. The way it is written only the last case, if true, results in deletion of the column. — ScampMichael
Ah yes. @ScampMichael thanks, I changed the (A1:A20) to (A1:H20) and added sheet.deleteColumn(col+1); for each case but still there was no change in the sheet. — Mohammad Ahmed
The switch/case statement it's not really appropriate if you're going to perform the same action on all cases. — ScampMichael

Cooper Cooper · Accepted Answer · 2017-03-19T19:56:47

I needed to delete the largest columns first so in real life you may want to sort that final array colA largest to smallest. But I think this will get you where you want to go.

function deleteColumns() 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var dataR = sheet.getDataRange();
  var dataA = dataR.getValues();
  var colA = [];
  var re = /(.*_confidence|.*_estimatedconfidence|.*_estimatedconfidence accuracy)/
  for (var i = 0; i<dataA.length;i++) 
  {
    for(var j = 0; j<dataA[i].length;j++)
    {
      if(String(dataA[i][j]).match(re) && colA.indexOf(j+1)==-1)
      {
        colA.push(j+1);
      }
    }

  }
  colA.sort(compareNumbers);
  colA.reverse();
  for(var i =0;i < colA.length; i++)
  {
    sheet.deleteColumn(colA[i]);
  }
}

function compareNumbers(a, b) {
  return a - b;// I pulled this function out of the MDN website
}

I modified the code above to sort numbers instead of strings.

Delete columns based on certain header values Google Apps Script

1 Answers