Rearranging Columns on Google Spreadsheet using Google Apps Script

GasTips
Google Apps Script / Spreadsheet

Gists

This is a sample script for rearranging the columns on Google Spreadsheet using Google Apps Script.

Sample script

In this sample script, the columns are rearranged with an array including the rearranged column indexes.

function rearrangeColumns(sheet, ar) {
  var obj = ar.reduce(function(ar, e, i) {
    return ar.concat({ from: e + 1, to: i + 1 });
  }, []);
  obj.sort(function(a, b) {
    return a.to < b.to ? -1 : 1;
  });
  obj.forEach(function(o) {
    if (o.from != o.to) sheet.moveColumns(sheet.getRange(1, o.from), o.to);
    obj.forEach(function(e, i) {
      if (e.from < o.from) obj[i].from += 1;
    });
  });
}

// Please run this function.
function main() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rearrangedColumnIndexes = [4, 3, 1, 0, 2];
  rearrangeColumns(sheet, rearrangedColumnIndexes);
}

  • rearrangedColumnIndexes is the indexes of rearranged columns.

    • When [4, 3, 1, 0, 2] is used, the columns A, B, C, D, E are rearranged to E, D, B, A, C.
    • The 1st index is 0. Please be careful this.

  • This sample script is a simple sample script. So please modify it for your actual situation.

Reference

 Share!