Integrating a spreadsheet with a workflow

Written by Peter Hilton | 8 min read
Published on: March 30th 2017 - Last modified: November 13th, 2020

Signavio Process Governance tutorial that shows how to automatically read and write spreadsheet data in a customer support workflow

Sometimes workflows use information that you manage centrally, such as information about a company’s products or employees. This happens when tasks in a workflow need access to data that isn’t part of the process model.

This article shows an example of mixing a customer service workflow with data in a spreadsheet that keeps track of which customer service representative has previously handled requests for each customer. You’ll see how integrating a spreadsheet with a workflow makes it possible to record which customer service rep works with each customer, and automatically assign the same customer service rep for subsequent enquiries.

Responding to customer email enquiries

This example starts with the  ‘Customer service - Respond to customer email’ example process that you can select from the ‘Examples’ menu in Signavio Process Governance. This process starts when a customer sends an email that contains a request or question, and includes a single task and a notification.

Responding to customer email enquiries, published earlier on this blog, explains the rationale for using such a simple process model. It turns out that workflow automation can offer significant benefits, even for trivial business process models.

One benefit of this workflow comes from assigning a ‘Customer support rep’ role, for the person who will write the response to a customer query. In Signavio Process Governance, use the ‘Assign using a role’ option in the ‘Write response’ action’s configuration.

This task assignment makes it possible to keep track of who is working on which customer request, in a support team. You can also use the assignment elsewhere in the process, such as in the signature of the generated response email’s template.

Suppose that your support team prefers to assign the same customer support rep to future queries from the same customer, to improve the customer experience and allow support staff to develop better relationships with customers. To make that easier, you could improve this workflow by automatically assigning the same customer support rep the next time the same customer sends a request. However, to do that, you’re going to need to maintain a list of pairings between support reps and customers.

Building a customer contact person spreadsheet

One way to keep track of which support rep looks after each customer is to maintain a spreadsheet of these long-term ‘assignments’. For this example, create a spreadsheet using Google Sheets that has two columns, with ‘Customer’ and ‘Customer support rep’ column headers.

The plan is to populate this spreadsheet with email addresses, which the workflow will use to uniquely identify people. Instead of doing this manually, you can can modify the workflow to add rows to the spreadsheet automatically. Adding data is the first part of integrating a spreadsheet with a workflow.

Add an ‘Assign customer support rep’ user task to make the assignment, a ‘Copy email addresses’ JavaScript action that captures the two email addresses, and a ‘Record customer support rep’ a Google Sheets action to add a row to a spreadsheet.

Now configure the three new actions as follows. First, the manual ‘Assign customer support rep’ task has a form field for the ‘Customer support rep’ assignment. This is a required field, so the person who completes this task must assign the role before the process proceeds.

Next, the ‘Copy email addresses’ script task copies the email addresses from the trigger email sender and the ‘Customer support rep’ field to text values that we can add to a spreadsheet. First add Text variables called ‘Customer email address’ and ‘Support rep email address’, then set the script body to these two lines of JavaScript.

customerEmailAddress = triggerEmail.from[0]
supportRepEmailAddress = customerSupportRep.emailAddress

The ‘Add row to sheet’ task called ‘Record customer support rep’ maps these two email addresses to columns in the Google Sheet you created earlier. For each case, this action will add a row to the spreadsheet that captures the support rep assignment for a particular customer, both identified by email addresses.

Finally, the process ends with the existing ‘Write response’ and ‘Send response’ actions, which were the only two actions in the original example process. However, this time the ‘Write response’ task already has someone assigned to the ‘Customer support rep’ role, who will now receive a ‘Task assigned’ notification.

Now you have a new version of your process that adds a row to the spreadsheet each time you complete a case. The next step is to use this spreadsheet data for automatic task assignment.

Automatically assigning the customer support rep

You can use the email addresses in the spreadsheet to assign the customer support rep automatically. This will work by looking for the row in the spreadsheet whose ‘Customer’ email address matches the email address that started the case, via the email trigger. Reading data is the second part of integrating a spreadsheet with a workflow.

Add a script task at the start of the process that reads the spreadsheet, and looks up the customer support rep email address for the trigger email’s ‘From’ address.

The gateway then skips the manual assignment task, and subsequent spreadsheet update, if the script task found an existing assignment in the spreadsheet. Add the following ‘Assign customer support rep automatically’ script task.

const csv = require('csv')
const request = require('request')
const users = require('users')

const parseCsv = (error, rows) => {
  if (error) {
    console.error(error)
    return
  }
  const customerEmail = triggerEmail.from[0]
  console.log(`customer: ${customerEmail}`)
  const assignment = _.findLast(rows, (row) => row.Customer === customerEmail)
  if (assignment) {
    const customerSupportRepEmail = assignment['Customer support rep']
    customerSupportRep = users.findByEmail(customerSupportRepEmail)
  }
}

const parseHttpResponse = (error, response, body) => {
  if (error) {
    console.error(error)
  } else if (response.statusCode !== 200) {
    console.error(`${response.statusCode} ${response.statusMessage}`)
  } else {
    csv.parse(body.toString(), {delimiter: ',', columns: true}, parseCsv)
  }
}

const sheet = 'https://docs.google.com/spreadsheets/d/12SkX_arZT7kIYXAKsmH4bCRjUa/pub?output=csv'
request.get(sheet, parseHttpResponse)

In the script, the ‘readCsv’ function finds the row in the spreadsheet that matches the customer’s email address, from the trigger email, and looks up the Signavio Process Governance user for the customer support rep’s email address from the spreadsheet.

Next configure the gateway to skip directly to the ‘Write response’ task if the ‘Customer support rep’ role has a value.

Now, when an email from a previously-used address starts a case, the first task will be a ‘Write response’ task that is automatically assigned to the right customer support rep. You’ve added a new level of automation to the workflow.

The next step is to think about future improvements. For example, you might prefer to match customer support reps to company’s email domains, so the same person handles all requests from example.com email addresses.

Conclusion

This tutorial shows how you can start with a simple workflow example and gradually add more automation. Integrating a spreadsheet with a workflow is a typical example of a straightforward integration that you can later replace with a more sophisticated solution, if required. This flexibility lets you choose which aspects of workflow automation you optimize, so your teams can work at maximum effectiveness.

This tutorial’s example also shows how you can use simple integration to start with, using an existing third-party service like Google Sheets. When you migrate from spreadsheets to using your own CRM system to manage customer support assignments, you can use Signavio Process Governance script tasks in just the same way.

If you would like to try integrating a spreadsheet with a workflow yourself, sign up for a free 30 day trial of Signavio Process Governance.

Published on: March 30th 2017 - Last modified: November 13th, 2020