Skip to main content

Attendant satisfaction survey report (Google Sheets)

  • Updated

Index:

  1. Demonstrative video
  2. Adding new data to a Google Spreadsheet via an API
  3. Creating a survey flow in Builder
  4. Integrating the search flow with Google Sheet
  5. Finalizing the search flow

Demonstrative video

 

Adding new data to a Google Spreadsheet via an API

1st Activate Sheets Service in your Project

Create Token de Oauth through Client ID, It's from Secret ID and a Google account to access the Google Developer.

 

2nd Add Project.

In the top left corner, click your current project. A new window will open and at this point you will click on Create New Project, in the upper right corner of this window.

 

The following image shows the step by step:

 

 

On one name to your project and salve.

 

3rd Activate Sheets Service in your Project

In the upper left corner, click the ☰. With this, a new window on the left will open and at that moment you will click on APIs and Services and then in Library. Search for the word Sheets and select the first option.

Now it's just Activate by clicking the button in the center of the screen.

This process must be repeated, but this time, click on ☰ and access APIs and services> Panel

 

In this new environment, click on Screen OAuth Consent which is on the left side menu and, in User Type, select External and click TO CREATE.

 

 

 

Then define a name for the API, enter the email address for support and developers. At the bottom of the page, click SAVE AND CONTINUE. Then, on the next page that will be displayed, click on the same button at the end of the SAVE AND CONTINUE. Do this until you reach step 4.

 

In the fourth step, you must go to the bottom of the page and click on RETURN TO DASHBOARD, which will be the OAuth consent.

 



Just go on PUBLISH APP, as in the image below:

 

The status will change to "In production".

 

Creating the Credentials

No left side menu, click on Credentials. On this page, click+ CREATE CREDENTIALS and then on the option OAuth Client ID.



 

Now select Web Application for choosing the Application type and enter the name of your choice. At this time, you will need a lot of attention as it is an important part. Insert in URLs Redirection Authorized the following portal links:

 

 

Then click Create and proceed to the keys:

 

 

At that moment, a window with credentials will appear. Save all this information!

 

 

Authorizing Credentials

 

The next step is to go to https://developers.google.com/oauthplayground and click on the gear in the top right corner. In this new pop-up, enable the option Use your own OAuth credentials and fill in the data you collected in the previous step.

 

 

To finish, select the email which the OAuth will be linked and click Allow:

 

 

Then click Token exchange authorization code to get the refresh token. This will be used to refresh the access token once it expires. Every 60.

 

Of CRTL+F to search for the word Sheets. Select the option Google Sheets API v4 to expand. Then select the option that has spreadsheets name:



 

After selecting, click Authorize APIs. If you ask to authorize access to unknown domains, no problem: just proceed as usual.

 

If this error occurs, click on the description that appears below the know more and copy the warning URL.

 

 

In Exchange authorization code for tokens, you will have an authorization code.

If the tokens were not generated automatically, click Exchange authorization code for tokens. 

You will need the Refresh token straight ahead. So, save this information.

 

 

Creating the spreadsheet

In this last stage of integration with sheets you will need to create a Google spreadsheet and name the columns.

ATTENTION: If you do not name the columns, the integration will possibly fail. Use at least one name “temporary”.

With your spreadsheet in hand, you will need to store the code, which is this information after the /d/ and before the/edit URL.

 

 

In our proposal, the spreadsheet will have the following columns:

 

data  ticket  agent time  score

 

You can include or exhear information tailored to your needs.

 

Creating a survey flow in Builder

1st To create the flow we need:

  • Service completion block;
  • Research performance block;
  • Justify Note block (optional);
  • Thank you block for the review.

 

The finishing block should be right after the human service block. If you use a router and sub-bots, a flow can be used to capture the satisfaction survey, for example.

 

The flow should follow this logic:

 

 

2nd In the Finishing block let's define the following variables:

  • agent;
  • ticket;
  • time.

 

Observation: You can use other names.

Actions > Input Actions > Set Variable

 

 

Each variable will have the following value:

agent > {{input.content@agentIdentity}}

ticket > {{input.content@sequentialId}}

time > {{input.content@team}}

 

Your variable name can be changed, but these values ​​are Blip defaults.

 



To ensure the user moves to the next step in the flow (Satisfaction survey), changes the Standard Output for the Satisfaction Survey block.

 

As a content suggestion for this block, we can put something like: “Your service was completed by the person who helped you, you are talking to me again, Joy! 😃”

 

 

3rd Block Satisfaction Survey

 

Click on user input and enable Save answer in variable It is Validate user input. Give your variable a name. Here, we will call score. 

 

In Validation type, choose Number and put an instruction to the user in case he makes a mistake, for example: "by Please enter a number between 0 and 5, where 0 is terrible and 5 is excellent.”

 

 

We will place the following exit conditions in this block: 

If the grade is equal to or less than three, the user will go to the justification block.

If the score is equal to or greater than 4, it will go to the checking block (or another final block you have).

 

 

Integrating the search flow with Google Sheet

HTTP Request

Now, let's create two Exit Actions in the Satisfaction Survey block of the HTTP Request type. Let's do this to integrate our search with Google Sheet.

 

The first will be to authorize Google Sheet. We will use the POST method and the URL https://accounts.google.com/o/oauth2/token

 

Empty Headers are left empty. In the field Save Answer, place status1 It is oauth.

 




In the body of this request, we will place the following code:

 

{

    "client_secret": "your_secret_id",

    "grant_type": "refresh_token",

    "refresh_token": "seu_refresh_token",

    "client_id": "your_client_id"

}



Remembering that this information must be replaced according to the data generated in your credentials. Gloogle Cloud Platform: your_secret_id, your_refresh_token It isyour_customer_id

 

Once the information has been changed, press ctrl + s to save.

 

 

The next request is to integrate with the spreadsheet. In this same block, go back to Exit Actions and select HTTP Request. Again, the method will be POST and, in the URL, you will place the following completed link:

https://sheets.googleapis.com/v4/spreadsheets/{{your spreadsheet code}}/values/{{tab name}}!{{chosen columns}}:append?insertDataOption=INSERT_ROWS&responseDateTimeRenderOption=FORMATTED_STRING&valueInputOption=USER_ENTERED

 

Example of a completed link:

https://sheets.googleapis.com/v4/spreadsheets/1nwvwDBddNRJoKe9P3OO6j4CO53c2JUyuzwgvTS1g2qg/values/note!A:C:append?insertDataOption=INSERT_ROWS&responseDateTimeRenderOption=FORMATTED_STRING&valueInputOption=USER_ENTERED

 

In headers, put KEY Authorization e Value Bearer {{oauth@access_token}}

 

In the body, place the following code (or depending on the variables you want to use):

{

   "values": [

   [

     "{{calendar.datetime}}",

     "{{ticket}}",

     "{{agent}}",

     "{{time}}",

     "{{score}}"

   ]

 ]

}

 

This variable {{calendar.datetime}} is standard in the Blip library for recording date and time.

 

 

The variables must be inserted in the correct order according to the column they belong to.

 

In Save Answer, place status2 It is response

 

 

Pay attention to the order of requests. The first must be to authorize, followed by the spreadsheet.

 

 

Finalizing the search flow

 

1st Block Justify Note

Here, we will thank the user for the evaluation and ask the user to explain the reason for their rating. You can record user input in an event log, direct it to human support, integrate with the spreadsheet. Use the imagination!

 

As an exit condition, we will direct the user to the block Thank Justification.

 

 

2nd Thank you for the justification

If the user leaves a message, we need to thank them for it.

Then, forward the user to the completion block. Remember to change the exit conditions according to the strategy you will adopt in your flow.



 

Example of a completed spreadsheet:

 

 

Now just publish and test!

 

Here, we leave some suggestions for phraseology, data recording and flow, but no one knows better than you what makes sense for your business!

 

If you need support, count on the Blip Customer Success team!

 

For more information, visit the discussion on the subject in our community or the videos on our channel. 😃

Related articles