How can I track Google Forms responses without requiring manual email submission?

Question

Pretext: I am very new to programming and google api.

I'm currently working on a project where I need to track the sending and submission receiving of each respondent of a Google Form. The challenge is that I don't want to make the respondents manually submit their emails because I already have their email addresses stored in a customer database, along with unique identifiers for each customer.

To give you some context, the surveys are sent out via email, and I want to find a way to alter the Google API to record when each survey is sent out to each email, as well as identify the survey responses as they are submitted.

I've explored various approaches, but I haven't found a straightforward solution yet. I've considered using Google Apps Script or the Google Forms API, but I'm unsure about the best approach to achieve my goal.

If anyone has experience or knowledge in tracking respondent activity in Google Forms without requiring manual email submission, I would greatly appreciate any guidance, suggestions, or insights you can provide. Specifically, I would like to know if there are any methods to modify the Google API to record the email sending and associate it with the survey response.

Thank you in advance for your help!

Sincerely,

A Bootstrapper.

I found a working script off the internet that can export the contents of a gmail folder to a google sheet. I haven't been able to set this up to monitor the sent folder, but since the email will only be sending surveys out, this could record the sending out of surveys.

This does not allow tracking for the completed surveys, however. I understand their may be a way to have a hidden field containing the UID, and that a pre-filled link can be generated based on the email chosen, but I wouldn't know where to start with this method...

Answer 1

As an alternative if your customers don't want to type their email addresses manually, I can provide you with a formula for creating a prefilled URL in Google Sheets, so it already contains their email address (or any other identifier you want).

1. Create your Google Form including the field for the ‘email’ address or any other ID you would like to use. Then, we need to gather the entry id for the question we will be pushing the prefilled answer.
2. In the editor of your Google Form click on 3 dots at the top and select "Get pre-filled link"

3.It will open a view version of your form, where you can fill the parameter you want to customize later, scroll all the way down to "Get link" and copy the link, we will use it for the formula in the next step:

4. The link will have the following structure https://docs.google.com/forms/d/e/1YOUR-FORM-ID/viewform?usp=pp_url&entry.1418014325=email@example.com Copy everything until the = so it will look like this https://docs.google.com/forms/d/e/1YOUR-FORM-ID/viewform?usp=pp_url&entry.1418014325=

5. In a Google sheet where you have your customer's data, we will paste the formula, but making some changes:

Formula =ARRAY_CONSTRAIN(ARRAYFORMULA({"Prefilled form URL"; hyperlink("PASTE-HERE-THE-URL-FROM-PREVIOUS-STEP="&$C$2:$C&"","Custom URL")}),counta($C1:$C),1)

Changes Make sure to update the ranges where the identifier of your choice is located

Once you have modified the formula to match your data it should generate links like this:

I'm adding the file I created where I tested this solution: https://docs.google.com/spreadsheets/d/1uLE-bp6aZsr41JatgQalWfKLlwKHsrK4TTa5esxmwuo/edit?resourcekey#gid=1351399216
hopefully this gets you closer to what you are trying to achieve. If you have any questions, let me know and I'll see what I can do :)