Create n8n Nodes:

  1. Create new workflow and name it something meaningful
  2. Select ‘form submission’ trigger for the first node
    1. Create test fields ‘First Name’, ‘Last Name’,  ‘Email Address’, and ‘Position’.
    2. Test the form to confirm that data can be submitted
    3. Pin the sample data so it can be used in the current workflow
  3. Select ‘Action in App’ for the next node
  4. Search for ‘Google Sheets’ and click on the icon
  5. Click ‘Append or update row in sheet.’
  6. Select ‘Create new credentials’ from the ‘Credential to connect with’ dropdown menu (or add existing connection if one exists)
  7. Select the ‘OAuth2 recommended’ radio button
  8. Make sure to save your workflow frequently

Create OAuth2 strings (credentials to be used in n8n) in Google Cloud Console :

  1. Create credentials in Google Cloud Console (https://cloud.google.com/).  You need a Google account for this – make sure you are logged in to the Google account you want to create OAuth keys on.
  2. Click ‘APIs & Services’
  3. Click ‘OAuth Consent Screen’
  4. Click the ‘Clients’ link on the left navigation menu
  5. Click ‘Create Client +’ in the top menu
  6. Select ‘Web Application’ from the ‘Application Type’ dropdown menu
  7. Give OAuth2 a name (e.g., Connect to Sheets using n8n).
  8. Under ‘Authorized redirect URIs,’ copy and paste the redirect URL shown in n8n’s ‘OAuth Redirect URL’ in the OAuth2 connection string dialog box
    Click the blue ‘Create’ button
  9. Copy the OAuth2 connection strings (Client Id and Client Secret) to a safe place
  10. Click the ‘Audience’ link in the left-hand navigation
  11. Add test user email accounts (the same email account you used to create the OAuth2 connection strings)
  12. Important! Enable Google Drive API from (https://console.cloud.google.com/apis/api/drive.googleapis.com/), otherwise the n8n will not load the Spreadsheet documents in the n8n Google Sheets node when it comes time to choose a Document to Append or Update to.
  13. Important! Enable Google Sheets API from (https://console.cloud.google.com/apis/api/sheets.googleapis.com/), otherwise n8n will not load the Sheets in the n8n Google Sheets node when it comes time to choose a Sheet to update form data to.

Google credentials configuration:

  1. Add the Client ID and Client Secret to the fields in the ‘Connection’ dialog box
  2. Click the ‘Sign In With Google’ button
  3. Select the Google email account you used to create the OAuth2 connection
  4. Click the ‘Continue’ link for the ‘Google hasn’t verified this app’ pop-up window
  5. For the Google dialog window that popup ‘wants additional access to your Google Account, click the ‘Continue’ link
  6. If all went well, you will see a follow-up dialog window that the connection to n8n was successful

Google Sheets and n8n configuration and test:

  1. Create a new Google Sheet (https://docs.google.com/spreadsheets/) on the same Google email account you created the OAuth2 connection.
  2. Add the fields that were created on the form in the first trigger node
  3. Settings should be:
    1. Resource: ‘Sheets Within Documents’
    2. Operation:  ‘Append or Update Row’
    3. Select the Document you created earlier in Google Sheets from the Document dropdown menu. NOTE: Make sure you enable the Google Drive API; you will get an error that the Google Drive API has not been enabled when you try to select a Document from the ‘Documents’ dropdown list.
    4. Select the Sheet that you created.  NOTE: Make sure you enable the Google Sheets API; you will get an error that the Google Sheets API has not been enabled when you try to select a Sheet from the ‘Sheets’ dropdown list.
    5. For ‘Mapping Column Mode’ select ‘Manual.’
    6. Drag and drop the form fields you created in the form node to the Google Sheet fields.
    7. Make sure the ‘Email Address’ field is dragged to the ‘Column to match on’ field.
    8. Click the ‘Execute Step’ orange button to append the pinned submitted form data to the Excel Sheet.
    9. Check the Sheet to confirm the data was added/appended.
    10. Save your workflow (it  may auto-save or it may not)

Create a filter (node 3) to filter out rows in the Google Sheet (node 2):

  1. Search for Filter
  2. Drag and drop ‘Position’ into ‘Value1’.
  3. Is equal 2 should be = to ‘Student’
  4. Test the workflow to confirm it is keeping the position where the student was selected in the form dropdown list.

 

Send filtered form submission (node 3) to Gmail inbox (node 4):

  1. Search for Gmail in the node list and select ‘Send a message.’
  2. For ‘credentials to connect with’, select OAuth2
  3. Use the same OAuth2 keys for the Gmail node
  4. Select which permissions to allow n8n in the Gmail dialog box.
  5. Confirm the connection is made in the n8n Gmail dialog box (showing in the ‘credentials to connect with’ dropdown list).
  6. Resource: ‘Message’, To: ’email account that you can check inbox, Subject: ‘A student’, and Message: ‘this is your student’.
  7. NOTE: Make sure to enable the Gmail API on the Gmail account you created the n8n workflow on (https://console.developers.google.com/apis/api/gmail.googleapis.com), else the Gmail node will not send the filtered message in the workflow
  8. Go to the inbox of the email account you assigned to accept messages and check that the sent message is in the inbox.