我想知道我如何能从谷歌表分组的信息，这样我就可以形成小组与3名成员。我想能够复制一个注册人的详细信息（如果团队成员的数量在3以下），然后粘贴到上面的列，这样它就可以形成一个3人的团队。排序后的列表应该是这样的。
未排序数组示例
排序数组示例
我尝试过使用一些googlesheets函数，比如vlookup，您建议使用什么java/pythonapi来解决这个问题。
"entry": [{
"id": {
"$t": "https://spreadsheets.google.com/feeds/list/1sjPsuoIZOJWRLLcJlvZbvKuHnGn6KfaqLLa3rpSXmR0/od6/public/values/cokwr"
},
"updated": {
"$t": "2020-07-28T09:58:17.850Z"
},
"category": [{
"scheme": "http://schemas.google.com/spreadsheets/2006",
"term": "http://schemas.google.com/spreadsheets/2006#list"
}],
"title": {
"type": "text",
"$t": "Dan"
},
"content": {
"type": "text",
"$t": "registrantlastname: M, registrantsemailadress: dan@gmail.com, secondmemebersfullname: MatL, secondmembersemail: mal@gmail.com, thirdmembersfullname: Oscar, thirdmembersemailaddress: radio@hotmail.com, teammembers: 3"
},
"link": [{
"rel": "self",
"type": "application/atom+xml",
"href": "https://spreadsheets.google.com/feeds/list/1sjPsuoIZOJWRLLcJlvZbvKuHnGn6KfaqLLa3rpSXmR0/od6/public/values/cokwr"
}],
"gsx$registrantfirstname": {
"$t": "Dan"
},
"gsx$registrantlastname": {
"$t": "M"
},
"gsx$registrantsemailadress": {
"$t": "dan@gmail.com"
},
"gsx$secondmemebersfullname": {
"$t": "MatL"
},
"gsx$secondmembersemail": {
"$t": "mal@gmail.com"
},
"gsx$thirdmembersfullname": {
"$t": "Oscar"
},
"gsx$thirdmembersemailaddress": {
"$t": "radio@hotmail.com"
},
"gsx$teammembers": {
"$t": "3"
}
}, {
"id": {
"$t": "https://spreadsheets.google.com/feeds/list/1sjPsuoIZOJWRLLcJlvZbvKuHnGn6KfaqLLa3rpSXmR0/od6/public/values/cpzh4"
},
"updated": {
"$t": "2020-07-28T09:58:17.850Z"
},
"category": [{
"scheme": "http://schemas.google.com/spreadsheets/2006",
"term": "http://schemas.google.com/spreadsheets/2006#list"
}],
"title": {
"type": "text",
"$t": "Mariana"
},
"content": {
"type": "text",
"$t": "registrantlastname: Fake, registrantsemailadress: mari.fake@itesm.ru, secondmemebersfullname: Johnathan, secondmembersemail: jan@gmail.com, teammembers: 2"
},
"link": [{
"rel": "self",
"type": "application/atom+xml",
"href": "https://spreadsheets.google.com/feeds/list/1sjPsuoIZOJWRLLcJlvZbvKuHnGn6KfaqLLa3rpSXmR0/od6/public/values/cpzh4"
}],
"gsx$registrantfirstname": {
"$t": "Mariana"
},
"gsx$registrantlastname": {
"$t": "Fake"
},
"gsx$registrantsemailadress": {
"$t": "mari.fake@itesm.ru"
},
"gsx$secondmemebersfullname": {
"$t": "Johnathan"
},
"gsx$secondmembersemail": {
"$t": "jan@gmail.com"
},
"gsx$thirdmembersfullname": {
"$t": ""
},
"gsx$thirdmembersemailaddress": {
"$t": ""
},
"gsx$teammembers": {
"$t": "2"
}
}, {
"id": {
"$t": "https://spreadsheets.google.com/feeds/list/1sjPsuoIZOJWRLLcJlvZbvKuHnGn6KfaqLLa3rpSXmR0/od6/public/values/cre1l"
},
"updated": {
"$t": "2020-07-28T09:58:17.850Z"
},
"category": [{
"scheme": "http://schemas.google.com/spreadsheets/2006",
"term": "http://schemas.google.com/spreadsheets/2006#list"
}],
"title": {
"type": "text",
"$t": "Lorem"
},
"content": {
"type": "text",
"$t": "registrantlastname: Ipsum, registrantsemailadress: lorem@gmail.com, teammembers: 1"
},
"link": [{
"rel": "self",
"type": "application/atom+xml",
"href": "https://spreadsheets.google.com/feeds/list/1sjPsuoIZOJWRLLcJlvZbvKuHnGn6KfaqLLa3rpSXmR0/od6/public/values/cre1l"
}],
"gsx$registrantfirstname": {
"$t": "Lorem"
},
"gsx$registrantlastname": {
"$t": "Ipsum"
},
"gsx$registrantsemailadress": {
"$t": "lorem@gmail.com"
},
"gsx$secondmemebersfullname": {
"$t": ""
},
"gsx$secondmembersemail": {
"$t": ""
},
"gsx$thirdmembersfullname": {
"$t": ""
},
"gsx$thirdmembersemailaddress": {
"$t": ""
},
"gsx$teammembers": {
"$t": "1"
}
}, {
"id": {
"$t": "https://spreadsheets.google.com/feeds/list/1sjPsuoIZOJWRLLcJlvZbvKuHnGn6KfaqLLa3rpSXmR0/od6/public/values/chk2m"
},
"updated": {
"$t": "2020-07-28T09:58:17.850Z"
},
"category": [{
"scheme": "http://schemas.google.com/spreadsheets/2006",
"term": "http://schemas.google.com/spreadsheets/2006#list"
}],
"title": {
"type": "text",
"$t": "Ki"
},
"content": {
"type": "text",
"$t": "registrantlastname: C, registrantsemailadress: abc@live.com, teammembers: 1"
},
"link": [{
"rel": "self",
"type": "application/atom+xml",
"href": "https://spreadsheets.google.com/feeds/list/1sjPsuoIZOJWRLLcJlvZbvKuHnGn6KfaqLLa3rpSXmR0/od6/public/values/chk2m"
}],
"gsx$registrantfirstname": {
"$t": "Ki"
},
"gsx$registrantlastname": {
"$t": "C"
},
"gsx$registrantsemailadress": {
"$t": "abc@live.com"
},
"gsx$secondmemebersfullname": {
"$t": ""
},
"gsx$secondmembersemail": {
"$t": ""
},
"gsx$thirdmembersfullname": {
"$t": ""
},
"gsx$thirdmembersemailaddress": {
"$t": ""
},
"gsx$teammembers": {
"$t": "1"
}
}, {
"id": {
"$t": "https://spreadsheets.google.com/feeds/list/1sjPsuoIZOJWRLLcJlvZbvKuHnGn6KfaqLLa3rpSXmR0/od6/public/values/ciyn3"
},
"updated": {
"$t": "2020-07-28T09:58:17.850Z"
},
"category": [{
"scheme": "http://schemas.google.com/spreadsheets/2006",
"term": "http://schemas.google.com/spreadsheets/2006#list"
}],
"title": {
"type": "text",
"$t": "jane"
},
"content": {
"type": "text",
"$t": "registrantlastname: Ong, registrantsemailadress: j.ong@live.com, secondmemebersfullname: john Ong, secondmembersemail: j.ong@live.com, teammembers: 2"
},
"link": [{
"rel": "self",
"type": "application/atom+xml",
"href": "https://spreadsheets.google.com/feeds/list/1sjPsuoIZOJWRLLcJlvZbvKuHnGn6KfaqLLa3rpSXmR0/od6/public/values/ciyn3"
}],
"gsx$registrantfirstname": {
"$t": "jane"
},
"gsx$registrantlastname": {
"$t": "Ong"
},
"gsx$registrantsemailadress": {
"$t": "j.ong@live.com"
},
"gsx$secondmemebersfullname": {
"$t": "john Ong"
},
"gsx$secondmembersemail": {
"$t": "j.ong@live.com"
},
"gsx$thirdmembersfullname": {
"$t": ""
},
"gsx$thirdmembersemailaddress": {
"$t": ""
},
"gsx$teammembers": {
"$t": "2"
}
}]
}
1条答案
按热度按时间6qftjkof1#
这个问题应该适用于这样的大型数据集。
从这里开始，您可以通过选择右上角视图中的过滤器图标来收集2人一组中的所有人，并使用vlookup将他们分成1人一组，然后向下拖动以重复。
最后，将1组合在一起并使用hlookup函数来获得正确的名称和索引。对于团队成员，只需输入值（3,1,1）。这将为您提供所需的阵列。