如何将Google Forms响应数据格式化后自动追加到Google Sheets

现有情况 #

• 目前使用Google Forms采集运动测试数据，单条表单提交的响应内容里可包含多名参与人员的成绩信息，原始返回的非结构化文本格式如下：

Name:Karslten [0023]
Level:1
You managed to complete:
Burpies:30
Squats:45
Bench:29
Crunch:43
TotalLoss: 981

Name:Jaeger [0119]
Level:3
You managed to complete:
Burpies:40
Squats:53
Bench:45
Crunch:53
TotalLoss: 1253
Name:Fostyne [0003]
Level:1
You managed to complete:
Burpies:24
Squats:38
Bench:25
Crunch:32
TotalLoss: 758

需求说明 #

• 格式转换要求：通过Google Sheets原生公式实现上述非结构化文本的拆解，转换为标准行列结构的表格：每一名人员的完整数据单独占一行，列固定为姓名（含编号）、等级、波比跳完成数、深蹲完成数、卧推完成数、卷腹完成数、总消耗值。
• 自动化要求：后续Google Forms新增的所有提交条目，解析完成的结构化数据要能自动追加到专门存放结果的工作表中，不需要手动重复处理。
• 调试准备：已创建包含上述测试数据的可编辑Google Sheets样例文件，可直接在文件内调试方案。

数据识别提示：每个人员的独立数据块以Name:字段作为起始标记，块内固定包含所有成绩字段，字段和对应值用冒号分隔，不同人员的数据块之间可能存在空行。

内容的提问来源于stack exchange，提问作者Nadeem Muhammad