Moving a column in Google Spreadsheet

google-apps-script, google-sheets, javascript

Solution

Here's an approach that will be much faster than using the SpreadsheetApp methods. Instead of the copy / delete / insert / paste operations, you can take a copy of the whole sheet at once, modify in memory, then write the modified data back out.

We start with a `moveColumn()` function that has the same signature as in ssurendr's answer. However, its job is just to read the spreadsheet data, pass it to be manipulated by a generic javascript function, then write the result back out to the sheet.

/**
 * Move column in current spreadsheet
 *
 * @param {int}   iniCol  Source column index (1-based)
 * @param {int}   finCol    Destination column index (1-based)
 */
function moveColumn(iniCol, finCol) {
  var dataRange = SpreadsheetApp.getActiveSheet().getDataRange();
  var data = arrayMoveColumn( dataRange.getValues(), iniCol - 1, finCol - 1 );
  dataRange.setValues(data);
}

The next function, `arrayMoveColumn()`, is not specific to Google spreadsheets. It will move a column in any two-dimensional array. Javascript arrays are indexed starting at `0`, while Spreadsheet methods use `1-based` indexes. We've added some basic error checking, although it's not fool-proof.

The workhorse in this function is the Array.splice() method, which is used to remove and insert elements in the array.

/**
 * Move content of a "column" in a 2-d Array.
 *
 * @param {array}  data  Two-dimensional matrix (array with no null values)
 *                       Array content is changed by this function.
 * @param {int}    from  Source column index (0-based)
 * @param {int}    to    Destination column index (0-based)
 *
 * @return {array}       Resulting array (for chaining)
 */
function arrayMoveColumn( data, from, to ) {
  // Parameter error checking
  if ( !( data instanceof Array && data[0] instanceof Array ) ) throw new TypeError( 'need 2d array' );
  if ( from >= data[0].length || to >= data[0].length ) throw new Error( 'index out of bounds' );

  for (var row=0; row<data.length; row++) {
    var temp = data[row].splice(from, 1);  // take 'from'
    data[row].splice(to, 0, temp[0]);      // put  'to'
  }
  return data;
}

Problem

Now I feel like I'm asking too many questions on this site :), but it seems like no has asked this question. IF they did, please link me there. I just want to move a particular column to another place (before or after another column) in the same sheet. I see options to hide, insert, delete, etc. But something as fundamental as moving, I don't see. Do I really have to get the range of the column data and then copy it, delete the column, insert a column where I want, get the range, then paste the data..? Example: ``` Col A B C D E F G H 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 ``` Move column B to before G say: ``` Col A B C D E F G H 1 3 4 5 6 2 7 8 9 11 12 13 14 10 15 16 ```

Original source