if-statement - 脚本如果然后在两张纸之间匹配
问题描述
我想要做的是把这个公式=iferror(if(match($A2&F$1,arrayformula(Transpose!B$3:B&Transpose!B$1),0),"P"),"")
变成谷歌表格的脚本。本质上,该公式在 F 之后的每隔一列和 2 下的每一行中运行。
例如在 HI 列有
=iferror(if(match($A2&H$1,arrayformula(Transpose!C$3:C&Transpose!C$1),0),"P"),"")
在列姬有=iferror(if(match($A2&J$1,arrayformula(Transpose!D$3:D&Transpose!D$1),0),"P"),"")
等。我现在的方式是将这个公式粘贴到其他每一列的每个单元格中,当我添加更多数据时,我必须调整公式,所以它需要太长时间。我已经附上了工作簿,所以你可以看到我在说什么。任何帮助,将不胜感激。谢谢
https://docs.google.com/spreadsheets/d/1ZBrg4XoeL21mXwn93XT35kgjrE5pqx9cd3xEvfojIXM/edit?usp=sharing
我努力了
var ss=SpreadsheetApp.getActive().getSheetByName('Transpose')
var Ts=SpreadsheetApp.openById('1ZBrg4XoeL21mXwn93XT35kgjrE5pqx9cd3xEvfojIXM').getSheetByName('Attendance')
var meeting = ss.getRange("B3:B37").getValues();
var targetNames = Ts.getRange("A2:A37").getValues();
var writtingRange = Ts.getRange("F2:F37").getValues();
var updatedUser = targetNames;
var writtingColumn = -1;
var coolMatrix;
for (var i = 0; i < meeting.length; i++) {
var name = meeting[i][0]
for (var j = 0; j < targetNames.length; j++) {
if (targetNames[j][0] == name) {
for (var z = 0; z < writtingRange[0].length; z++) {
if (writtingRange[j][z] == '' && writtingColumn < 0) {
writtingColumn = z;
break;
}
}
writtingRange[j][writtingColumn] = meeting[i][1];
updatedUser[j] = true;
}
}
}
Ts.getRange("F2:F37").setValues(writtingRange);
}
但它给了我名称匹配的空白和名称不匹配的未定义“未定义”我也知道这个脚本只适用于一列而不是其他每一列。
解决方案
我对工作表名称中的空格有疑问。这是因为=transpose(split(B2,","))
,它将每个列表拆分为分隔名称,
。问题是 coma 后面有一个空格,它会混淆名称之间的比较。当名称位于第一个位置时,它没有空格,但如果它位于另一个列表的另一个位置,它就会有。
我通过再次获取每个列表并进行替换以删除每个名称的第一个空格来解决此问题。此外,您应该删除工作表与会者列表中每个姓名的第一个空格。
function myfunction() {
var sprsheet = SpreadsheetApp.getActiveSpreadsheet();
var transpose = sprsheet.getSheetByName("Transpose");
var attendance = sprsheet.getSheetByName("Attendance");
//Ranges of Transpose
var colDates = transpose.getRange("B1:I1").getValues();
var attendees = transpose.getRange("B2:I2").getValues();
//Ranges of Attendance
var eventsCol = ["F","H","J","L","N","P","R","T"]; //The dates of Transpose are different from the ones in Attendance, so I added "T" so the script doesn't broke.
var kids = attendance.getRange("A2:A17").getValues(); //Increase this if you add more names ("Abraham Paz" was missing from this list)
loop1:
for (var i = 0; i < eventsCol.length; i++){ //For each column
var list = getIndividuals(attendees[0][i]); //Get the list of each Transpose row 2
loop2:
for (var x = 0; x < kids.length; x++){ //For each name in Attendance
loop3:
for (var y = 0; y < list.length; y++){ //Compare it with each name of the list
if (list[y] == kids[x][0] || list[y] == " " + kids[x][0]){ //Second "or" is in case there is an space in the first position.
attendance.getRange(eventsCol[i]+(x+2)).setValue("P");
break loop3; //Once it finds a name it makes no sense to keep searching. Go back to the next name.
}
}
}
}
}
//Split the list into each name, removing the first space
function getIndividuals(attendees){
var individuals = attendees.split(",");
for (var i = 0; i < individuals.length; i++){
if (individuals[i][0] == " "){
individuals[i][0].replace(" ", "");
}
}
return individuals;
}
推荐阅读
- python - 使用 os.walk 传递变量
- kubernetes - 动态创建 configmap yaml 文件
- android - 如何在中心制作 recyclerView 项目?
- scala - 在scala中递归读取文件
- python - 使用 windows 连接 PySpark 行
- javascript - 如何仅在对象内的所有 id 完成获取后才设置状态
- javascript - 需要帮助将多个音频文件静音
- python - 如何在 AzureStorageCheckpointLeaseManager 类中提供容器的完整路径
- c++ - 继承增加了类的大小
- mysql - 一旦连接限制达到,npm mysql 无法获取新连接