Avoid Errors When Moving Columns in Google Sheets With a Script

One of my day-to-day tasks is to upload a file to our CRM. Unfortunately, the file columns and data formats change all the time and it’s hard to fully automate the process without taking a good look at it first. 

To minimize the time I spend on this task to a few minutes a day, I have written a Google Apps script that takes the file, and after doing the relevant mapping and data cleaning, re-orders the columns I need to send to the CRM and cleans all the rest. 

One issue I soon encountered, was that sometimes one of the columns I wish to see among the first 5 columns first is already in there. And when running the moveColumns() method, the below error was thrown: 

“Exception: The destination index cannot be within the span being moved.”

To understand why I got the error, let’s first understand how moveColumns() works. 

The moveColumns() method accepts two arguments:

1. Columns to move, an array of columns to move, from one column to many. 
2. Where to position the first column of the moved-columns array.

For example, if we have the table below and wish to move columns C and D to be A & B respectively, we could pass their range inside the method like this: moveColumns (“C1:D”, 1).

A	B	C	D
Col1	Col2	Wish to be 1	Wish to be 2

But what if one of our columns was already inside our destination index? In this case, we would get the error message from above.

To avoid it, we can move carefully in three steps:

1. Mapping the current column position on every column movement
2. Moving it to the earliest position possible.
3. Starting the recording from the last

To map the current column position we can use the function below: 

function mapColumnHeaders() {
  let sheet_columns = sheet.getRange(1, 1, 1, sheet.getMaxColumns()).getValues().flat()
  return sheet_columns;
}

Using this function we get an array of all the column names in the sheet. 

Now, we can create a function to reorder the columns. Inside this function we’ll map the current position of the column name we are repositioning:

function reorderColumns(col_names_order_arr, col_to_start) {
  for (let i = 0; i < col_names_order_arr.length; i++) {
    let column_names = mapColumnHeaders();
    let original_col_idx = column_names.indexOf(col_names_order_arr[i]) + 1;
    if (original_col_idx > 0) {
      let range = sheet.getRange(1, original_col_idx, sheet.getMaxRows();
        if (i + 1 != original_col_idx) {
          assignment_sheet.moveColumns(range, i + col_to_start);
        }
      }
    }
  }

Now that we have a reordering function, we can start moving the columns one by one in a regular loop, where we send the latest column we need first. There are different ways to accomplish this, such as by writing a descending loop or writing the array in reversed order. Here I chose the latter, but feel free to use the method that suits you.

let cols_to_move = [‘Col 3’, ‘Col 2’, ‘Col 1’]

for (let i = 0; i < cols_to_move.length; i++) {
  reorderColumns([cols_to_move[i]], 2);
}

And here’s the full code: 

function reorderColumns(){
	let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

	let cols_to_move = ['Col 3','Col 2', 'Col 1']
    
    for (let i=0;i<cols_to_move.length;i++){
        reorderColumns([cols_to_move[i]], 2);
    } 
};

// Get an array of all spreadsheet's column names  
function mapColumnHeaders() {
  let sheet_columns = sheet.getRange(1, 1, 1, sheet.getMaxColumns()).getValues().flat()
  return sheet_columns;
}

// Move a column 
function reorderColumns(col_names_order_arr, col_to_start) {
  for (let i = 0; i < col_names_order_arr.length; i++) {
    let column_names = mapColumnHeaders();
    let original_col_idx = column_names.indexOf(col_names_order_arr[i]) + 1;
    if (original_col_idx > 0) {
      let range = sheet.getRange(1, original_col_idx, sheet.getMaxRows();
        if (i + 1 != original_col_idx) {
          sheet.moveColumns(range, i + col_to_start);
        }
      }
    }
  }