0

Is it possible to use a Google Form to replace a user's data in a spreadsheet? Ideally this information would be attached to their email address as they are logged in to Google, thus preventing them from overwriting other rows.

Sheet example:
User-email 1 | Info 1a   | Info 1b
User-email 2 | Info 2a   | Info 2b
User-email 3 | Info 3a   | Info 3b
<blank>      | <blank>   | <blank>

So if User 2 submitted the form with Info A and B, could it replace the existing values such that only Info 2a and 2b are changed and NOT created in a new row? E.g.

Sheet example, with form submission for user 2:
User-email 1 | Info 1a   | Info 1b
User-email 2 | Info 2a*  | Info 2b*
User-email 3 | Info 3a   | Info 3b
<blank>      | <blank>   | <blank>
Improve this question
2
  • Forms alone can only access Google Sheets to push data. However, form-intake sheets should never be the main sheet anyone is working with if you need data presented in any way other than the way the raw data came in. No new columns, formulas or formatting should be added to the form-intake sheet. Rather, a separate sheet should reference the original data to present it the way you want to see it. As such, it is possible for formulas in that separate sheet to simply return only the most recent data submitted by a user—in essence, "replacing" the old data in that separate sheet.
    – Erik Tyler
    Nov 14, 2021 at 15:41
  • See How can a user edit their response in Google Forms, once they have submitted and closed the form.
    – doubleunary
    Nov 14, 2021 at 15:56

1 Answer 1

Reset to default
0

Forms can be configured so that a user can "edit after submit". In this case there is only ever one response from the user and your scenario doesn't apply.

On the other hand, Forms can also be configured to allow users to make multiple submissions (your scenario). In this case, the original "form responses" sheet should NOT be amended. Rather create a second sheet to analyse the Form responses.

This is an example of a user who has submitted two responses.

HT: This answer is based on Delete duplicates keep last record in Google "Docs Editors Help".

Option#1

=ARRAYFORMULA(Sort(VLookup(unique(filter(Responses!B2:B,Responses!B2:B<>"")),Sort({Responses!B2:B,Responses!A2:D},Responses!A2:A,0),{2,3,4,5},0)))

OR

=ARRAYFORMULA(Sort(iferror(VLookup(Unique(Responses!B2:B),Sort({Responses!B2:B5,Responses!A2:D5},Responses!A2:A5,0),{2,3,4,5},0))))

Explanation

Unique(Responses!B2:B) will return one empty row in its results. This might not normally be a problem except that the unique values are used as search terms in VLOOKUP. So an #N/A error will be generated when VLOOKUP tried to search for the value ''.

  • The first formula does NOT include an empty row in its results - so no risk of error.
  • The second formula DOES include an empty row in its results and this is masked by using IFERROR.

Option#2

=sortn(sort(Responses!A2:D,1,0),9^9,2,sort(Responses!B2:B,Responses!A2:A,0),0)

Responses

Responses

Most Recent

most recent

Improve this answer
7
  • Using Option #1 seemed to work for the most part (good enough, really), but I ended up with a row of N/As in addition to the returned rows. Hovering tells me that the cells Did not find value " in the VLOOKUP evaluation. Would you happen to be able to tell me my issue might be in the formula?
    – myacorn
    Nov 25, 2021 at 11:46
  • Mea culpa. Answer updated. I originally specified a finite range for unique() but you probably use unique(Responses!B2:B), which is fair enough as it adds flexibility. FYI: unique(Responses!B2:B) will return one empty row in its result and this was the value " that 'VLOOKUP' was trying to find - hence the error message. The modified formula (based on FILTER and excluding blank values) will NOT return an empty row - so there won't be error. Or you can wrap the original formula in IFERROR to mask the error; only problem is that it would also mask a genuine error.
    – Tedinoz
    Nov 25, 2021 at 21:49
  • This is interesting... I'm noticing that it is now returning the original form response rather than the most recent one. I'm not sure if I've misused the formula or something — I don't quite understand how it works, so I'm not sure what went wrong. Adapted to my sheet and the relevant columns, the formula looks like this: =ARRAYFORMULA(Sort(VLookup(unique(filter('Responses'!B3:B,'Responses'!B3:B<>"")),Sort({'Responses'!B3:B, 'Responses!A3:G},'Responses'!A3:A,0),{2,3,4,5,6,7,8},0)))
    – myacorn
    Nov 30, 2021 at 2:04
  • Isn’t your sheet that same as the layout in the question? There’s only one header row, but you formula range starts at row3. Aren’t you missing whatever was on row2?
    – Tedinoz
    Nov 30, 2021 at 4:22
  • I adapted it; having two rows used for titles and disclaimers required this. Thus the data starts on the row3. Regardless, it doesn't seem to me that this would cause the formula to react any differently…
    – myacorn
    Nov 30, 2021 at 5:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.