This is a sample script for rearranging columns on Google Spreadsheet using Google Apps Script.
Sample script
In this sample script, the initial columns of “header1”, “header2”, “header3”, “header4” and “header5” are rearranged to “header2”, “header5”, “header1”, “header4”, “header3”. This result can be seen at the above image.
As an important point, in this script, the header titles in the 1st row are used. Please be careful about this.
function myFunction() {
// Please set the final order of headers.
const finalOrder = ["header2", "header5", "header1", "header4", "header3"];
const sheet = SpreadsheetApp.getActiveSheet();
const obj = sheet
.getRange(1, 1, 1, sheet.getLastColumn())
.getValues()[0]
.reduce(
(ar, h, i) => [...ar, { from: i + 1, to: finalOrder.indexOf(h) + 1 }],
[]
)
.sort((a, b) => (a.to > b.to ? 1 : -1));
for (let 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
moveColumns(columnSpec, destinationIndex)is used, the indexes of columns are changed every run ofmoveColumns(columnSpec, destinationIndex). So, it is required to update the current column position. Please be careful about this.
Reference
- I answered this script to this thread on Stackoverflow.