CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
ssurendr
ssurendr

Reputation: 456

Moving a column in Google Spreadsheet

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

Upvotes: 5

Views: 6977

Answers (4)

jpp
jpp

Reputation: 349

News form the future!! You just need to use moveColumns(columnSpec, destinationIndex)

https://developers.google.com/apps-script/reference/spreadsheet/sheet#movecolumnscolumnspec,-destinationindex

Upvotes: 0

Kloso
Kloso

Reputation: 11

If someone wants to replace two columns: (thanks @ssurendr for the basic)

function replaceColumn(Col1, Col2) {
// Ex:
// Col A  B  C  D  E
//     1  2  3  4  5
//     6  7  8  9  10
//     11    12 13 14
// Want to replace Column B & D.
// moveColumn(2,4);
// Col A  D  C  B  E
//     1  4  3  2  5
//     6  9  8  7  10
//     11 13 12    14
if (Col2 > Col1) {
  sh.insertColumnAfter(Col1);
  var iniAftRa = sh.getRange(1, Col2+1, lRow); 
  var finRange = sh.getRange(1, Col1, lRow);
  var finColAftRange = sh.getRange(1, Col1+1, lRow);
  finRange.copyTo(finColAftRange, {contentsOnly:true});
  iniAftRa.copyTo(finRange, {contentrsOnly:true});
  finColAftRange.copyTo(iniAftRa, {contentrsOnly:true});
  sh.deleteColumn(Col1 + 1);  
} else {
  sh.insertColumnAfter(Col2);
  var iniAftRa = sh.getRange(1, Col1+1, lRow); 
  var finRange = sh.getRange(1, Col2, lRow);
  var finColAftRange = sh.getRange(1, Col2+1, lRow);
  finRange.copyTo(finColAftRange, {contentsOnly:true});
  iniAftRa.copyTo(finRange, {contentrsOnly:true});
  finColAftRange.copyTo(iniAftRa, {contentrsOnly:true});
  sh.deleteColumn(Col2 + 1);    
}
}

Upvotes: 0

Mogsdad
Mogsdad

Reputation: 45720

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;
}

Upvotes: 3

ssurendr
ssurendr

Reputation: 456

I guess there is no other way except doing it like I said earlier. Here is a function I made, if some people who come asking for the same thing need it. You can easily modify this to move rows as well. I'm new to Apps Script, so there may be an easier code than this.

function moveColumn(iniCol, finCol) {
  // iniCol - Column of interest. (Integer)
  // finCol - Column where you move your initial column in front of.(Integer)
  // Ex:
  // Col A  B  C  D  E
  //     1  2  3  4  5
  //     6  7  8  9  10
  //     11    12 13 14
  // Want to move Column B in between Column D/E.
  // moveColumn(2,4);
  // Col A  B  C  D  E
  //     1  3  4  2  5
  //     6  8  9  7  10
  //     11 12 13    14
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var lRow = sh.getMaxRows();
  if (finCol > iniCol) {
    sh.insertColumnAfter(finCol);
    var iniRange = sh.getRange(1, iniCol, lRow);
    var finRange = sh.getRange(1, finCol + 1, lRow);
    iniRange.copyTo(finRange, {contentsOnly:true});
    sh.deleteColumn(iniCol);
  }
  else {
    sh.insertColumnAfter(finCol);
    var iniRange = sh.getRange(1, iniCol + 1, lRow);
    var finRange = sh.getRange(1, finCol + 1, lRow);
    iniRange.copyTo(finRange, {contentsOnly:true});
    sh.deleteColumn(iniCol + 1);    
  }
}

I figured that ideally anything we can do in google spreadsheet in the frontend could be done programmaticly. (Like moving a column or row.)

Upvotes: 2

Related Questions