Automate the “Contact Us” form for a website with Google Workspace

zenphi Use Cases

This article was written and kindly contributed by Scott Donald – Google Workspace Developer, Google Developer Expert, and Google Champion Innovator.

You have clients reaching out to you from your website contact page. If you don’t have any custom software to handle this you are often stuck with sorting through a spreadsheet of responses and manually forwarding requests to departments in the hope that they will get back to the enquiry on time.

It’s probably also a good idea to track a few things like:

– The enquiry form content submission.

– Who has been assigned to the enquiry.

– When the enquiry was assigned.

– When the first response was made.

– Total response time from enquiry form submission to first response.

– Continued correspondence record.

What We’re Building

In this beginner-friendly tutorial, we will walk through building your own Contact Us form for a website with Google Workspace and zenphi.

Zenphi is a no-code business process automation platform built explicitly for Google Workspace users. It enables you and everyone in your company to automate their processes without writing a single line of code. If you don’t have a zenphi account yet – create one for free.

The Contact Us Process Flow

zenphi generates a custom flow chart as you build the Flow for your project, as you can see in the image above. It is an elegant way of viewing your automation process that allows you to zoom in and out of the flow to explore its elements or what we call, Actions.

There is an irony here though. One of the best ways for us to plan out an automation process is to create a flow chart.

For the time being, let’s scratch out the basics of what we will build in some basic nested list items instead:

1. Form trigger: Here we create our Contact Us form for your users to make their request. When the user submits the form, the trigger is sent.

2. Check if the user email is spam: We don’t want to waste our team’s time. If we have logged the form submitter as spam in the past, we will ignore the user and conclude the process.

3. Generate a unique UI: We can then assign this ID to the form submission as a ticket number.

4. Run 3 parallel processes:
a. Send email to form sender: We will notify the submitter of the form that we have received their submission and provide them with their ticket number.
b. Run consultant allocation process: We need to do a number of things here:
i. Lookup our Contact Us sheet to find the team leader assigned to the user’s selected category.
ii. Get a list of team members for the selected category.
iii. Send an email request to the team leader to:

Assign a team member to the ticket. OR
Mark as spam

iv. Record the submission: Record the user’s submission along with their ticket number.
c. Record the assignment and the time it took to assign the team member.

What We Will Learn

We will touch on:

– Creating a zenphi Form Submission trigger.

– Generating a unique id action.

– Logging history of our Flow.

– If condition control flows.

– Run parallel processes.

– Create request events for your team to respond to.

– Calculate the difference between two date-times.

– Send emails through Gmail.

– Google Sheets:

– Lookup a selected row.

– Lookup selected rows.

– Add a new row.

– Test your flow.

– Publish your flow.

Getting Hands On

Hands-on approaches are always a better way for you to learn a skill quickly, plus they are much more enjoyable than reading a ‘How-to’. With this in mind, jump to the next section and click the link to the starter Google Sheet and make a copy for yourself and then log into your zenphi account.

If you don’t have an account yet, you can sign up to zenphi here for free, plus get a bonus 14 day free trial of the Pro plan.

Step 1: Getting Started

The Starter Google Sheet

Make a Copy of the Sheet

Make a copy of the starter Google Sheet for this project by selecting the link below:

A window will open. Select Make a copy.

Set up the Google Sheet for testing

In the Notes Google Sheet tab enter the email address that you want to receive your test correspondence. This will update the cells in the Category Head and Teams sheet tabs.

The Contact Us Sheet

It is a good idea to take a quick look at the starter sheet because it gives you a good idea about how you should prepare your data for a zenphi flow action to receive it.

While header rows are optional, they do come in handy when you are trying to work out which columns you want to select when configuring your data in your flow editor.

Form Submissions

This will receive the form submissions if the submission is not marked as spam. Note the User ID or ticket number creating a unique identifier for the user’s enquiry.

Spam

This sheet stores the email of any form submission that is considered spam by the assigned team leader.

It will be referenced towards the beginning of the flow. If a form submission contains an email on the list then the workflow will be skipped.

Contact Assignment

This sheet tab is updated when the Team Leader responsible for a category selects a team member to correspond with the form submitter. We store the ticket number the department and the team member the ticket was assigned to. We also store the date time stamp that the ticket was assigned and then the amount of time it took the team leader to assign the ticket from when the form was submitted.

Category Head

This sheet tab contains a list of all categories and the team leader responsible for them. Note that I have replaced the Head Email column with my email column with my email to receive all messages for testing and not bother my team.

This sheet will be referenced when we need to look up the team leader by the category in order to send them a request to assign a team member.

Teams

The Teams sheet tab contains a list of all available team members by team or group lead by a team leader.

Again, we have temporarily swapped out the User emails for our test email account while we are building and testing our Flow.

Create a Space

Log into your zenphi account.

Create a new space or use an existing one.

Spaces allow you to organise your Flows, tables and even any AI models that you create (not in this tutorial) and even generate informational dashboards and details pages. Finally, you can check on the level of permissions you have used to request certain actions in your flows within your Space.

For us, we will create a new space called, ‘Customer Care’. To do this:

Select the Create a new space icon.
Select the Empty space icon’s Create button.
Under name, write, Custom Care.
Under description write, Workflows Storage and Automation for the Custom Care team.

The Overview screen of your Space allows you to see what flows, tables and AI models you have. Being a new space, these will be blank.

Create a Flow

Select the Create flow plus button to create a new flow.

You’ll be navigated to the flow selection menu. Here you can choose from what will trigger your flow or start from a complete pre-built template.

For our project, we are going to select the zenphi form submission.

This will then navigate us to the flow designer.

The Flow Designer

Let’s rename this flow, Contact Us. In the top right corner of the flow designer double-click the title to edit and rename.

The main part of the flow designer displays a flow chart of the flow that you are creating. The first item will always be the trigger that starts the flow.

For us, this is the zenphi form submission trigger. We will edit this in a minute.

On the right-side panel, you can see a full list of all the actions that you can apply to your flow. Actions can be anything from sending emails, accessing and updating a Google Sheet to looping through data, running processes in parallel, and even accessing the power of AI.

Back when we discussed what we will learn in this tutorial, we were mostly listing all the actions we will use in our flow.

You can add a flow by either using the search bar or browsing through the list of actions. Once you have decided on an action, left-click and drag the action across to the appropriate plus symbol in the flow diagram. From there, you can configure the action (More on this later).

While each action has a handy short explanation of what the flow does, you can always select from either the help or documentation buttons (bottom-right sidebar) to search for more information.

Check out the video below for more details on the flow designer.

https://www.youtube.com/watch?v=3Eo5mDpb3bw

Step 2: Build the zenphi form

When planning out our Contact Us form we needed to decide if we use a Google Form or zenphi Form. You’re probably already quite familiar with Google Forms already, so let’s introduce you to the latter.

Our aim is to provide a consumer-facing contact form for users to select from a list of categories and then enter in a subject followed by some details of their request.

Just like the Google equivalent, we can embed a zenphi Form into our website for users to contact us. In the example below I have embedded our sample form into a Google Site connected to my domain.

Configure the zenphi form

In the flow click on the form trigger. A right-side bar will appear with a configuration menu.
Click the Form builder icon to build the form.

The zenphi form builder

The form builder probably looks quite familiar. Much of the functionality of the Google Form remains with form builder. However, a zenphi form does have a few bonus features designed to run with my effortlessly with our flow.

While you can customise the form theme and add custom header images, we’ll skip this bit and head to building the form data. You can learn more about building a form here.

Add Title Information

Enter our title and header details in the form’s fields:

Title:	Contact Us
Description:	Need to Get in Touch?

Add Short Text Fields.

Click the plus button on the right-side mini toolbar to add a field.

By default, this should be set to Short text. In the main input field enter “Name”.

You can select the copy icon next to the red delete icon or use the plus icon again to create another Short text field.

This time, change the name of the short text field to ‘Email’.

Text Response Validation

We can ensure that the text response is in an email format by selecting the 3 vertical ellipses in the bottom-right of the field and selecting response validation.

A validation section will appear with 3 to 4 input fields. From left to right complete the following:

In the first dropdown, select Text.
In the second dropdown, select Email.
In the optional Custom Error text write, “Must be an email”.

Add Drop-down Selection Field

Create a new field using the plus icon. This time around select the field type Drop-down option from the top-right.

Rename the field title to “Categories”.

Now in the fields add the following items.

Flow Development Support
Billing and Accounts
Bug Reports, Troubleshooting
Arrange Training for your Team

Add a Subject Short text and Body paragraph

Now we need to get the information from the person contacting us.

We’ll first build another Short text field called “Subject”. Then build another field, this time change the field option to Paragraph and call it “Body”.

These are fairly common terms used in emails and other correspondence, so the user should be able to quickly determine what is expected of them here. However we should also give them a few context clues for what to write to help get them started.

To add a description, click the vertical ellipsis in the bottom right corner and select Show description.

Add the descriptions below to the Description field.

Header	Description	Field type
Subject	Briefly describe the topic of your request	Short text
Body	Any details will help us respond to you more completely.	Paragraph

Make sure that all the fields are set to Required with the blue toggle button slid across to the right.

Set the Form to Accept Multiple Responses

In the top menu of the form builder select Settings. Then toggle Submit another response so that users can send the form if they have another enquiry.

Next, save the form by clicking the button in the top-right corner of the page. You will be navigated back to the flow designer.

Close the Form trigger configuration sidebar and then click the save button in the top bar next to the title of your flow.

Step 3: Check for Spam

Instead of wasting our team’s time with spam emails, we can run a check to see if the form submission has been flagged from our Spam list in the past.

We’ve conveniently added a spam list to our Contact Us – Management (zenphi) Google Sheet under the Spam folder. We can now check this sheet tab for any occurrence of the response email of the form. If the email is on our spam sheet, then we are going to skip our workflow and conclude our flow. Otherwise, we can continue to process the form.

Google Sheets Action – Lookup Row

In our flow Action sidebar scroll down until you find a Google Sheets list. Expand the list and scroll down until you find Lookup Row. Alternatively, use the search bar to find Lookup Row.

Next, click on the Lookup Row section and drag it across to the green plus button between the form trigger and the stop symbol.

Click the configuration cog icon to the right of the Lookup Row action you added. A sidebar will appear to the right.

Add a connection

We need to access our Contact Us Google Sheet via our zenphi account. Google will require your permission to access (or authorise) your Google Sheet files. In zenphi we call these Connections.

In the first input field in the Lookup Rows action configuration sidebar we have a choice of adding an existing authorisation or creating a new one. This authorisation can be reused for other processes in your flow or even for other flows in your selected space.

You can remove authorisation at any time from the left-side bar cog > Connections.

You can select an existing connection with the dropdown menu or create a new one by selecting the plus button to the right of the input field. A pop-up window will appear. Rename the name to “Contact Us Google Sheets connection” and select Authorise.

Another popup box will appear. Select your account and then select Continue.

Select the Google Sheets File and Sheet name.

In the File input field select the Google Drive logo in to the right and then navigate through the file picker to find your copy of the Contact Us – Management (zenphi) – Starter Sheet.

You can now choose from your desired sheet name. Select the Sheet icon in the far right of the Sheet Name input field > select Spam > Done.

Search the column for the selected value

We need to search column B of our Spam sheet tab for any email that matches the input field.

Ensure that the Has Header toggle is selected.
Then load the columns to make it easier for us to find the column we need Load Columns.
Select the Email column.

Add a Search Token

You’ve probably seen this little icon next to input fields all over the place.

This icon is called the Token Picker. This allows you to put any values or variables that you have assigned or collected from your other inputs like forms, tables, Google Sheets etc.

Click the token picker. A dialogue box will appear.
Select the Form Data from the Trigger section.
Select the plus (+) button next to the Email entry. You will see it appear in the Search Value field.
Click the (x) close button.

The result of the Google Sheet Action

We can skip the rest of the options. Scroll down to the bottom of the configuration sidebar and have a quick look at the Retrieve Column items. These items will be saved by zenphi and can be used later as tokens in your Token Picker in future actions.

Go ahead and close the configuration menu and don’t forget to save the flow.

If the Form Email is Spam

There are a number of actions in a zenphi flow that control how the flow operates. For example, we can:

Loop through items to carry out actions on each of them.
Run parallel actions.
Set new variables.
Log tokens and events.
Switch between actions flows depending on the outcome of events.

Add the If Condition Action

We can even determine a flow path depending on if the form email has been registered as spam or not. This can be achieved with the If Condition Control Flow action.

In the action sidebar on the left, scroll down to the Control Flow section and select If Condition and drag it to the plus symbol below our Lookup row action.
Then open the configuration sidebar.
Next select the condition configuration.

Create an If Condition

A Condition dialogue will appear.

When the Google Sheets – Lookup Row actions looks up an item it will return a list of the selected data for the found row. However, if the search item does not find a matching row, then the row will return a null value.

We can take advantage of this knowledge in our If condition. Let’s set it up:

Change the first field to “Except When”.
In the next field select the token selector.
Select Lookup Row > Row (+).
In the last field select Is Null.
Click Ok to close the Condition dialogue.

Close the If condition configuration sidebar and save the flow.

Rename the Condition

You should now see that our flow has split into two paths:

True: If the form email was found in the Spam sheet. The Row token’s child contains key-value pairs.
False: If the form email was not found in the Spam sheet or the Row token returns null.

We will use the If condition action a number of times in this flow, so it is probably a good idea to rename it.

To do this:

Double click on the text on the If condition action item.
Rename the action item to “If is Spam”.
Click off the text range and save the flow.

Step 4: Running your First Test

It’s probably a good time in this stage of our flow development to run a test on our if condition to see if it works.

Logging Data, Responses and Tokens

You might have noticed the Test Run button in your flow’s top menu bar. We can run tests on our flow with this button. But before we do, we should log some data in each of the If conditions so we know which path our form input took.

Under Control flow in the action menu, select Log to History and drag one across to the plus symbol below the False statement and another below the True statement. Then open the configuration sidebar.

The configuration of our log will return whatever we want to record based on the input we make. Let’s record the email from the form and state the condition outcome.

Condition	Log Text	Log name
If spam = False	Email: [ replace with form email token]Info: Form is NOT spam.	Log: Form is NOT spam.
If spam = True	Email: [ replace with form email token]Info: Form IS spam.	Log: Form IS spam.

Replace the section in square brackets with the email token:

Copy and paste in the relevant text from the table above under Log Text.
Deleted the square brackets and content.
Select the token Icon.
Select Actions > Lookup Row > Row > Email (+).
Close the Insert Tokens dialogue.
Close the configuration.
Double click on the Log action and rename it with the relevant Log name from the table above.
Save the flow.

Naming The Flow Run

When we run a test of our flow, the test will be named based on a unique identifier automatically. While this is helpful to keep track of our tests for the software, it is not all that human readable.

We can name each test by adding a Set Run Name action from the Control Flow action list.

Go ahead and drag the action to the plus icon just below the form trigger at the start of the flow and then open the configuration sidebar.

Let’s identify our test by the user’s email and the time the form was sent.

Click on the token icon in the Name input field.
Select Form Data > Email (+).
Add a colon (:) to the field.
Back in Insert tokens select Initiator > Submit Time.
Close the Insert tokens dialog box.
Close the configuration sidebar.
Save the flow.

Now when we run our test the flow might look like this:

badEmail@demo.zenphi.com: 2022-11-07T02:34:06.7656366Z

Running the Test

Update your Spam sheet tab data

First, let’s make sure we have an email in our Spam sheet. You can use the one provided in the test sheet or create your own perhaps from an alias or testing account.

Add Two sample form inputs

Before we can run our tests, we need to add some form inputs.

To add input to a zenphi form, we will need to publish the flow. Click Publish from the flow top menu bar.

Select the zenphi form trigger. The configuration sidebar will open.
In the top menu of the configuration bar select Invocation.
Click the Open in a new tab icon in blue to open the form.

Enter the following two form inputs and hit Submit and close the form. Then open the form again from the configuration sidebar to repeat:

Title:	Spam Email	Good Email
Name:	Scott Badactor	[Your name]
Email:	badEmail@demo.zenphi.com	[Your test email]
Selection:	Billing and Accounts	Arrange Training for your Team
Subject:	This is a hold up!	We want to learn it all.
Body:	Hand it over easy-like!	Hi, I would like to arrange a live training session for my team in the Education sector.

Understanding the Results

The Published Flow Actually Ran

Because we had to publish our Form Trigger flow to add in some sample inputs for testing, when we entered each test the flow actually ran.

We can see the results of each run by heading back to the Contact Us dashboard with the back button in the top right of the flow designer header bar.

The Flow Dashboard

The Flow Dashboard (needs link) contains all the runs of your selected flow. In the example below, you can see that I have made 3 runs. The first one failed, and the other two were successful.

These successful runs were the ones we created when generating our test form inputs.

Right now, our flow is still published. Let’s take it out of its published status for now, just to be safe. You can do this by clicking the little cloud icon in red with a strike through it in the top right of the flow dashboard header menu (1).

You can also learn more about each run, by selecting the desired run (2).

You can see that the name of the first run is a unique numerical identifier, but the others are identified by the email and date time stamp. We created these emails earlier when we added the Flow Run Action.

The Flow Run Dashboard

The Flow Run Dashboard contains all the action events that occurred during the selected run.

Let’s take a look at the badEmail@demo.zenphi.com run.

On the left side of the dashboard, you can see each of the steps that were run for the selected flow.
1. If there is an error along the way then the corresponding step will be marked with a red ‘!’.
2. You can also see how long it took for the selected flow to run broken down into hours:minutes:seconds:milliseconds.
On the right side of the dashboard, you can see all the logs that were generated in the flow including the flow run name.
1. You can expand each item to see the logged results.
2. Note the Email and Info in the second logged item. If there was no email found in the Spam sheet tab, then the email will be blank.
3. Any error results will also be displayed along with the error Object message in this section.

Use the back arrow in the top left corner of the dashboard to head back to the flow dashboard.

Run the Test Inside the editor

Back in the flow dashboard, select the Edit flow icon in the top right corner of the header menu to get back to the flow designer.

Let’s run a test.

Click the Test Run button from the top flow menu.

As each step is actioned, you can see it appear in the Test Session. Just like the Flow Run Dashboard, you can see that the step has a success or failure indicator along with the amount of time taken to run each step of the test.

If you click on the log menu item, you can see a list of all the logs that you have added to your flow.

Step 5: Generate a Submission ID

Now that we know that the form email is not spam we can assign a unique identifier to form submission. This will make it easy for us to track the submission and team assignment process.

Under the log of the FALSE condition of the “If is spam” branch, we can use zenphi’s custom “Generate GUID” (needs link) to create a unique 32-digit alphanumeric identifier. “Generate GUID” creates a globally unique identifier that will be different from any other identifier across your flow.

You can find “Generate GUID” in the Flow Actions menu under “Operations” or use the search bar (1). Go ahead and drag the flow to below the “Log” action in the FALSE condition of the “If is spam”.

Click on the cog icon to open the configuration menu (2).

From the dropdown menu, select “Without Hyphens” (3). Then close the configuration menu.

Click save to save the flow.

Unique identifiers will be generated like the samples below for each form submission that is not spam:

2ba30d7ce02d45199b9fd1980e03d758

3bd9e2eab3eb46f585bd0e7e28e7f41f

8a4953885e144fff9b033079d2757d55

Step 6: Run 3 Parallel Processes

There are three steps to complete next:

Allocate a consultant to complete the form request.
Store a record of the request in our Contact Us Management Google Sheet.
Send an Email to the form submitter with a summary of their message and acknowledgment of receipt.

One of the incredible things about zenphi’s workflow is that we don’t have to do each of these tasks one after the other. We can create parallel processes to handle each one of the tasks simultaneously.

To add the 3 parallel processes we can use the “Parallel” action under the Control Flow section (1).

Click and drag this action to below the “Generate GUID” action (2).

Click the cog to open the configuration panel (3).

The Parallel action will start off with two child actions. Here we will update the actions (Indicated as a ‘Branch’) to make these flows more understandable for our future selves (4).

Click the “Add Branch” button to add a third branch. Next, add the following titles for each branch:

Send Email to form Submitter.
Run Consult Allocation Process.
Update Google Sheet (Contact Us – Management)

Close the configuration panel. You will see that all branches contain the titles we have given them.

Click ‘Save’ to save the flow.

Step 7: Send an email to the Form Submitter

If you have ever sent a form request or requested an issue ticket for something you generally receive a confirmation email indicating your ticket number and often get a summary of what you submitted on the form. We are going to do the same here.

In the action sidebar, scroll down to the Gmail section and select ‘Send Email (Gmail)’ action (1). Next, open the configuration menu (2).

Add a connection

Just like when we needed to add a connection to give authorization to access the “Contact Us” Google Sheet, we will need to authorise the flow to access Gmail to send an email to the user.

If you already have a gmail connection established in your current Space select the connection from the dropdown menu. Otherwise, select the plus button in the input field (1).

For this tutorial, leave everything else as it is and select Authorise.

Another dialogue box will appear. Follow the steps like before completing the authorization process:

Add recipients

While you can add recipient emails manually in this section, in our flow we don’t know who the recipient is.

Fortunately, we can grab the recipient’s email from our stored list of tokens retrieved from our zenphi form:

Select the “Insert Token” icon in the “To” input field.
Expand the “Trigger(Form Trigger)” section in the dialogue box that will appear.
Expand the “Form Data” Section.
Add the Email token using the plus icon.

We will skip the other inputs for the time being and head to the “Subject” line.

Subject

In the “Subject” input field enter the following:

Body

We are going to write a response message to the form sender. While you can write the message from the sidebar, it is easier to see what you are writing by expanding this section.

In the bottom options menu of the body input section click the ‘Expand’ icon.

We will fill out the field as follows:

Using what you know now about inserting tokens and see if you can enter all the token information on your own. Then come back to the tutorial and read through the sections below to see if you have added everything correctly.

Add the recipients name

Add:

Dear {{name token}}

Note that the double curly braces represent the token field.

To access the user’s name:

Select the ‘Token’ insert icon.
Expand “Trigger (Form Trigger)”.
Expand “Form Data”.
Add the name.

Don’t forget to add a comma after the name token is added.

Add the confirmation text

Copy and paste in the following text:

Thank you for contacting us. Your request has been sent to the relevant team and we will be in touch with you soon.

Add the ticket number

Next, we should provide the ticket number. To make things easier on ourselves, let’s use the unique user ID that we generated previously.

This will allow the user to reference their ticket or current correspondence with your assigned team member.

It will also make it easier for team members to search their emails by the ticket number if the form sender replies to the email.

Enter the following text:

Your ticket number is {{Unique ID}}.

Delete the double curly braces and everything in it and leave your text cursor there.

Again, click the token insert icon (1).

This time around expand the “Generate GUID” section (2) and add the GUID (3).

Include Selected Form Data

Finally, we want to provide the form sender with a summary of their form inputs and then sign off the email.

Copy and paste the following text:

Please find a copy of your form information below: Category: {{Form Data – Category}} Subject:{{Form Data – Subject}} Body:{{Form Data – Body}} Respectfully, The zenphi team

For each form data item select the token input icon and insert the select item from the “Trigger (Form Trigger)” > “Form Data” section.

Minimise the body input field and close the email configuration side panel.

Save the flow.

Step 8: Update Sheet with Form Response

We want to store a copy of each response that comes in. We will store it in our “Contact Us – Management (zenphi) – STARTER” in the “Form Submissions” sheet tab.

We can use the Add Row action from the Google Sheets section from the left-sidebar. Drag the action into the third branch of the Parallel action section we created earlier.

Select the configuration cog icon. A right-side configuration panel will appear.

For “Connection”, use the existing connection you made when you checked for spam.

Under “File” select the Google Drive Icon (1). A Dialog box will appear. Navigate to your own copy of the “Contact Us – Management (zenphi) – STARTER” Google Sheet. Click on it (2) and click “Select” (3).

From the Sheet Name input field, select the “select a sheet” icon (1).

Use the dropdown menu to select “Form Submission” (2).

Click “Done” (3).

Update the column references

Next, we need to reference the appropriate data to insert into the row.

Select “Has Header”.
Click “Load Columns” to load the column list of the sheet.
In the first value input field click the “Insert Token” Icon.
User ID [A]: Actions > Generate GUID > GUID.
DTS [B]: Trigger (Form Trigger) > Submit Time. This is an automatically generated time that the form was submitted.
Email [C]: Trigger (Form Trigger) > Form Data > Email.
Name [D]: Trigger (Form Trigger) > Form Data > Name.
Category[E]: Trigger (Form Trigger) > Form Data > Categories. The category the user selected.
Subject [F]: Trigger (Form Trigger) > Form Data > Subject.
Body [C]: Trigger (Form Trigger) > Form Data > Body.

Close the configuration panel and save the flow.

Step 9: Run consultant allocation process

In this, our second, parallel process we need complete a number of tasks:

Look up the details of the consultant assignment manager for the form sender’s selected category.
Get a list of team members assigned to the selected category.
Send an email request to the category manager to assign a consultant or mark the email as spam.
If the email is not spam:
1. Get the time taken to respond to the form submission.
2. Update the Contact Us – Management Google Sheet.
If the emails is spam:
1. Update the Spam section of the “Contact Us – Management” Google Sheet.

Lookup the category manager

We have stored a list of all of our category managers in the “Contact Us – Management” Google Sheet. Take a look in your sample sheet now.

We need to check who the form submitters selected category manager is so that we can send them an email asking them to assign a consultant to respond to the submitter.

Under the Google Sheets action list select the “Lookup Row” action and drag it into the “Run Consult Allocation Process” branch of your Parallel section (1).

Doubleclick the name and rename it to:

Lookup category manager

This way we better know what this step is.

Add the primary data

Click on the configuration icon and.

There should already be an authorised connection to the Contact Us sheet. Select this connection.

Under “file,”

Select the Google Drive icon. A file picker dialogue will appear.
Navigate to your copy of the “Contact Us: Management (zenphi)” Google Sheet.
Click “Select”.

Under “Sheet Name” Either type in “Category Head” in the input field or:

Select the Sheet tab name list.
Use the dropdown menu to select “Category Head”.
Select “Done”.

Configure the lookup

We need to match the form’s category selection to the category in column A of our “Category Head” sheet tab in our “Contact Us” form.

Toggle “Has Header” to true.
Select the “Load Columns” button. This will load a list of all header labels in the selected sheet tab.
Select “Category[A]” from the list of headers.
In the “Search Value” input field select the token insert icon.
Go to “Trigger (Form Trigger)” > “Form Data” and add “Categories”.

Leave the rest of the configuration as is and scroll down to…

Retrieve Column

This section should come prefilled with all the available columns in the sheet tab. If there is an item you don’t want, then you can remove it from the list using the trash icon.

For our project, we want to retrieve all of these values. There is no need to make a change here.

Close the configuration panel and save the flow.

The output of the retrieved row will be in an object format that you can retrieve as a token.

Note that “Category” differs from “Department”. Some departments may share multiple categories. We will be using the Department identifier to get a list of relevant consultants next.

Get team members for the selected category

Just like retrieving the category manager data, we need to retrieve all of the team members available to consult for the selected category.

We store our list of consultants in the “Teams” tab of the “Contact Us – Management (zenphi)” Sheet tab.

Here we will extract our list of consultants by their Group Name (Also known as “Department” in the “Category Head” tab).

Look up the team member rows

Under the Google Sheets Actions section select “Lookup Rows”. Note the plural here because we will be selecting multiple rows.

Drag the “Lookup Rows” action below the previous lookup action. Rename to “Lookup Consultants”.

Add the primary data

Repeat the steps from the previous section to add a connection and select the “Contact Us – Management (zenphi)” Sheet from your Google Drive.

This time around select “Teams” for your Sheet name.

Configure the lookup

Ensure “Has header” is selected and then load the columns.

Under “Search Column” select, “Group Name [B]”.

Next, we need to retrieve the group name from our previous lookup of category managers:

Select the “Token” input icon.
From the dialogue select “Lookup category manager”.
Select “Row”
Add “Department [B] into the Search Value.

Retrieve Columns

Leave the “Retrieve Column” section as it is.

Review the Output

In the top menu of the configuration panel select input.

As you can see, “Lookup Rows” retrieves an array of objects for each row it finds with the matching search item. It also stores the total number or rows it retrieves in a Count.

Send a request to the category manager

We now need to automatically contact the category manager so that they can review the message, check if it is spam or not, and if it is good, assign a team member to the form request.

The “Request Data” action can be used for this. This action sends an email to the assigned user with a form attached. This allows the flow to assign a task or collect data at this stage.

Under the “User Interaction” action category, click and drag the “Request Data” action to below our previous lookup action.

As you can see, this action creates the primary “Request Data” action and 3 child actions:

Notify Email – Send an email with a form for the user to fill out.
Reminder Email – Send a reminder email which may be written slightly differently.
Expiry Email – Notifies the target user that they have missed the deadline to complete the form.

Each branch of the “Request Data” action allows for the inclusion of other actions specific to the branch.

Consider the “Request Data” action to be a specialised IF condition action.

Setup the Request Data block

Click the cog to access the configuration panel.

Let’s leave the form builder until later.

For recipients, we collected our category manager email previously. This means that we can click on the token insert icon (1), select “Lookup category manager” (2) and expand the “Row” (3).

Add the “Head Email” to the “Recipients” input field.

Reminders

It’s probably a good idea to send a reminder to the category manager (They’re busy people).

Change the first “After” to 6 and change from days to Hours (5).

Add a second reminder (6) for the next day (7).

Now head up and click the Form Builder icon (8).

Form Builder

The Request Data email will contain a link to a zenphi form.

Header Details

Update the “Title” with:

Click the insert token icon (1.b) and add the Category from the original Form Data.

In the “Description” add the text:

You have a new request for your team. Please either: 1. Assign a team member to the request.2. Report the request as spam.

Client Form Submission Summary

We should provide a summary of the form submission for the category manager to review.

Add a “Title and description” item from the options sidebar menu. A new block will appear.
Under “Title” add:

Under “Description” add:

The submitted request.

Under “Value” add the following keeping in mind that {{token}} items are replaced with the selected token.

Date Request Sent:{{Initiator. Submit Time}}
Name: {{Form Data.Name}}Email: {{Form Data.Email}}Subject:{{Form Data.Subject}}
Body: {{Form Data.Body}}

Make sure you add a space after the colon on each line so that the token values don’t join the text.

Is this Spam Option

If the category manager deems the form input as spam we want them to let us know so that we can update our Spam Sheet tab of our “Contact Us – Management (zenphi)” Google Sheet.

Add a new field with the plus button icon.
Select “Multiple choice” as the field.
In the question/description field type:

Is this Spam?

Add “Yes” and “No” to the two responses.
Mark the field as “Required”.
CLick on the vertical ellipses and select “Go to section based on answer”. This will open a dropdown menu option for each response item. For “Yes” select “Submit Form”. The manager doesn’t need to do anything else.

Assign a team member

If the item is not spam, we can assign a team member.

In the options menu select “Add section”.
In the title add:

Assign Team Member

Next, we need to add a dropdown list of all available team members:

Select “Add new field” plus button.
Change the field to “Drop-down”.
Add “Ass a Team Member” to the title/question.
Click ‘Add”Dynamic options”’. This will allow us to access our Consultants tokens.
Click the insert token icon.
Select “Lookup consultants” > “Rows”
Add the “User Name [C]” item. This will produce a list of all of the available users from the lookup.

The form is built. Click save to close the form builder and close the “Request Data” configuration dialogue.

Configure the Notify Email action

Select the configuration icon for the “Notification Email” sub-action of the Request Data section.

Here, we are going to prepare our email to the Category Manager. Select your previously built Gmail Connection (1). In the To section, you should already see the Category Manager recipient that we added previously in the main Request Data configuration (2).

Under “Subject” add (1):

ACTION: Assign new Contact Us request to team member

The body will come with some stock standard text. Update the following text in the body field (2):

Hi {{Lookup category manager.Head Name [ C ]}},

Please assign a team member to handle this Contact Us request.

After “Hi” insert the token “Lookup category manager” > “Head Name [C]”. (3)

Note that the link to the form is already included in the body.

The resulting email should look like this:

Note that the email should match the recipient, but in the example, this matches me as the tester.

Close the “Notify Me” configuration and save.

Configure the Reminder Email

Click the configuration icon for the Reminder Email sub-action. We’ll keep this one short. Select the connection and leave the subject and body as default.

Close the configuration panel and save.

Remove the Expiry Email

We won’t use an expiry email for this section so we need to delete it from our Request data.

Select the Expiry Email configuration panel cog and click the trash remove icon.

This will remove the email from the Expiry flow path and no action will be taken on this path.

Step 10: Update the Contact Us Sheet

Once the form submission has been assigned or marked as spam by the category manager we need to update the “Contact Us – Management (zenphi)” Google Sheet.

If Condition: Reviewed As Spam

If the form input was reviewed as spam by the category manager, we want to add the email to our “Spam” sheet tab. Alternatively, if it was a legit request then we want to update the “Contact Assignment” sheet tab with the submission id and the assigned consultant.

The “If Condition” action to the rescue again:

From the “Control Flow” action category, select the “If condition” action.
Rename the action to “Review as spam” to indicate what we are checking for.
Open the configuration panel.
Select the “Condition” Configuration icon.
The configuration dialogue will appear. Select the Insert Tokens icon > “Request Data”.
Add the “Is this spam?” form item.
Select “Equal to”
On our form for “Is this spam?” our two responses were “Yes” and “No”. Add “Yes” to the “equal to” response.
Select “OK” to save and close the dialogue.

Close the “If Condition” configuration.

Save the flow.

Now if the if condition is “Yes” marking it as Spam, then it will follow the “True” flow.

Marked as Spam

If the category manager marked the form response as spam we need to record it in our “Spam” section of the “Contact Us – Management (zenphi)” Google Sheet.

Here we can use the “Add row” action from the Google Sheets section.

Use your previous connection to Google Sheets.
Click on the Google Drive icon and search for your copy of the “Contact Us – Management (zenphi)” Google Sheet.
Select “Has Header”
Load the Columns. This will add the 4 columns to the sheet.
Insert the tokens for each header item:
- User ID [A]: Actions > Generate GUID > GUID
- Email [B]: Trigger (Form Trigger) > Form Data > Email
- Date Added [C]: Actions > Request Data > Submitted On
- Added By [D]: Actions > Request Data > Submitted By

Update Contact Assignment

If the category manager successfully assigns a consultant, we need to update the “Contact Us – Management (zenphi)” Google Sheet’s “Contact Assignment” tab.

On the “Contact Assignment” tab there are 5 headers:

Note columns E’s header, Assignment Response Time. We want to keep track of how long it took our team to assign a consultant to the form request.

Date Difference

We can use the date difference to calculate the time difference in Days, Hours, Minutes, Seconds and Milliseconds between the time the initial form was submitted and the time that the category manager assigned a consultant to the request.

We can use the Date Difference action to achieve this.

Find the” Date Difference” action in the “Operations” category and drag into the “False” flow of the “Review as Spam” If condition action.
Open the configuration.
Start Date: From the Token menu, Trigger (Form Trigger) > Initiator > Submit Time. This is the start time the initial form was sent and the flow was instantiated.
End Date: From the Token menu, Actions > Request Data > Submitted On. This is the time the category manager completes the form.
Change the interval to “Minutes”.

Add a new row to the Contact Assignment Sheet tab

Just like in the previous section we can use the “Add row” action from the Google Sheets section.

Using what you have learned so far, see if you can set up the action on your own and then come back and check to see how you went.

From the Google Sheets action list, select “Add Row”.
Open the configuration panel.
Use your previous connection to the Google Sheet to authorise this process.
Click on the Drive Icon and find your copy of “Contact Us – Management (zenphi)”
From the Sheet tab menus select the “Contact Assignment” tab.
In columns, select “Has Headers” and then “Load Columns”
User ID [A]: Generate GUID > GUID.
Department [B]: Lookup category manager > Department [B]
Assigned to [C]: Request Data > Assign a Team Member
DTS Assigned [D]: Request Data > Submitted On
Assignment Response Time [E]: Date Difference > Result

Close the configuration panel and save.

That’s it! You’ve completed the entire Contact Us flow. Well done!

Just one final step remains.

Step 11: Run a published test

It’s time to see your entire flow in action. In this next, final step we will publish the flow and then run the flow through our 3 possible scenarios.

But first:

Select “Save”
Select “Publish”
Click the go back arrow to head to the flow main menu.

We will run through our three possible scenarios:

Submit a successful request.
Send from a record spam account.
Have the category mark a request as spam.

To run a flow we can use the “Start an instance” button in the top right of the flow dashboard.

Alternatively, you can get a copy of the link to the trigger form or open the trigger form in a new browser tab from the flow editor by clicking on the trigger configuration button and selecting the “Invocation” page.

Submit a Request

When you invade the flow the request form will load. Fill out the form using the data in the table below. Alternatively, fill out the form with our own information.

Title:	Good Email
Name:	[Your name]
Email:	[Your test email]
Selection:	Arrange Training for your Team
Subject:	We want to learn it all.
Body:	Hi, I would like to arrange a live training session for my team in the Education sector.

Then hit submit.

Refresh the dashboard.

You will notice that a new run has been added to the “Recent Runs” list. It’s status should be marked as “Executing”.

Now, go and check your emails.

You should see two new emails.

The bottom email is a copy of the “Contact Us Form” that would be returned to the sender of the form request. The opened email should look a little like this:

The next email, “ACTION: Assign new Contact Us request to team member” would be for the category manager:

Click on the URL to fill out the form.

1. Mark the email as not spam.

2. Select Next.

3. Assign any team member to the response.

4. Select “Submit”.

Head back to the flow dashboard and refresh it. Note, it may take a few tries before the refresh comes into effect.

Let’s check in on our “Contact Us – Management (zenphi)” Google Sheet to make sure everything has been entered correctly.

The form submission has been added with the form details and the unique user id.

The contact assignment has been added with the correct assignee and the time taken for the consultant to be assigned at 16 minutes (Which was correct because I went and made a cuppa).

Great, our main flow works. Let’s check the spam flow.

Spam account

Just like when we ran our first tests we will run an instance where we enter an account marked as spam.

Start another instance again and this time fill out the form as follows:

Title:	Spam Email
Name:	Scott Badactor
Email:	badEmail@demo.zenphi.com
Selection:	Billing and Accounts
Subject:	This is a hold up!
Body:	Hand it over easy-like!

Wait a moment or two and refresh the dashboard (1). It may take a few refreshes but you should see the flow has been completed.

We didn’t want to waste bandwidth on spammers so we have not recorded any data on them except for adding a log.

Click on run (2) to review the logged spam record.

Click on the last log in the “Steps” column (1).

This will reveal the log information.

You can also expand the last item in the “Log Stream” to show what was recorded in the log (2).

Success! The flow caught the spam item.

One final test remains…

New Spam account

For our last test, we want the category manager to mark a new email as spam.

Run a new instance of the flow and fill out the form as follows:

Title:	Spam Email
Name:	Mrs Sneaky
Email:	dodgyEmail@demo.zenphi.com
Selection:	Billing and Accounts
Subject:	I forgot my username and password.
Body:	Hi! I forgot all the details to my premium deluxe account. Please give me all the things!

You might be expecting just one email this time but because our fake spam email is, well… fake, zenphi will kindly let you know.

Click the email “ACTION: Assign new Contact Us request to team member” and then select the link to fill out the form.

Select “Yes” for “Is this Spam?”
Click “Submit”.

Note that the form button changes to “Submit because we assigned that option to that selection earlier.

Refresh the dashboard to see that the run is complete:

Let’s go over and check on the “Spam” sheet tab in the “Contact Us – Management (zenphi)” Google Sheet.

Also, we should have the first record of this user in the “Form Submission” tab.

Success!

Conclusion

That’s it! You have successfully completed a zenphi flow in a fraction of the time it would take to code it with a developer. Not to mention much more securely.

This tutorial has covered the basics of completing a complete flow in zenphi. You learned how to:

Create a new flow with a zenphi form.
Access read and append Google Sheet data.
Test your flow.
Use parallel flows to speed up processing.
Use If conditions to determine a flow path.
Make requests to team members to complete responses and set task reminders.
Run the flow in productions.

Well done no-coder!

Check out our Youtube channel to discover more tutorials to enhance your zenphi skills to build the flows you need to streamline your work processes.