移转细胞同时保持细胞背景

Question

我正在创建一个电子表格来跟踪谁每天都做了什么。由于复杂的原因，我不想进入工作表，必须按行而不是列排序。

我的完整脚本工作很好，除了它清除了每个单元格的背景色时，它转置。我可以手动转换和排序，保留细胞背景(某些细胞必须根据来自另一张纸的输入进行颜色编码)，但这很乏味，这就是为什么人们首先要编写脚本。

我尝试过以各种方式将下面的代码编辑成getBackgrounds()和setBackgrounds()。我的结论是我需要帮助。

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

修改

在以下几个方面的访问背景：

var backgrounds = range.getBackgrounds();

将转置逻辑移到实用程序函数并优化它：

/**
 * 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];
    });
  });
}

将前面的步骤结合到您的主要功能中，干净和简单：

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

您可以只使用内置的转置：

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

如果您想忽略公式，只复制值/格式，

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

参考文献：

• Range#copyTo

Answer 3

只是也要应用背景

forEach作为一个一致的解决方案

/**
 * 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);
}

当前代码的实现

基于此，您可以在代码上实现它。

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