CODEWITHSUNDEEP
google-sheetsspreadsheet
General Douglas MacArthur
General Douglas MacArthur

Reputation: 37

Update Google sheet from forms

I have data of client lookup information in a google sheet. This is tied to a Google Data Studio Viz to allow the team working with the data to see only the relevant information to each team member easily.

The only way this data is updated is when the team member asks questions to the client "Has you address changed, is the IP address the same, etc. etc"

I created a Google Form that has a simple series of questions allowing for the team member to record the changes. This is stored in the same workbook in another sheet. I want a formula/function that checks the unique ID (SiteID) for a match and then updates the cells where there is new data.

A sample is below:

Here is the Forms updated document

This is the Form sheet

This is the source document I need updated

Here is the source document

Another complication is at some times there is a whole new site added. So a SiteID that is not present in the source, I would need these to be appended to the source.

Upvotes: 0

Views: 93

Answers (1)

Nabnub
Nabnub

Reputation: 1055

Try the following in C2:

=ArrayFormula(if(A2:A="",,iferror(VLOOKUP(A2:A,sort({
   Sheet1!B2:D},
   row(Sheet1!B2:B),false),{2,3},false))))

Source document (Sheet2 here)

Column A is formatted as Plain Text

enter image description here

Form submissions (Sheet1 here)

enter image description here

Upvotes: 1

Related Questions