Move Columns Based on Array of Column Numbers

Question

I have an array of columns that I want to keep. I've got that code below:

  //Keep only columns user wants.
  if(keepColumnsArray != ""){    
    for (var col = sourceShtLC; col > 0; col--) {
      if (keepColumnsArray.indexOf(col) == -1) {
        // This isn't a keeper, delete it
        destSht.deleteColumn(col);
      }    
    }
  }

What I'd like to do now is arrange the columns following the order the keepColumnsArray has them.

Samples:

var keepColumnsArray = [3,2,1,4,5]

Using the above sample I want column 3 to be the first column, column 2 to be the second, column 1 to be the 3rd, column 4 to be the 4th and column 5 to be the 5th.

Current Order:

The order I want it. As you can see it's the same order the array is in.

Solution: Rather than deleting the columns first I used code from the accepted answer to move the columns I want to keep to the front. In this case Columns 1 through 5 I kept and then I deleted the rest because all that was left were columns I did not need. Here is my final code.

  //Use order of array to reorder columns and then delete the rest of the columns
  var offset = keepColumnsArray.length;
  destSht.insertColumns(1, offset);
  keepColumnsArray.forEach(function(e, i) {
    destSht.getRange(1, (e + offset), destSht.getLastRow(), 1).copyTo(destSht.getRange(1, i + 1));
  });
  destSht.deleteColumns(offset + 1, sourceShtLC);  //Keep only columns user wants.

Answer 1

• You want to arrange the columns on the sheet with var keepColumnsArray = [3,2,1,4,5].
  • For example, you want to arrange from the columns 1, 2, 3, 4, 5 to 3, 2, 1, 4, 5.
• You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Pattern 1:

In this case, moveColumns is used. The flow of this script is as follows.

1. Create an array object including the original index and destination index of the columns.
2. Sort the array.
3. The columns are arranged using moveColumns.

Sample script:

function myFunction() {
  var keepColumnsArray = [3,2,1,4,5];

  var obj = keepColumnsArray.reduce(function(ar, e, i) {
    ar.push({from: i + 1, to: e});
    return ar;
  }, []);
  obj.sort(function(a, b) {return  a.to < b.to ? -1 : 1});
  var sheet = SpreadsheetApp.getActiveSheet();
  obj.forEach(function(o) {
    var columnSpec = sheet.getRange(1, o.from);
    if (o.from != o.to) sheet.moveColumns(columnSpec, o.to);
    for (var j = 0; j < obj.length; j++) {
      if (obj[j].from < o.from) obj[j].from += 1;
    }
  });
}

Pattern 2:

In this case, each column is copied with the order of keepColumnsArray using a temporal sheet, and put the arranged columns to the original sheet.

Sample script:

function myFunction() {
  var keepColumnsArray = [3,2,1,4,5];

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var temp = ss.insertSheet("temp");
  sheet.activate();
  keepColumnsArray.forEach(function(e, i) {
    sheet.getRange(1, e, sheet.getLastRow(), 1).copyTo(temp.getRange(1, i + 1));
  });
  temp.getDataRange().copyTo(sheet.getRange(1, 1));
  ss.deleteSheet(temp);
}

Reference:

• moveColumns(columnSpec, destinationIndex)

Added:

From OP's comment, In this sample script, the columns are inserted and put the arranged columns.

Instead of creating a temp sheet can we not add 5 columns to the beginning and then copy them to those new columns?

Sample script:

function myFunction() {
  var keepColumnsArray = [3,2,1,4,5];

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var offset = keepColumnsArray.length;
  sheet.insertColumns(1, offset);
  keepColumnsArray.forEach(function(e, i) {
    sheet.getRange(1, e + offset, sheet.getLastRow(), 1).copyTo(sheet.getRange(1, i + 1));
  });
  sheet.deleteColumns(offset, offset);
}

Answer 2

Problem

1. Filtering out columns by criteria
2. Reordering columns by criteria

Solution

First, add logic that filters and sorts your values. One of the possible algorithms is as follows: map source such that each row is mapped such that each cell is cell by order index from source and filter columns such that its index is a keep index.

var input = [3,2,1,4,5];

var initial = [
  ['Column1','Column2','Column3','Column4','Column5','Column6','Column7'],
  ['2first','2second','2third','2fourth','2fifth','2sixth','2seventh'],
];

function filterAndReorder (source,order) {
  return source
  .map(function(row,r){        
    return row.map(function(cell,c){
      return source[r][order[c]-1];
    })
    .filter(function(cell,c){
      return cell !== undefined;
    });
  })
}

var output = filterAndReorder(initial,input);

console.log(output);

Then, use the fact that setValues() accepts 2D Array and replaces Range contents:

var lrow = destSht.getLastRow();
var range = destSht.getRange(1,1,lrow,5);
var inputVals = range.getValues();

var keepColumnsArray = []; //obtain the keep Array somehow;

var outputVals = filterAndReorder(inputVals,keepColumnsArray);

var range.setValues(outputVals);

Notes

1. keepColumnsArray is an Array so the != "" is redundant (unless you actually expect it to be an empty string in which case I would suggest rewriting the logic that outputs the Array - it will save you at least 1 op + save you debug time in the future).
2. As a general rule of thumb, please, avoid using I/O as much as possible (especially in a loop) and keep input close to start of the logic and output to the end. deleteColumn is an I/O and thus should at least be performed in batch.
3. UPD if you reorder is partial (that is, there are columns unchanged), you can fold the resulting empty columns via deleteColumns()

References

1. filter() method ref on MDN
2. map() method ref on MDN