google spreadsheet column to row conversion not working

Question

I have a column of test data (I cut down my actual app) One Two Three Four Five and named the column's range 'column'. I have another spot on the sheet I named 'rowBegin'. I want the column as a row starting at rowBegin.

function columnToRow() {
  var ss = SpreadsheetApp.getActive() ;
  var sheet = ss.getActiveSheet() ;
  var column = [] ;
  column = ss.getRange('Column').getValues() ;
  var row = [] ;
  for ( i = 0 ; i < column.length ; i ++ ) {
    Logger.log('column [0,' + i + "] = " + column[0,i]);
    row [i,0] = column[0,i] ;
  }
  Logger.log('column length = ' + column.length);
  for ( i = 0 ; i < column.length ; i++ )
      Logger.log('row [' + i + ",0] = " + row[i,0]);
  
  var rowBegin = ss.getRange('RowBegin');
  var rowStart = rowBegin.getRow();
  var colStart = rowBegin.getColumn();
  var rangeRow = sheet.getRange(rowStart, colStart, 1, column.length ) ;
  rangeRow.setValues(row);
}

The Logger output is

[18-05-28 15:11:47:534 PDT] column [0,0] = One
[18-05-28 15:11:47:535 PDT] column [0,1] = Two
[18-05-28 15:11:47:535 PDT] column [0,2] = Three
[18-05-28 15:11:47:536 PDT] column [0,3] = Four
[18-05-28 15:11:47:536 PDT] column [0,4] = Five
[18-05-28 15:11:47:537 PDT] column length = 5
[18-05-28 15:11:47:537 PDT] row [0,0] = Five
[18-05-28 15:11:47:538 PDT] row [1,0] = Five
[18-05-28 15:11:47:538 PDT] row [2,0] = Five
[18-05-28 15:11:47:539 PDT] row [3,0] = Five
[18-05-28 15:11:47:540 PDT] row [4,0] = Five

So why isn't the simple column/row inversion working? The final setValues fails with "Incorrect range width, was 1 but should be 5..."

Can you give a full line? row = column.transpose() did not work, row = transpose(column) did not work. Says "transpose is not defined..." — Acmene Gymilis
I'm getting the column from importhtml, storing in rows, so can't use a static transpose, the above example was cut down from a larger app. — Acmene Gymilis

Acmene Gymilis Acmene Gymilis · Accepted Answer · 2018-05-28T22:39:34

This works:

function columnToRow() {
  var ss = SpreadsheetApp.getActive() ;
  var sheet = ss.getActiveSheet() ;
  var column = [] ;
  column = ss.getRange('Column').getValues() ;
  var row = [] ;
  for ( i = 0 ; i < column.length ; i ++ )
    row [i] = column[0,i] ;
  Logger.log('column length = ' + column.length);
  
  var rowMatrix = [] ;
  rowMatrix[0] = row ;
  var rowBegin = ss.getRange('RowBegin');
  var rowStart = rowBegin.getRow();
  var colStart = rowBegin.getColumn();
  var rangeRow = sheet.getRange(rowStart, colStart, 1, column.length ) ;
  rangeRow.setValues(rowMatrix);
}

Just loading the column values into a 1 dimension array, then inserting that as the value for another array, finally made it 2D in the right shape.

google spreadsheet column to row conversion not working

1 Answers