I got this VBA macro from here it reorders columns from an array of column headers even if there are hundreds of columns in a moment (at least in excel)
from this part:
x = GetOrder(rng, myOrder, RemoveRest:=False, Before:=False
If Before:=false then the columns are reordered to the end of the range, and if true then reordered to the beginning of the range
If RemoveRest:=true then only the columns listed in the array of headers to be reordered myOrder = Array("From", "To", "Name From", "Name To") will be outputed
I want to use it as a Google script (I am using Googles V8)
Sub ReOrderColumns()
Dim a, x, rng As Range, myOrder, ws As Worksheet
Set ws = ThisWorkbook.Sheets("Helper_1Filted")
Set rng = ws.Cells(1).CurrentRegion
a = rng.Value
myOrder = ("Finished", "Progress", "StartDate", "Status", "EndDate")
x = GetOrder(rng, myOrder, RemoveRest:=False, Before:=False) '<-- change bold part as per your need.
Application.ScreenUpdating = False
With rng
x = Application.Index(.Value, Evaluate("row(1:" & rng.Rows.Count & ")"), x)
.ClearContents
.Resize(, UBound(x, 2)).Value = x
.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub
Function GetOrder(rng As Range, myOrder, RemoveRest As Boolean, Optional Before As Boolean = True)
Dim a, x, i As Long, ii As Long, ub As Long, myList, n As Long, t As Long
ReDim myList(1 To 2, 1 To rng.Columns.count)
myList = rng.Rows(1)
With Application
x = filter(.IfError(.Match(myOrder, .Index(myList, 1, 0), 0), False), False, 0)
If RemoveRest Then
GetOrder = x
Else
ReDim a(1 To UBound(myList, 2))
ub = UBound(x): ReDim x(1 To rng.Columns.count)
For i = 1 To UBound(myList, 2)
x = .Match(myList(1, i), myOrder, 0)
If IsNumeric(x) Then
If Before = False Then
a(x + IIf(Before, 0, UBound(myList, 2) - ub - 1)) = i
Else
a(x + IIf(Before, 0, ub)) = i
End If
Else
n = n + 1
a(n + IIf(Before, ub + 1, 0)) = i
End If
Next
GetOrder = a
End If
End With
End Function
This is my attempt to translate the first part
function ReOrderColumns() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sht = ss.getSheetByName("Elements");
const rng = sht.getDataRange();
var a = rng.getDisplayValues();
//Array of column headers to reordoer
const myOrder = ["Finished", "Progress", "StartDate", "Status", "EndDate"]
var x = GetOrder(rng, myOrder, RemoveRest:=False, Before:=False) //<-- change bold part as per your need.
x = Application.Index(rng.Value, Evaluate("row(1:" & rng.Rows.Count & ")"), x) //<-- Do not now what to do with Evaluate
sht.Clear()
rng.setValues(x)
}
Thanks in advance for any assistance with this
Here is a Google Sheet with data if helpful https://docs.google.com/spreadsheets/d/1xmIs6Aur-1ruI3iPZeJvvQXhhA4j8WowP44-tr-dh90/edit?usp=sharing