我有一个约20列,最多100行的汇总表,但是我想将其转换为平面列表,以便可以导入数据库。
在我的情况下,This solution无法正常工作,而我对JS的了解远远低于对其进行适当调整的能力。
Example sheet中有三个标签:
源数据-我当前拥有的虚拟数据
所需结果-我想要将源数据转换为什么
我得到什么-使用上述the solution时得到的结果
工作表是共享的,因此您可以尝试测试脚本(菜单>脚本>运行)。它将自动创建一个新标签。
最佳答案
这是经过一些JS学习和谷歌搜索后我想到的。如果有人可以建议如何使它更短/更干净/更简单-我都很高兴。可能还远未达到完美,但这恰恰满足了我的需求。
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]);
}