Google Form Specific Automatic Response

Question

I am aware that I can add a little code to a Google Form's spreadsheet to have it auto respond to a submission. I am curious if there's a way to have a person submit the form and then have it auto respond with specific information, most likely pulled from a separate sheet.

For example, submitter wants to know a password that is specific to them so they fill out the Google Form and the Form auto responds with the submitter's specific password. Is this possible?

Answer 1

TL;DR: Use AppScript, refer to Google's documentation for correct syntax of the SpreadsheetApp class which is what you would use to read a separate spreadsheet.

---

Use AppScripts custom scripts in the Google Sheets. What you could do is have the script fetch information from another sheet with the info to be sent, then send that info. This would make a function that you can use in the forms sheet by using =functionname() in a cell. I think the script would go something like this:

// https://docs.google.com/spreadsheet/d/the_id_is_this_part_of_the_url
function fetch(id, row, col) {
SpreadsheetApp.openByID(id);
SpreadsheetApp.get()
eval("var cell = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange("+col+row+");");
SpreadsheetApp.getCurrentCell(cell);
var output = cell.getValue();
return output;
}
/*
in the code to send the response, do 'var response = fetch(id, row, col);' and 
use the new variable 'response' in the response
*/

I'm 99% sure this code is full of 5ynt4x_3rr0rs_ and bad code-writing practices but that's the gist of it. Google's documentation is a better place of reference though, and that's where I researched this answer from.