在Google表格中取消透视-乘以标题列和行 [英] Unpivot in Google Sheets - multipe header columns and rows

问题描述

我有一个约20列，最多100行的汇总表，但是我想将其转换为平面列表，以便可以导入数据库.

此解决方案不起作用就我而言，我对JS的了解远远低于对其进行适当调整的能力.

This solution is not working properly in my case and my knowledge of JS is far beneath the ability to adjust it properly.

示例表中有三个标签:

1. 源数据-我当前拥有的虚拟数据
2. 所需结果-我要将源数据转换为什么
3. 我所得到的-使用时得到的结果上面提到的解决方案

Sheet是共享的，因此您可以尝试测试脚本(菜单>脚本>运行).它将自动创建一个新标签.

Sheet is shared, so you can try and test the script (Menu > Script > Run). It would create a new tab automatically.

推荐答案

这是经过一些JS学习和谷歌搜索之后我想到的.如果有人可以建议如何使它更短/更清洁/更简单-我都很高兴.可能还远未达到完美，但这恰恰满足了我的需求.

Here's what I came up with after some JS learning and googling. If anyone can suggest how to make it shorter/cleaner/simpler - I'm all ears. Might be far from perfect, but it does exactly what I need.

function transpose(){
  var source = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SOURCE DATA');
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var numColumns = source.getLastColumn();
  var numRows = source.getLastRow();
  Logger.log('numColumns = ' +numColumns);
  Logger.log('numRows = ' +numRows);


  //GET NUMBER OF HEADERS (PRODUCTS)

  var products = []; // get product models in the first row
  for (var b = 2; b <= numColumns; b++){
    if (source.getRange(1, b).getValue() != "") {
      products.push([source.getRange(1, b).getValue()]); //store
    }
  }


  // PRODUCTS and SITES INTO COLUMNS

  var output = [];

  var sites = []; // get sites list
  for (var a = 3; a <= numRows; a++){
    if (source.getRange(a, 1).getValue() != "") {
      sites.push([source.getRange(a, 1).getValue()]); //store
    }
  }

  for(var p in products){
    for(var s in sites){
      var row = [];
      row.push(sites[s]);
      row.push(products[p]);
      output.push(row);//collect data in separate rows in output array
    }
   }

  var date = Utilities.formatDate(new Date(), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "M/d/yyyy");
  Logger.log('Date = ' +date)
  ss.insertSheet(date,0).getRange(1,1,output.length,output[0].length).setValues(output);

    var newSheet = ss.getSheetByName(date);


  // COPY REGIONS

  var numProducts = products.length; // number of models
  Logger.log('numProducts = ' +numProducts);

  var i = 1;
  var j = 3 // first column number to copy
  do {
    var colC = newSheet.getRange("C1:C").getValues();
    var copyToCell = colC.filter(String).length+1;
    Logger.log('copyTo R = ' +copyToCell);

    source.getRange(3,2,numRows-2,1).copyTo(newSheet.getRange(copyToCell,3), {contentsOnly:true});
    i++;

    source.getRange(3,j,numRows-2,2).copyTo(newSheet.getRange(copyToCell,4), {contentsOnly:true});
    j+=2;  
  }
  while (i <= numProducts);
  while (j < numColumns);


  // SORT BY SITE AND PRODUCT

  newSheet.getDataRange().sort([1, 2]);
}

这篇关于在Google表格中取消透视-乘以标题列和行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！