Skip to content

Google

How to Create a Free Basic Help-desk Tool in 15 Minutes?

Problem Statement

XYZ is a new residential complex with 250 units. More than 150 families have already occupied their respective flats. As 100 families are yet to occupy, the official association (the elected body that would manage the society) is yet to be formed. The ad hoc association currently in place can’t make big decisions to go for paid software to efficiently manage the various day-to-day affairs of residential complex complaints (such as electrical, plumbing etc.) from the residents.

Currently they are manually maintaining a register which is kept with the security personnel at entry gate and the residents can log their complaints. The facility manager and housekeeping/maintenance staff would go through the register and follow up with the complaints. It was going well and good till the number of complaints were limited but as the number of complaints increased the process of assigning the task and updating the task status became very hectic. To log the complaint the residents has to go to the entry gate – not so user friendly!

They soon realized the need to automate the process to some extent using some free tool.

Note: The above problem statement is just a sample scenario. The solution provided here will be same even for a full fledged existing apartment complex.

Solution

The ad hoc association (committee) discussed the problem with one of the residents who is a software developer. The prerequisite was that the proposed solution should be free to use. Fortunately, the software developer was a big Google fan. The developer proposed the following services from Google:

  • Google Forms (and Spreadsheet combo).

Google Forms can easily collect the complaints online and the Spreadsheet can be used to view,  assign and track the the complaint status. Google Forms has been extremely popular as a tool to replace paper forms, collecting information and planning an event etc.

In the current solution we’ll go a little beyond the regular basic form features to implement some of its lesser known advanced features such as:

  • In Spreadsheet:
    1. Auto-generated ticket number for reference.
    2. Color coded display for work status.
    3. View/Edit permission settings for spreadsheet.
  • In Form
    1. Pattern matching tips for validations.
    2. Page Break [Move to a specific page based on drop-down selection]

Interestingly, you don’t need to know any programming language as such to implement this.

Can I See the Sample Form and Spreadsheet?

Yes, before we go for the implementation details have a look at the end result:

  1. XYZ Residents Association – Form
  2. XYZ Residents Association – Spreadsheet

Prerequisite

If you are new to Google Form please visit the following link: basic of Form Creation.

Implementation Details

The following steps are needed to achieve the above Form and Spreadsheet.

  1. Log in to Gmail and go to Drive.
  2. Click Create > Form.
  3. Choose Title and Theme.
  4. Add Items to the Form based on this Sample Form
  5. Once you complete the Form creation go to View Live Form.
  6. Fill the Form and Submit it. Now check the entry of your complaint from View Responses link (spreadsheet link) present in the Form.
  7. At this point in time the Spreadsheet contains the same field as the Form with one extra field named – Timestamp.

If you’ve completed the above steps you already have the basic entry Form and Spreadsheet. Now let us check what else shall be implemented to make it practically useful:

Action to be Taken in  the Spreadsheet

1. Add a column for Complaint Reference Number. I’ve named it as Ticket-Id. [Column A]

2. Go to Tools > Script Editor 

3. Copy/Paste the following code:

function onFormSubmit(e) {

var sheet = SpreadsheetApp.getActiveSheet();
var row = SpreadsheetApp.getActiveSheet().getLastRow();

sheet.getRange(row,1).setValue(row);
}

4. Save the script and then go to the Current Project’s Triggers menu

5. Select the DropDown lists as shown in the following image and click on Save button:

Trigger-Script

6. Add Assigned to in the column H and Status in the column I. DropDown list can be added from the menu named Data > Validation as shown in the following image for Status column for your reference.

StatusList

Select column I [Status] by clicking at the top [at the letter I]. Now right click and select Conditional Formatting from the context menu. [You can also select it from the top menu named Format. See the following figure and choose the color of your choice. Once selected click Save Rules.

Conditional-Formatting

Form Validations

  • Check Required Question check-box where ever required.
  • Flat Number validation – Here the association has shared the following business rule that must be validated.

A valid Flat# is exactly a 4 digit number. 2nd digit is always 0. 1st digit is any number between 1 and 5 (both inclusive). 3rd digit is between 0 & 3 (both inclusive). 4th digit is between 0 & 9 (both inclusive)

To achieve this, go to Advanced Settings, select Regular Expression from the 1st dropdown list and select Matches from the second dropdown list. Enter [1-5]{1}[0]{1}[0-3]{1}[0-9] in the adjoining text box.

Tip: You can validate email-Id, US ZIP codes, phone number etc. Learn more about validations here at Google Support.

  • To go to a different section (page) based on selection of Complaint Type, you need to insert a Page Break in question titled Complaint Type. Read the details here.

Now you can share the Form and Spreadsheet link via email. You can also embed Form and Spreadsheet in Google Sites and that’s better way than sharing it via email. Make sure you take care of the View/Edit permissions in Spreadsheet. Edit permission should not be granted to general members.

How was it? Please share your experience of using Google Forms for some innovative use cases in the comment section.

Be Sociable, Share!
    The following two tabs change content below.

    Basant Singh

    Basant is a talented software developer who is very engaged in web & cloud platforms on a daily basis. He is a contributor to codingthis.com reporting on various news and troubleshooting on various platforms. Basant is very strong in SQL and a great asset to the CodingThis community.

    Latest posts by Basant Singh (see all)