Given this table schema:

Col_France	Col_Argentina	Col_Croatia	Col_Morocco
x	x	x	x
x	x	x	x

I want to create a Google Script that rearranges the columns so the order is always:

Col_Argentina -> Column 1
Col_France -> Column 2
Col_Croatia -> Column 3
Col_Morocco -> Column 4

Because the original column orders of the given table is not always as described above, I cannot simply use:

var sheet = SpreadsheetApp.getActiveSheet();
// Selects Col_France.
var columnSpec = sheet.getRange("A1");
sheet.moveColumns(columnSpec, 2);

and so on... In other words, the table schema can possibly be:

Col_Morocco	Col_Croatia	Col_France	Col_Argentina
x	x	x	x
x	x	x	x

but the desired outcome should always be the defined above. The script should be scalable. In the future, more than 4 columns should be rearranged.

My approach would be:

• Define the range of columns to rearrange (they are all together)
• For the first column, get the value of the column header
• Depending on the value, move the column to a predefined index
• Move to the next column and repeat
• Iterate until end of range

Can somebody please point me to the required functions?

CodePudding user response：

In your situation, when moveColumns is used, how about the following sample script?

Sample script:

function myFunction() {
  var order = ["Col_Argentina", "Col_France", "Col_Croatia", "Col_Morocco"]; // This is from your question.

  var sheet = SpreadsheetApp.getActiveSheet();
  var obj = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].reduce((ar, h, i) => [...ar, { from: i   1, to: order.indexOf(h)   1 }], []).sort((a, b) => a.to > b.to ? 1 : -1);
  for (var i = 0; i < obj.length; i  ) {
    if (obj[i].from != obj[i].to) {
      sheet.moveColumns(sheet.getRange(1, obj[i].from), obj[i].to);
      obj.forEach((e, j) => {
        if (e.from < obj[i].from) obj[j].from  = 1;
      });
    }
  }
}

• When this script is run, the columns are rearranged by order you give. In this case, the text and cell format are also moved.

• When moveColumns(columnSpec, destinationIndex) is used, the indexes of columns are changed after moveColumns(columnSpec, destinationIndex) was run. So, please be careful about this. In the above script, the changed indexes are considered.

References:

• moveColumns(columnSpec, destinationIndex)
• reduce()
• forEach()

CodePudding user response：

Order Columns:

function ordercols() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const [h,...vs] = sh.getDataRange().getValues();
  const idx = {};
  h.forEach((h,i) => idx[h]=i);
  const o = vs.map(r => [r[idx['COL4']],r[idx['COL3']],r[idx['COL2']],r[idx['COL1']]]);
  sh.clearContents();
  o.unshift(['COL4','COL3','COL2','COL1']);
  sh.getRange(1,1,o.length,o[0].length).setValues(o);
}

Data:

COL1	COL2	COL3	COL4
24	5	2	9
16	0	13	18
22	24	23	16
12	12	4	17
6	20	17	14
7	13	4	2
2	20	4	22
3	5	3	4
16	5	7	23

ReOrdered:

COL4	COL3	COL2	COL1
9	2	5	24
18	13	0	16
16	23	24	22
17	4	12	12
14	17	20	6
2	4	13	7
22	4	20	2
4	3	5	3
23	7	5	16