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

Question

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

Answer 1

I don't like altering form results. I've found that doing everything elsewhere:

• is safer from a data-integrity standpoint while I'm building or experimenting,
• poses less risk to the form→spreadsheet link,
• helps with any process/data transparency needs since it's pristine,
• helps with strict sharing and access-management requirements, and
• allows much greater flexibility in a Report or analysis's format and functionality.

I added a "Report" sheet to your demo spreadsheet. There are just five formulas, across row 2. They each pull in the appropriate data from the two Form Responses sheets, live and up-to-date with any additional responses, and show the requested output for each generated row.

If you can tolerate the change of going to a Report tab rather than working right there in Form 1 Responses, I think it's the right approach and is a good fit for your file. All the best with your project.

---

Below, I've copied the five formulas for posterity. If you think you'll end up using that Report tab, or adapting its formulas to your project, please do say so and I'll explain them in detail—nothing worse than a year going by and then you have to repair my convoluted formulas or edit them to meet some new project need.

A2—Grab each distinct name from both forms

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

B2—Show 'Complete' or which Doc letters are missing

=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—Pull in any links for Doc A from either Form

=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 links

=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 links

=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))
))