Transposing cells while keeping cell backgrounds

Question

I'm creating a spreadsheet to track who did what on a day to day basis. For complicated reasons I don't want to go into the sheet has to be sorted by the rows rather than the columns.

My full script works great except it clears out the background colors of each cell when it transposes. I could transpose and sort by hand and keep the cell backgrounds (Certain cells have to be color-coded based on input from another sheet) but that's tedious and it's why people script in the first place.

I've tried editing the code below to getBackgrounds() and setBackgrounds() in various ways. I've come to the conclusion that I need help.

function Transpose() {
//This function Transposes it in order to sort since google doesn't let you sort by rows.

  // get all the data in the sheet
  var ss  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet3');
  var range = ss.getDataRange();
  var values = range.getValues();

  // clear existing
  range.clear();

  // transpose it & write it out
  ss.getRange(1,1,values[0].length,values.length)
    .setValues(Object.keys(values[0]).map ( function (columnNumber) {
      return values.map( function (row) {
        return row[columnNumber];
      });
    }));
  }

Answer 1

Modifications

Access backgrounds somewhere along the lines:

var backgrounds = range.getBackgrounds();

Move transpose logic to a utility function and optimize it:

/**
 * Transposes values;
 * @param {Array<Array>} values input Array;
 * @return {Array<Array>} transposed Array;
 */
function transpose(values) {
  return values[0].map(function(col,c){
    return values.map(function(row){
      return row[c];
    });
  });
}

Combine previous steps in your main function, clean and simple:

function Transpose() {

  // get all the data in the sheet
  var ss  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet3');
  var range = ss.getDataRange();

  //access values and backgrounds;
  var values = range.getValues(); 
  var backgrounds = range.getBackgrounds();

  // clear existing;
  range.clear();

  //transpose;
  values = transpose(values);
  backgrounds = transpose(backgrounds);

  //access target range - note that dimensions are inversed;
  var target = ss.getRange(1,1,values.length,values[0].length);

  //set values and backgrounds;
  target.setValues(values);
  target.setBackgrounds(backgrounds);
}

Answer 2

You could just use the inbuilt transpose:

function transpose() {
  var ss  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet3');
  var range = ss.getDataRange();
  range.copyTo(
    ss.getRange('A1'),
    SpreadsheetApp.CopyPasteType.PASTE_NORMAL,
    true
  );
}

If you want to ignore formulas and just copy values/format,

["VALUES","FORMAT"].forEach(function(type){
  range.copyTo(
    ss.getRange('A1'),
    SpreadsheetApp.CopyPasteType["PASTE_" + type ],
    true
  );
})

References:

• Range#copyTo

Answer 3

Just apply backgrounds too

forEach as a consistent solution

/**
 * Transposes with backgrounds
 */
function transpose() {
  // get all the data in the sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet3');
  var range = ss.getDataRange();
  var values = range.getValues();
  var backgrounds = range.getBackgrounds();
  var transposeBackgrounds = [];
  var transposeValues = [];

  values[0].forEach(function(_, i) {
    var rowValues = [];
    var rowBackgrounds = [];
    values.forEach(function(_, j) {
      rowValues.push(values[j][i]);
      rowBackgrounds.push(backgrounds[j][i]);
    });
    transposeValues.push(rowValues);
    transposeBackgrounds.push(rowBackgrounds);
  });

  // clear existing
  range.clear();

  // transpose it & write it out
  ss.getRange(1, 1, transposeValues.length, transposeValues[0].length)
    .setValues(transposeValues)
    .setBackgrounds(transposeBackgrounds);
}

The implementation of the current code

Based of that you can implement it on your code

function Transpose() {
  // This function Transposes it in order to sort since bla-bla-bla

  // get all the data in the sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet3');
  var range = ss.getDataRange();
  var values = range.getValues();
  var backgrounds = range.getBackgrounds();
  var transposeBackgrounds = [];

  // clear existing
  range.clear();

  // transpose it & write it out
  ss.getRange(1, 1, values[0].length, values.length)
    .setValues(
      Object.keys(values[0]).map(function(columnNumber) {
        var rowBackgrounds = [];
        var newRow = values.map(function(row, i) {
          rowBackgrounds.push(backgrounds[i][columnNumber]);
          return row[columnNumber];
        });
        transposeBackgrounds.push(rowBackgrounds);
        return newRow;
      })
    )
    .setBackgrounds(transposeBackgrounds);
}