Rearranging Columns on Google Spreadsheet using Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

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 of moveColumns(columnSpec, destinationIndex). So, it is required to update the current column position. Please be careful about this.

Reference

 Share!