google scripts move row to another spreadsheet reordering the columns

Question

I have a 4 sheet Google Sheets workbook. The 'Main' sheet has the data dumped into the first 11 columns automatically. The 12th column is set up as the manual move trigger, to move selected column data, on a per row basis, to the target sheet (one of the other 3 sheets). I'm able to move only the columns that I want to the selected target sheet, for example columns 1, 4, 9 and 10. But those are the columns that get populated in the target sheet when the data moves. I want the data to move into the first 4 columns of the target sheet instead. I've searched a number of forums and can't find if this is even possible to do. This is my current script:

function onEdit() {

  // base definitions
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var editedSheet = ss.getActiveSheet();
  var editedCell = editedSheet.getActiveCell();
  var wordInCell = editedCell.getValue().toString();
  var moveSheet = "Main";
  var moveColumn = 12;
  var moveWords = ["move5x", "move6x", "moveas"];
  var targetSheets = ["5x", "6x", "Assigned"];

  // if the edit was mot in the moveSheet jump out
  var sheetIndex = moveSheet.indexOf(editedSheet.getName());
  if (sheetIndex == -1) return;

  // if the edit was mot in the move column jump out
  if (editedCell.getColumn() != moveColumn) return;

  // if the value in editedCell isn't one of the move words, jump out 
  var moveWordsIndex = moveWords.indexOf(wordInCell);
  if (moveWordsIndex == -1) return;

  // if the value in editedCell is one of the move words, set up columnsToMove
  var columnsToMove;
  if (moveWordsIndex == 0 || moveWordsIndex == 1) {columnsToMove = [[1, 4], [8, 8]]};
  if (moveWordsIndex == 2) {columnsToMove = [[1, 1], [4, 4], [9, 10]]};

  // copy data from editedSheet's sourceRow to targetSheet's targetRow
  var sourceRow = editedCell.getRow();
  var targetSheet = ss.getSheetByName(targetSheets[moveWordsIndex]);
  var targetRow = targetSheet.getLastRow();
  targetSheet.insertRowAfter(targetRow++);
  for (var i = 0; i < columnsToMove.length; i++) {
    var startColumn = columnsToMove[i][0];
    var numColumns = columnsToMove[i][1] - startColumn + 1;
    var numCopiedColumns = columnsToMove.length;
    var rangeToCopyFrom = editedSheet.getRange(sourceRow, startColumn, 1, numColumns);
    var rangeToCopyTo = targetSheet.getRange(targetRow, startColumn, 1, numColumns);
    rangeToCopyFrom.copyTo(rangeToCopyTo);
  }
}

I have tried manipulating rangeToCopyTo every way I could think of, but the results are never what I'm looking for. Does anyone know if reordering selected columns is even possible? If it is, am I at least on the right track to doing it? Thanks

<iframe src="docs.google.com/a/meditech.com/spreadsheets/d/…> — bobm
Hi @ben, Since this is the first time I've posted to this forum, I'm not sure if you can see the spreadsheet or not. If you can, I have shown the desired result in row 2 of sheets: 5x, 6x and Assigned. Row 3 of each sheet shows the actual result. — bobm

a-change a-change · Accepted Answer · 2016-07-08T20:29:20

Why don't you set your target range's start column to 1?

var rangeToCopyTo = targetSheet.getRange(targetRow, 1, 1, numColumns);

It should do the trick if I got you right. You could also go with .getValues() and .setValues() stuff that works in a pretty similar way.

And there's also a way to you use separate .setValue() function:

 for (var i = 0; i < columnsToMove.length; i++) {
    var startColumn = columnsToMove[i][0];
    var numColumns = columnsToMove[i][1] - startColumn + 1;
    var numCopiedColumns = columnsToMove.length;
    var rangeToCopyFrom = editedSheet.getRange(sourceRow, startColumn, 1, numColumns).getValues(); 
    for (var i = 0; i < numCopiedColumns; i++) {
       //here you insert your values into first 'numCopiedColumns' columns in the targetRow
        targetSheet.getRange(targetRow, i + 1).setValue(rangeToCopyFrom[i]); 
    }
 }

It will probably be more time-consuming as you are performing .setValue() operation for each separate value instead of pasting the data as a whole but this can give you more freedom in choosing the target columns.

google scripts move row to another spreadsheet reordering the columns

1 Answers