Businesses often use Excel to build questionnaires especially if there are many questions or if they need scoring calculated based on user responses. Excel’s user-friendly interface makes is easy to build such questionnaires. But collecting answers from users and aggregating them can be a challenging and time-consuming task.
With SpreadsheetWeb Hub, you can turn your Excel-based questionnaires into online scored questionnaires without any coding and build workflows in distributing, collecting, and aggregating user responses.
In this article, we will take a fictitious company sending a security questionnaire to a list of vendors. This company used to send spreadsheets to vendors. They would review submitted spreadsheets and send them back for further clarification on some of their answers. The process was inefficient because of sending spreadsheets back and forth. With SpreadsheetWeb, this company converted their spreadsheet questionnaire into a web-based application. Now, they can also automate the approval mechanism with a workflow for reviewers to review, give scores, approve completed questionnaires, and request additional information if needed. We will show you how you can use tags to manage and automate the entire workflow.
In this workflow, vendors fill out the questionnaire and submit when it’s done. When a vendor submits a questionnaire, an internal user, reviews the responses, assign reviewer scores and adds notes if needed. They can either approve the submission as a whole or notify the vendor to revise their responses if more information needed.
This "Questionnaire" shown below is created with a list of questions that the company wants to ask vendors, the options for the answers, scoring weighs and calculation formulas. It also includes conditional logic to control the visibility of each question based on a TRUE/FALSE formula. It is possible to incorporate complex logic that can be driven by specific answers to any prior question. Accommodation of fields that the reviewer can set a status to each question, Reviewer Notes and Reviewer Score are some of other features added to the questionnaire.
Once the questions and their configuration is finalized in the Excel file, the next step is converting it to a web application on SpreadsheetWeb. We will skip this step in this article since we have published similar articles and video tutorials on this subject. Refer to this video tutorial on how to convert an Excel-based questionnaire into a web application on SpreadsheetWeb.
In this article, we will concentrate on configuring the workflow. In this scenario, you must first create a role tag for the reviewer. Also, create tags indicating the stages for the ’Questionnaire Status’ to configure a workflow to manage a questionnaire from its creation to completion. We have created the following 3 stages; you can name these stages as you use them within the company.
- Questionnaire Status
- Waiting Review
- Approved
- More Information Needed
The next step after creating the tags is to create Identity Templates to determine the access, editing and viewing permissions of the workspace for Reviewer and Vendors. You can follow these steps to create identity templates:
- Creating an Identity Template for Reviewer:
- Select the appropriate user role tag in Workspace Rights > Is Associated With For example, Reviewer for a reviewer.
- Make sure at least Read only Access to All Applications is selected for Application Rights.
- Add Edit Access to Application Data by Tag in Data Rights. Add the tags relevant with the user role. For example, a reviewer can edit a record only if the record is in Waiting Review
- On the other hand, you may want to allow a reviewer to see a vendor's data without ability to edit it. For these types of scenarios, add the Read only Access to Application Data by Tag right with specified tags. E.g., Accepted and More Information Needed.
2. Creating an Identity Template for Vendors:
- You can skip Workspace Rights for the Vendor
- Make sure at least Readonly Access to All Applications is selected for Application Rights.
- Add Edit Access to Application Data by Tag in Data Rights and add More Information Needed
- Optionally, add the Readonly Access to Application Data by Tag right for Waiting Review tag to allow users to see their data although they cannot modify it.
After creating the Identity Templates, you can invite users to your workspace. You can select Identity Templates while inviting them to the workspace. Each invited user will receive an invitation email.
If invited users already have a SpreadsheetWeb Hub account, they can find and accept their invitations from Workspace > My Invites page. If invited users do not have a SpreadsheetWeb Hub account, they need to register even if your application is an unrestricted application which means that open to everyone who has the application link can save records, but an unregistered user cannot access the workflow features.
When invited users approve their workspace invitation, their workspace permissions specified for the selected template will be applied to their accounts.
To use Questionnaire Status, you need to modify the Excel File related with your application and add Questionnaire Status options with formulas and named ranges for the UI input, which will help to maintain the workflow process.
- IsReviewer: A formula that is checking if the user has "Reviewer" tag as an associated tag.
- Vendor_Email: Vendor's email, which is automatically parsed from user data, which is identical with a user's own tag. This info will be useful to save vendor info with the saved record.
Save your file and go back to your SpreadsheetWeb Hub Applications page, click on Go to Designer, and update the application’s UI according to your needs. Make sure to add new fields as a data field to preserve their state in the database as well.
You can follow these steps while updating the application’s UI:
- Add a TRUE/FALSE returning named ranges can be used to show or hide elements like buttons or grid columns. For example, you don't want a reviewer's notes to be seen by a vendor.
- Use buttons with Save event to maintain a workflow. Save event allows you to save your data with tags which are the key elements of distinguishing privileges in the SpreadsheetWeb Hub.
We used three buttons:
- Submit Answers: This button is the only button a vendor can see. It saves the record with user's own tag and Waiting Review tag to notify the reviewer(s).
- Request information: This button can be seen by reviewers only. It changes the Waiting Review tag to More Information Needed to allow the vendor to add more information to their answers. Assign tags by named range? ensures to keep vendor's tag for the data.
- Approve: This button can be seen by reviewers only as well. It will assign Approved tag to the record. Then the record will become non-editable by neither reviewers nor vendors.
When you are ready, do not forget to Publish your application to apply all changes and to make it online.
After the application is published, the lifecycle of a questionnaire will be as follows:
1. Send invites to your vendors. Each vendor should be registered to SpreadsheetWeb Hub to be able to validate their answers if needed.
2. After vendors successfully joins to your workspace, edit each user; Add user tags to Data Rights. Also, make sure to mark Should these tags be evaluated conjunctively? checkbox. This action will ensure a vendor can see only his/her records.
3. When a vendor opens the application, they can only see what you wanted to be seen. All reviewer-based columns will be hidden. Also, you can show and hide questions based on a vendor's previous questions. For example, " Are background checks for contractors the same as for employees?" question becomes visible only if the answer above is "Both".
4. After filling the questionnaire, vendor clicks Submit Answers button to save a record.
Note: A Spreadsheet Web Hub Application allows you to make inputs required to make sure all necessary inputs are filled, or you can restrict a user to submit a single record.
5. After saving, the record will be listed in the Data page. The eye button indicates that the record is for viewing only, which means, Vendors cannot edit answers unless reviewer changes the tag (status) to Need More Information.
6. While a vendor can only see own record, reviewers can see all records with a customized layout. In our example, the reviewer adds Total Score and Reviewer Score columns into the table. The columns are sortable to rank the vendors when needed.
7. Reviewer edits the records and either approves or requests more information by using dedicating buttons.
If the Reviewer clicks on ‘Request Information’ button the saved record will be displayed with ‘More Information Needed’ tag and Vendors will be able to edit the questionnaire.
If the Reviewer click on ‘Approve’ button the saved record will be displayed with ‘Accepted’ tag and Vendors won’t be able to edit the questionnaire.
With SpreadsheetWeb Hub, you can turn your Excel-based questionnaires into engaging web-based questionnaires with a no-code approach. You can also use the new Tags feature to manage user rights and configure approval processes to automate the workflow within your workspace.
