时间戳

首页 > 解决方案 > Way to overwrite specific column data based on new form response?

google-sheets - Way to overwrite specific column data based on new form response?

问题描述

I have a Google Form (form 1) which a sales person fills out once a sale is complete. This form gives an option to upload customer documents eg. Copy of Passport, Proof of address etc. These are optional since the customer may not have them at the time of sale.

These uploads are tracked via Google doc links in the main input sheet.

I have a second form (form 2) which is used to upload documents which were never uploaded during the first attempt.

Form 2 might be used more than one occasion as multiple documents may be uploaded at different times.

Is there a way to overwrite blank input data from form 1 with the new input from form 2?

After all, documents are uploaded the end result would look something like the following the third sheet in

https://docs.google.com/spreadsheets/d/1cOSBvgCFYoLYsf4GslyshUoXO0EP_ZbHfuaiGYD8FLk/edit?usp=sharing

标签： google-sheetsgoogle-formsgoogle-sheets-formula

解决方案

我不喜欢更改表单结果。我发现在其他地方做所有事情：

  • 在我构建或试验时，从数据完整性的角度来看更安全，
  • 对表单→电子表格链接的风险较小，
  • 有助于满足任何流程/数据透明度需求，因为它是原始的，
  • 有助于满足严格的共享和访问管理要求，以及
  • 使报告或分析的格式和功能具有更大的灵活性。

我在您的演示电子表格中添加了“报告”表。第 2 行只有五个公式。它们每个都从两个表单响应表中提取适当的数据，实时更新任何其他响应，并为每个生成的行显示请求的输出。

如果您可以容忍转到“报告”选项卡而不是在“表格 1 响应”中直接工作，我认为这是正确的方法，并且非常适合您的文件。您的项目一切顺利。

下面，我为后代复制了五个公式。如果你认为你最终会使用那个报告选项卡，或者根据你的项目调整它的公式，请这样说，我会详细解释它们——不会比一年过去更糟，然后你必须修复我复杂的公式或编辑它们以满足一些新的项目需求。

A2 - 从两种形式中获取每个不同的名称

=UNIQUE({'Form 1 Response'!A2:A;'Form 2 Response'!A2:A})

B2 - 显示“完成”或缺少哪些文档字母

=ARRAYFORMULA(IFS(
  NOT(LEN(A2:A)),"",
  LEN(C2:C)*LEN(D2:D)*LEN(E2:E),"Complete",
  TRUE,IF(LEN(C2:C),"","Doc A ")&IF(LEN(D2:D),"","Doc B ")&IF(LEN(E2:E),"","Doc C")
))

C2—从任一表格中拉入 Doc A 的任何链接

=ARRAYFORMULA(IF(NOT(LEN($A$2:$A)),"",
  IFERROR(VLOOKUP($A$2:$A,FILTER('Form 1 Response'!$A$2:C,LEN('Form 1 Response'!C$2:C)),3,FALSE))&
  IFERROR(VLOOKUP($A$2:$A,FILTER('Form 2 Response'!$A$2:B,LEN('Form 2 Response'!B$2:B)),2,FALSE))
))

D2—Doc B 链接

=ARRAYFORMULA(IF(NOT(LEN($A$2:$A)),"",
  IFERROR(VLOOKUP($A$2:$A,FILTER('Form 1 Response'!$A$2:D,LEN('Form 1 Response'!D$2:D)),4,FALSE))&
  IFERROR(VLOOKUP($A$2:$A,FILTER('Form 2 Response'!$A$2:C,LEN('Form 2 Response'!C$2:C)),3,FALSE))
))

E2—Doc C 链接

=ARRAYFORMULA(IF(NOT(LEN($A$2:$A)),"",
  IFERROR(VLOOKUP($A$2:$A,FILTER('Form 1 Response'!$A$2:E,LEN('Form 1 Response'!E$2:E)),5,FALSE))&
  IFERROR(VLOOKUP($A$2:$A,FILTER('Form 2 Response'!$A$2:D,LEN('Form 2 Response'!D$2:D)),4,FALSE))
))

推荐阅读