Google Scripts, Docs and Spreadsheets Time Tracker



    The previous article dealt with the approach to the technical task in Decart IT production. When we implemented these changes, projects were conducted in cloud Jira, but its potential was used to a minimum. For a small company, a fairly competent task setting, time tracker, bug tracker and project and team statistics. It was much more convenient for the team to work with TK as a single document than with separate tasks in Jira, if only because of the ease of navigation in Google Docs (hereinafter - Docs). At the very beginning of work on a new TZ, thoughts appeared to simplify the process of work, somehow “finishing” Docs, but a series of projects did not leave time to dive into this question. And so, when time was found, I made a list of goals that we wanted to achieve:

    1. Time keeping in docs
    2. Reporting on staff time
    3. Reporting on project work
    4. Reducing the time to work with the system itself in the course of project implementation
    5. Avoid duplication of one information in different places.
    6. Spend a minimum of company resources

    But first, let's talk about technology.

    Google scripts


    Google Apps Script (hereinafter referred to as GAS) is a JavaScript dialect for creating automation scripts and extensions for Google services. Although GAS is not very popular in the Russian-speaking Internet community, there is no reason to doubt its usefulness. GAS is a good tool for automating various business processes, as it has a wide range of integrations. At the moment, the language has classes and methods for working with the following services: Tables, Documents, Forms, Disk, Gmail, Calendar, Contacts, Maps, Groups, Translator.

    One script can work with several services at once, which will allow you to create a combination of tools suitable for your task.

    Also, with the help of Google Apps Script, you can create full-fledged web applications with graphical HTML interfaces / embed Google Apps Script on your websites. The simplest example: to make on the website a form for downloading files from a computer to Google Drive.

    As a result, I would like to note the following advantages:

    1. Gas is quite easy to use.
    2. A wide range of integrations with various services
    3. Works in the cloud
    4. Convenient tools for debugging and logging
    5. The ability to fine-tune access rights

    As constraints, we note the following:

    1. Limits on the number of requests, the number of documents created, etc. The limit on the maximum execution time can force you to spend more time on code optimization than you would like
    2. Has some restrictions with CSS
    3. You must have a Google account to design and use extensions.

    In general, GAS is an excellent tool for automating business processes of small companies, but can also be used for more serious tasks.

    We now turn directly to the implementation.

    Structure


    To store and visualize data, we decided to use Google Spreadsheets and built a simple architecture based on three tables.

    Table “Labor”


    We did it right away for a year. Sheets - months. Columns:

    1. Day of month
    2. Who
    3. Project
    4. Task id
    5. Noted time
    6. Comment



    In fact, this is our base, on the basis of which other tables are formed. Each line represents a separate record of labor costs.

    Table “Monthly Report”


    It is needed so that both the employee and the management saw on which day over which projects and how much was worked. Sheets - employees. Columns:

    1. Day of month
    2. Worked for the day
    3. Project
    4. Task id
    5. Task
    6. Noted time
    7. Comment



    The number and total time for this number are on a separate line, the labor costs themselves are lower.

    Table “Project Work”


    It allows you to understand the time allotted for the task, how much has already been spent, how many hours each department has left (design, front-end, back-end). Sheets are product versions. Columns:

    1. Section TK (h1 headers)
    2. Task id
    3. Task
    4. On 2 columns on each department: it is fulfilled and an assessment (in hours)
    5. Total
    6. Balance. Grade minus spent
    7. Who worked on the task and how much spent



    The last line is a summary of the DL columns.

    How it works


    We wrote this add-on in google scripts, which after installation can be used in any Google Document using the “Add-ons” tab. When all documents are signed with the customer, we make a copy of the TZ, with which we will continue to work. During project initialization, it is checked whether the table “Project Work” has already been created. If not, a new one is created. If so, a new sheet is added to the old one. For correct work, of course, it is necessary to name the files according to the template chosen by us, but this can be omitted.



    All tasks should have the following name:
    Task_name (Design_time_value + Frontline_time_value + Backend_Evaluation_) [Task_id].
    The ID is generated automatically when the project is initialized.

    Now the file is ready for work. To mark the time, you need to put the cursor on the task header, and in the menu select the item “Add labor costs”, when you click on it, a window will open.



    Man-hours are immediately added to all the above tables. Under the title of the task in Docs appears the line "Participants", where all those who worked on it with the indication of the marked hours are listed.



    Bugtracker


    For each project, in addition to the files with the TZ versions, we create a file with errors, which in terms of the system is the same version. Evaluation of tasks (time paid by the Customer) will be zeros.

    But unlike the file with TZ, it will be constantly replenished. In this case, we implemented a single addition of the task, as well as a change in the name / evaluation of the task in the tables, if they have changed.

    To prevent the bugs from being lost in this file, we duplicate the headings of all the fresh tasks into a separate chat in the telegram indicating the project, the level of importance and the responsible developer. When the bug is fixed, the developer responds with a plus, and the PM checks and removes from the chat all messages related to this bug. Thus, the goal is an empty chat.

    Yes, in terms of the bugtracker, we could come up with a more elegant solution, but this approach did not require any modifications from us, and at the same time it showed itself well.

    Future plans


    At the moment we have done the MVP, and in the future we will implement at least task statuses (now it's just a highlight) and basic financial statistics, without turning the project into a Frankenstein monster.

    Thank you for reading to the end, and have a nice day!
    Tags:

    Also popular now: