I have a Google Forms where one of the fields is a dropdown to select a department. When choosing the department, I want a column for email to appear in my responses, but I want it to be already filled in without having to select it. For example, when selecting 'Human Resources', I want the email answer to be set to [email protected]. What is the best way to do this, use Apps Script or link to another spreadsheet containing all the emails?
In my opinion, it is best practice to leave the Form Responses sheet as is. (Although you can hide it if you want.) Then on another sheet, do whatever calculations you wish. The way I would set it up is to have a Form Responses sheet, a Department Emails sheet, and a Form Responses with Modifications sheet.
The Department Email sheet would have department list in column A and the corresponding emails in column B.
If department names are entered in column B of your Form Responses sheet, I would use =ARRAYFORMULA('Form Responses'!B2:B) in B2 of Form Responses with Modification Sheet. In C2 I would use something like this to find the emails corresponding to the department name in the respective form response:
=IFERROR(ARRAYFORMULA(IF(B2:B="",,XLOOKUP(B2:B,'Department Emails'!A2:A,'Department Emails'!B2:B))),"No assigned email")
All other needed columns from Form Responses can be displayed using =ARRAYFORMULA('Form Responses'!x2:x) with x being the required column letter.
Hopefully, that all made sense. :)