Verify each Google Forms response address

1. 1
   In PricklyMails

   Create an API key in PricklyMails

   1. In the dashboard, open API Keys and click New key.
   2. Under Environment, pick Test first: the key starts with pm_test_, answers from canned results, bills nothing and contacts no mail server.
   3. Copy the key as soon as it shows: it is shown only once.

   Tip

   The Free plan holds one key: once everything works, revoke the test key, then create the one under Live.

2. 2
   In Google Forms

   Add the script to your Google Forms responses

   The script lives in the spreadsheet that holds the responses, and keeps the key in its script properties, out of the code.

   1. In the form, click Responses, then More then Select destination for responses, and create a spreadsheet or pick one.
   2. In the spreadsheet, add a column titled PricklyMails after the last one.
   3. Click Extensions then Apps Script.
   4. Replace the code with the code below, and set EMAIL_COLUMN to the header of the column that holds the address.
   5. Open Project Settings, click Add script property, then enter PRICKLYMAILS_KEY as the property and your test key as the value.
   6. Click Save script properties.

   The script asks PricklyMails to answer within 12 seconds (Prefer: wait), then writes the recommendation in the PricklyMails column of the response's row.

   Code.gs

   const VERIFY_URL = 'https://api.pricklymails.com/api/verify'
   // The headers of the response sheet's address column and of the column the
   // script fills.
   const EMAIL_COLUMN = 'Email'
   const RESULT_COLUMN = 'PricklyMails'
   
   function installTrigger() {
       ScriptApp.newTrigger('verifyResponse')
           .forSpreadsheet(SpreadsheetApp.getActive())
           .onFormSubmit()
           .create()
   }
   
   function verifyResponse(event) {
       const email = event.namedValues[EMAIL_COLUMN]?.[0]
       if (!email) {
           return
       }
       const sheet = event.range.getSheet()
       const headers = sheet
           .getRange(1, 1, 1, sheet.getLastColumn())
           .getValues()[0]
       const column = headers.indexOf(RESULT_COLUMN) + 1
       if (0 === column) {
           return
       }
       sheet
           .getRange(event.range.getRow(), column)
           .setValue(recommendationFor(email))
   }
   
   // Without an answer in time, the address is one to look at, like any review.
   function recommendationFor(email) {
       const key =
           PropertiesService.getScriptProperties().getProperty('PRICKLYMAILS_KEY')
       const response = UrlFetchApp.fetch(VERIFY_URL, {
           method: 'post',
           contentType: 'application/json',
           headers: { Authorization: `Bearer ${key}`, Prefer: 'wait=12' },
           payload: JSON.stringify({ email }),
           muteHttpExceptions: true,
       })
       if (200 !== response.getResponseCode()) {
           return 'review'
       }
       const { verification } = JSON.parse(response.getContentText())
       return verification?.recommendation ?? 'review'
   }
   

   How the API answers in one call

   SourceGoogle Forms's official help center

   • Choose where to save form responses (opens in a new tab)support.google.com
   • Container-bound scripts (opens in a new tab)developers.google.com
   • Properties Service (opens in a new tab)developers.google.com
   • Event objects (opens in a new tab)developers.google.com
   • Class UrlFetchApp (opens in a new tab)developers.google.com
3. 3
   In Google Forms

   Turn on the check for each Google Forms response

   An installable trigger runs the script after each response. The code creates it, so there is no menu to look for.

   1. In the script editor, pick installTrigger in the list of functions, then click Run.
   2. Allow the access Google asks for: the script reads the sheet and calls a service outside Google.

   Warning

   Run installTrigger only once: each run adds another trigger, and each trigger verifies the response again.

   SourceGoogle Forms's official help center

   • Installable triggers (opens in a new tab)developers.google.com
   • Class SpreadsheetTriggerBuilder (opens in a new tab)developers.google.com
4. 4
   In Google Forms

   Try it with the test key

   1. Submit a response with an address that starts with invalid, such as invalid@example.com: the test key answers remove, and the row shows it.
   2. Submit another one with valid@example.com: the row shows send.
   3. Where you stored the key, replace it with a key created under Live: from then on, each response is verified for real and billed like any verification.

What the column says

Frequently asked questions