Running a Basic Helpdesk with Google Sheets

• Configuration
• Mechanism
• Usage

Create a simple helpdesk and request ticketing solution with just forms and sheets.

Helpdesk management systems are common, but sometimes you just need something simple and easy to keep track of incoming issues and requests. The sheet below gives you this functionality within a single spreadsheet, out of the box. You can create your own form for your users to submit their issues, and connect it up straightaway. No formulas nor coding needed.

Configuration

The sheet above is provided with example data so that you can explore it straightaway. With some simple configuration (explained in the CONFIG tab, you can link it up to your form responses). Remember to click the ‘Allow Access’ buttons in the TEST column of the CONFIG tab once you entered in the URL of the sheet you want to link to (cell B3). This creates the import range link that allows issues data to be imported dynamically.

You should include a field to indicate the type of request/issue received. If you name this something other than Type, update the configuration in the TYPE_FIELD row to ensure that everything works correctly.

Mechanism

Spreadsheets are excellent for data analysis but are harder to use for structured data input (forms) or management. In this tool, a sidebar is used to handle data selection and input. Click on the Tracker menu, then Show to display it.

Each issue is assigned a code to help track it, and any log entries associated with it. This code is a hash of the submitting user and submitting timestamp. They should be unique for our purposes.

Usage

After displaying the sidebar, position your cursor on the issue row (on the HELPDESK tab) that you wish to comment on or manage. Click the Select button on the sidebar, which will then display the code and status of the selected issue. Fill in the form with the details you wish to log about the issue and click Log to complete the process. The issue row will be updated, and your logged data will appear in the LOG tab. If you mark the issue as complete, it will no longer be shown unless you set the status filter to ALL.