An automated timesheet reminder

May 19, 2022

At Capyx, we are human. And sometimes we forget some stuff, like submitting our timesheet of the month... But it is as simple as filling a table and clicking the “Submit” button. Since we would rather focus on real problems and we love making efficient and quality solutions, why wouldn’t we do it internally too? That is what I thought when Fabienne, our HR and Accounting Manager, confessed to me that she has more work when employees forget to submit their timesheets.

That is when an idea popped in my mind: what if I quickly wrote a script that would execute twice a month to send an email to Capyx’s employees when they did not submit their timesheet?

This article aims to walk you through my journey of writing the necessary modules to answer the above need.

Tell me more
Gaining visuals w Ku5yv AT0bg unsplash | Capyx


Let us rewind a little bit and make a list of specifications:

  1. We need to get a filtered list of employees’ professional emails (filtered because we only need those who did not submit their timesheet in time). For that, we can add a new endpoint to a .NET API I created when I first joined Capyx.
  2. As we are cool people, we don’t want some plain old text. We will send a funnier email by including a random meme from Google Image. For this point, we need to find an easy-to-use and free web service to query Google Image and return a list of image links.
  3. We need to send them an email twice a month (on the 21st and on the last day of the month). For that, we can use a cronjob on the Linux server which will simply execute our NodeJS script at specified time.

That concludes the starting point: we have our requirements. Now, let’s start diving into the development.

1 | Capyx

Coding and scripting

Get the list of emails

As I said, when I arrived at Capyx, I had to quickly build an API to query the ERP database. I think this is a good start for our first need: I can create a new endpoint protected by an API key as this is going to be program-to-program exchange on the same machine (a Linux server). Here’s the endpoint implementation.

I had to add a few things to the project, such as an extension method to the IQueryable class in order to facilitate the readability of the code, but also an ApiKeyAttribute to get the API key from the headers and validate it. Here the attribute’s implementation if you are interested in it.

2 | Capyx

The project is a basic 3-tier web API running on the server and granting access to a few information from the ERP database. That is pretty useful to be honest! And of course, there’s always room to improvement. I could have set the header key in the application settings so that we can change it whenever we want without having to recompile the application for example. I could also have gone for another architecture to facilitate the maintainability and testability of the app, and so much more of my choices are questionable. But the most important thing here is that once it is done, it is not absolute. As long as it works, you can change it as you want.

Anyways, with that done, we have our first working part! We can retrieve a list of users who have not submitted their timesheet for the provided month of the year.

Find a free web service to query Google Image

For the second part, I scrolled on the web and got a chance to test a few APIs but the only one that made me happy was SerpAPI. It has a free plan of 100 searches per month, way more than needed for our script, and it returns the link to the image file. Neat!

3 | Capyx

Build our script

Now that we have the different parts we need, we can build our script! Running a NodeJS script is easy with a cronjob, so I decided to go with that technology. Again, I could have used literally anything else but that is just what I am comfortable with right now and one of the most easiest and fast-to-learn technology I know. Plus a bunch of my colleagues know how to write JavaScript.

The code is separated in a few (CJS) modules. I am using nodemailer to send emails, and node-fetch to query the web services. The whole script relies on a list of mandatory environment variables set in a .env file and retrieved via dotenv.

NB: requiring the src folder uses its index.js file, which serves as an exporting module (I import then export all the folder’s “public” modules from there).

Execute the task

In a Linux system, you can use a cronjob to schedule the execution of scripts. As the above script does the verification of the date, I decided that the cronjob should execute everyday at 9 am. The cron syntax for “everyday at 9 am” is 0 9 * * *. I highly recommend to double check your syntax.

The second step to setup the automatic execution of the NodeJS app is to create a bash script that will run the command node index.js. As cron does not support relative paths, we will use absolute ones to target node and our index.js file. Two things to know: you can type pwd in your terminal to get your current folder’s absolute path, and you can also type which node to get node’s absolute path. This is the result set into the bash script.

/root/.nvm/versions/node/v16.15.0/bin/node /var/nodejs/submit-your-timesheets/index.js

The last step is to wire up the cron with the script. As simple as typing crontab -e and writing down the 0 9 * * * followed by the path to the bash script.

4 | Capyx
5 | Capyx


Now you save your changes and… That’s it! We now have a background job running everyday at 9 am executing our NodeJS app which will

  • query our API to get a list of emails of employees who forgot to submit their timesheet ;
  • query Google Image via the SerpAPI to get a random meme on timesheets ;
  • send an email to all those forgetful colleagues!

Here’s the result of a local test...

I hope your job will get easier with that, Fabienne! And you, how would you solve her problem?Tell us on our social media!

More about the author…

Tinaël DEVRESSE is a young IT consultant identifying as a Fullstack Developer. He started working for Capyx in June 2021 and is a big fan of food, games, knowledge sharing and open-source. Find out more on his website:


Keep me posted

Do you want to stay tuned ? Subscribe to our newsletter !