google-apps-script - 使用 Apps 脚本转置并复制到另一张工作表
问题描述
我需要使用 Apps 脚本根据合并的标题将列数据转换为行。下面是我的输入和预期输出的视图，
输入
输出
到目前为止我写的：
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getRange("A1:AO1");
var mergedValues = [];
//get the header added to the array
mergedValues.push(sheet.getRange("A2:I2").getValues());
Logger.log(mergedValues);
var mergedRanges = range.getMergedRanges();
for (var i = 0; i < mergedRanges.length; i++) {
var calcA1Notation = "A"+(i+3) + ":C"+(i+3);
var monA1Notation = "D"+(i+3) + ":F"+(i+3);
//Load the Transpose values into the array
mergedValues.push([[
sheet.getRange(calcA1Notation).getValues().toString(),
mergedRanges[i].getDisplayValue(),
sheet.getRange(monA1Notation).getValues().toString()
]]);
}
Logger.log(mergedValues[0].length);
for (var i = 0; i < mergedValues.length; i++){
//Writes to the lastrow+1 of the sheet
sheet.getRange(sheet.getLastRow()+1, 1).setValue(mergedValues[i]);
}
}
你们能帮我修改谷歌脚本以生成预期的结果吗？
解决方案
该问题包括“转置”一词，但这是一种误导。
提问者的目标是直截了当的；将单元格从一张纸复制到另一张纸。有一个附带条件，将一张工作表中的列标题作为目标范围中的单元格包含在内。
提问者演示了代码，尽管他们没有解释这在多大程度上是有目的的。该代码采用三列数据并将值连接到一个单元格中。充其量，人们可能会认为这是一份早期草案。
源数据的引用并不复杂；获取月份名称是主要的并发症。我使用两个循环来处理源表上的行，因为提问者的预期结果是数据应该按月排序。
我本可以构建一个例程将月份字符串值转换为数值，然后按该值排序（我当然考虑过）-但我没有；）
月份名称大写，提问者的结果使用 TitleCase。同样，我可以建立一个例程来转换案例，我确实花了一些时间尝试。但最后我决定这不是一个高优先级。
function so5273586002() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Declare the two sheets
var sourcesheet = ss.getSheetByName("Input");
var targetsheet = ss.getSheetByName("Output");
// Get the respective starting row and ending rows.'' the target last row is declared in the loop.
var sourcestartrow = 3;
var targetstartrow = 2;
var sourcelastrow = sourcesheet.getLastRow();
// get the the data
var sourcerange = sourcesheet.getDataRange();
var sourcevalues = sourcerange.getValues();
// rubric for copying data.
// each row of the source must create two rows in the target - one row for each month
// the first three columns are repeats on both rows
// each row includes the source data as well as the month name
// target row #1
// source columns A, B & C to target A,B,C
// Month#1; value in D1 Source=> Target Column D (4)
// source columns DEF to target E F G
// target row #2
// source columns A, B & C to target A,B,C
// Month#2: value in G1 Source=> Target D (4)
// source fields G, H I to target E F G
// the questioner's prefered layout is that all the rows are sorted by month; to achive this, I used two loops
// the first to do the first month; the second to do the second month
for (i = sourcestartrow; i < (sourcelastrow + 1); i++) {
// get the last row for the target
var targetlastrow = targetsheet.getLastRow();
// Columns A, B and C -> Columns A, B and C
var targetRange = targetsheet.getRange(targetlastrow + 1, 1); //target: column =A, row = lastrow plus one
var sourcetest = sourcesheet.getRange(i, 1, 1, 3).copyTo(targetRange); // range = active row, column=A, 1 row, 3 columns, copy to SheetTracker
//Logger.log("source range is "+sourcesheet.getRange(i, 1, 1, 3).getA1Notation()+", target range is "+targetsheet.getRange(targetlastrow + 1, 1).getA1Notation());//DEBUG
// Month Name from the header
var targetRange = targetsheet.getRange(targetlastrow + 1, 4); //target: column =D, (month) row = lastrow plus one
var sourcetest = sourcesheet.getRange(1, 4).copyTo(targetRange, {
contentsOnly: true
}); // range = active row, column=A, 1 row, 3 columns, copy to SheetTracker
// Logger.log("source range is "+sourcesheet.getRange(1, 4).getA1Notation()+", target range is "+targetsheet.getRange(targetlastrow + 1, 4).getA1Notation());//DEBUG
// Month details
// Columns D E and F -> Columns E F and G
var targetRange = targetsheet.getRange(targetlastrow + 1, 5); //target: column =E, row = lastrow plus one
var sourcetest = sourcesheet.getRange(i, 4, 1, 3).copyTo(targetRange, {
contentsOnly: true
}); // range = active row, column=D(4), 1 row, 3 columns, copy to SheetTracker
// Logger.log("source range is "+sourcesheet.getRange(i, 4, 1, 3).getA1Notation()+", target range is "+targetsheet.getRange(targetlastrow + 1, 5).getA1Notation());//DEBUG
} // end loop#1
//Loop#2 to generate rows for the second month
for (i = sourcestartrow; i < (sourcelastrow + 1); i++) {
// get the last row for the target
var targetlastrow = targetsheet.getLastRow();
// Columns A, B and C -> Columns A, B and C
var targetRange = targetsheet.getRange(targetlastrow + 1, 1); //target: column =A, row = lastrow plus one
var sourcetest = sourcesheet.getRange(i, 1, 1, 3).copyTo(targetRange); // range = active row, column=A, 1 row, 3 columns, copy to SheetTracker
//Logger.log("source range is "+sourcesheet.getRange(i, 1, 1, 3).getA1Notation()+", target range is "+targetsheet.getRange(targetlastrow + 1, 1).getA1Notation());//DEBUG
// Month Name from the header
var targetRange = targetsheet.getRange(targetlastrow + 1, 4); //target: column =D, (month) row = lastrow plus one
var sourcetest = sourcesheet.getRange(1, 7).copyTo(targetRange, {
contentsOnly: true
}); // range = active row, column=G, 1 row, 3 columns, copy to SheetTracker
//Logger.log("source range is "+sourcesheet.getRange(1, 7).getA1Notation()+", target range is "+targetsheet.getRange(targetlastrow + 1, 4).getA1Notation());//DEBUG
// Month details
// Columns G H and I -> Columns E F and G
var targetRange = targetsheet.getRange(targetlastrow + 1, 5); //target: column =E, row = lastrow plus one
var sourcetest = sourcesheet.getRange(i, 7, 1, 3).copyTo(targetRange, {
contentsOnly: true
}); // range = active row, column=D(4), 1 row, 3 columns, copy to SheetTracker
// Logger.log("source range is "+sourcesheet.getRange(i, 7, 1, 3).getA1Notation()+", target range is "+targetsheet.getRange(targetlastrow + 1, 5).getA1Notation());//DEBUG
} // end loop#2
}
此屏幕截图显示源表（“输入”）。
这些屏幕截图显示了运行代码之前和之后的目标表（“输出”）。
更新
正如我在评论中指出的那样，早期的草案缺少两点：
1) 它效率低下并且遵循不良做法，因为它在创建每个字段时写入了每个字段的值。更合适的方法是将数据写入数组，然后在逐行处理完成后将数组复制到目标范围。
2）代码由两个循环组成，以适应演示数据中的 2 个月。然而，这是一个不切实际的结果，因为实际上每行中可能会有任意月的数据。同样，糟糕的做法是，当更合适的方法是假设任意数量的月份数据时。更有效的方法是在遍历每一行的同时构建一个数据数组。
这次修订克服了这两个缺点。
此外，由于月份名称没有按任何有意义的顺序排序，因此我添加了一个数字月份 id，可用于在输出数据表中进行过滤和排序。
function so5273586003() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Declare the two sheets
var sourcesheet = ss.getSheetByName("Input");
var targetsheet = ss.getSheetByName("Output");
// Get the respective starting row and ending rows.'' the target last row is declared in the loop.
var targetstartrow = 2;
var sourcestartrow = 2;
var sourcelastrow = sourcesheet.getLastRow();
var sourcelastcolumn = sourcesheet.getLastColumn();
//Logger.log("the last row is "+sourcelastow+", and the last column is "+sourcelastcolumn);
// get the the data
var sourcerange = sourcesheet.getDataRange();
var sourcevalues = sourcerange.getValues();
var sourcelength = sourcevalues.length;
var i = 0;
var m = 0;
var month = 1;
var dataarray = [];
var masterarray = [];
// start loop by row
for (i = sourcestartrow; i < (sourcelastrow); i++) {
// start loop by month (within row)
for (m = 0; m <= (sourcelastcolumn - 6); m = m + 3) {
dataarray = [];
// add first three columns
dataarray.push(sourcevalues[i][0]);
dataarray.push(sourcevalues[i][1]);
dataarray.push(sourcevalues[i][2]);
//add the month name
dataarray.push(sourcevalues[0][3 + m]);
//add month data
dataarray.push(sourcevalues[i][3 + m]);
dataarray.push(sourcevalues[i][4 + m]);
dataarray.push(sourcevalues[i][5 + m]);
//create month id
switch (sourcevalues[0][3 + m]) {
case "JULY":
month = 1;
break;
case "AUGUST":
month = 2;
break;
case "SEPTEMBER":
month = 3;
break;
case "OCTOBER":
month = 4;
break;
case "NOVEMBER":
month = 5;
break;
case "DECEMBER":
month = 6;
break;
case "JANUARY":
month = 7;
break;
case "FEBRUARY":
month = 8;
break;
case "MARCH":
month = 9;
break;
case "APRIL":
month = 10;
break;
case "MAY":
month = 11;
break;
case "JUNE":
month = 12;
break;
default:
month = 100;
break;
} // end switch
// add the month id to the array (used for sorting)
dataarray.push(month);
// add the data to the master array before zeroing for next month
masterarray.push(dataarray);
} // months loop
} // end row loop
// get the length of the master array
var masterlength = masterarray.length;
// define the target range
var TargetRange = targetsheet.getRange(targetstartrow, 1, masterlength, 8);
// set the array values on the Target sheet
TargetRange.setValues(masterarray);
}
推荐阅读
- python - 如何在不实际将数据发送到 Azure Speech API 的情况下使用 pytest 模拟转录结果
- spring-boot - Spring Cloud Data Flow Apps 未获取外部参数和环境变量
- c# - 如何在c#中抛出异常但不停止进程？
- css - vue js 将scss编译成css
- ruby-on-rails - Ruby on Rails 中的 Rake 与 Rails
- blockchain - “蜜罐”合约中的哪个功能阻止买家出售他们的代币？
- c# - 如何将 System.IO.Stream 放入控制台？
- javascript - 如何移动chartJs图例左侧并更改图表的宽度和高度？
- mongodb - 我已经使用 studio 3T 验证了我的 atlas 连接。连接未通过身份验证
- python - 为什么在 manim 中应用复杂变换的行为很奇怪？