This tool generates personalized employer statements, for employees who have to work during curfew hours in the Netherlands. The tool generates personalized statements, based on the employee's personalia and working hours. This tool consists of two components, that can be used independently of each other:
- A web-frontend in which statements can be generated based on manual input
- An automated script that generates statements based on a Google Sheet
The script enables communication to the employee in the following ways, all of which are optional:
- Slack
- Manually communicating the URL or printing the document
The tool runs in Google Script. It uses a Google Slides document as an engine for generating PDF files. Google Spreadsheets are used for logging and configuration purposes.
We have an example environment running to showcase the webapp. It can be found here.
To avoid abuse of the example tool, right now access settings prevent anyone but the creator to generate actual statements. An example statement can be found here.
This section describes the steps to install the script on your own environment. Please start at "General setup", and then head over to "Setup webapp" or "Setup Automated Script" based on which environment you'd like to configure (first).
To make it easier to install this script and its dependencies, we created "ready to copy" versions of each of the required resources. Alternatively, the files in this repo can be moved over to your (company) Google Drive manually.
First, start by creating a copy of the script. Go to this link and press the copy icon:
We also have to copy the configuration sheet that can be found here:
Now, we have to link to configuration sheet to the script. To do that, we have to replace a small piece of code in the script. Don't worry, this is the only time we have to touch the actual code.
Navigate to the code editor in the script, then open the file Code.gs if it isn't opened already (it should open by default):
On line 1, there is a piece of code that contains the following:
var configId = "1saiF5RRjg3tLyEioRghMQDZHoJKNNbTh3nZqvZ--w4A";
Replace the ID of the example configation sheet 1saiF5RRjg3tLyEioRghMQDZHoJKNNbTh3nZqvZ--w4A with the ID of the configuration file you've just copied (it is part of the URL: docs.google.com/spreadsheets/d/1Ut0LqYJrMlk3FXyiP2scaEvey6vI9srxf49BrUleH2Q/edit#gid=0).
Finally, we have to create a copy of the employer statement template we prepared, that can be found here:
Copy the ID of the document over to the configuration sheet under the field Form file id (column A, cell 2):
In this employer's statement template, please fill in all fields that are not personalized per employee (like company address, company name, etc).
To set up the webapp, we first have to configure the logs, then we must deploy the application itself.
When setting up the webapp to generate forms, we need to create a file that will contain the logs. These logs can be used to track who generates forms using the webapp, for the sake of accountability.
Make a copy of the template file here (the same way as we've copied the configuration file). Then, copy the ID of the newly copied file, and put it in the configuration sheet under the field Log manual (column B, cell 3).
Head back to the script file (the copied version you created during the general setup). In the top-right corner there should be a blue deploy button. Click it, then choose the "New deployment" option.
A popover will show up, allowing you to configure the webapp:
- The description doesn't matter, feel free to keep it empty.
- For Execute as, make sure it's set to Me (your Google account).
- For Who has access, choose an appropriate setting. Generally, we recommend limiting access to either your organization or only yourself. Further access restrictions can be configured, see the next section.
When you are finished configuring, click the blue "Deploy" button. Next, follow the steps on the screen to authorize the tool access to the relevant parts of your Google account. Finally, you will get a URL at which the webapp is located. The webapp is now ready to be used.
In the configuration sheet, the final column (column P) is named "Access to manual tool". Under this label, you can put down a list of email addresses that are allowed to generate employer statements. You can also choose to keep it empty, in which case any email address will be allowed to generate statements (this is of course subject to the access configuration that you chose in the previous section).
To set up the automated script, we need a log sheet, a data source, and we need to choose how to run the automated script.
When setting up the automated script, we need to create a file that will contain the logs. These logs can be used to track which forms were generated and sent successfully, but also to track potential failures.
Make a copy of the template file here (the same way as we've copied the configuration file). Then, copy the ID of the newly copied file, and put it in the configuration sheet under the field Log automated (column C, cell 3).
This section describes how to create a sheet that contains the employee data for which employer statements should be sent. Again, we have a template file that can be used, but the script can also be configured to read employee data in a different format. The template can be found here. You can copy this template and fill it with your own employee data. Then, you must reference the ID of this file in the configuration sheet in the cell below the Data file label (column D, cell 3). If you choose to go with this input data template, you can move on to the next section. If you want to customize it, the rest of this section has more information on the controls that are available for that.
If you choose to use different headers than the ones configured in the template file, or you'd like for other fields to be configured in the document template as well, you have to modify column I and J of the configuration file:
The data in the "Field" column should not be changed. It refers to the fields in the employer statement document. In that document, we gave each field a hidden "alt" value such the script can recognize them. However, the placeholder values in the "Cell" column can be changed to the appropriate cell names in your input data.
So, for instance, if your input data has a column named "Address" that holds the company address, you can set the cell next to <<company_address>> to [Address]. The script will replace the placeholder value [Address] by the value of the address cell for each employee in their personalized documents. The same thing already happens to the other cells, like [Role].
The field [Today] is somewhat special, since it doesn't have to be specified in your input data, but will automatically be set to the current date. You can override it with a custom value though, if you create a column named "Today" in your input data.
You can also put plain text or multiple placeholder values in the "Cell" values. An example of this is Van [Shift start] tot [Shift end] that we use to denote the hours in which an employee is exempt from the curfew. This configuration replaces [Shift start] and [Shift end] by the corresponding shift start and shift end times sourced from the input data. Because Van and tot will not be replaced, it results in the following in the generated document:
You don't have to use placeholder values in the cell contents. You can also hardcode something that must be filled in, like your company name. Although, rather than hardcoding it in the configuration, consider hardcoding it in the document template instead. Every field that is kept empty will not be replaced, thus safe precious performance.
Note: Make sure all date fields (like shift start, shift end, and birthdate) are formatted as such in your data sheet. So instead of a string value "today at 12 o'clock", the value needs to have a value in a date format that is recognized by Google Sheets, like "11-12-2020 15:00". If the fields are not formatted correctly, the script will fail.
After generating the document, the URL per employee is registered in the log file. From there, it can be printed or copied elsewhere. However, it is also possible for the document to be sent automatically to the employee. To do that, a few things need to be configured.
First of all, we must write the message that the employee will receive. You can put the message you want to be sent to the employee in the cell below Message (column E, row 3) in the configuration file. In the message, you should use the placeholder value [drive_url], which the script will replace by the URL of the generated report.
Next, we must configure either email or Slack communication (follow the corresponding steps below).
Email can be configured by configuring the information in the -- Email configuration -- section of the configuration sheet. In the cell below Log recipient (column F, row 8), put your own email. Every time the automated script runs, you will be sent a link to its logs. You can also keep it empty, if you do not wish to receive the logs of the script.
In the Employee email cell (column G, row 8) you can put a placeholder value of the email address of the employee. For instance, you can put the value [Email] in that cell, such that the script will use the email addresses from the "Email" column in your data sheet. The Email subject (column H, row 8) can be set to something like Here's your employer statement, which will be used as the subject of the email message that is sent to the employees.
As an example, consider the following communication configuration:
It will send the following email to the employee:
Create a Slack app in your organization. Configure it to have both a Webhook and an Authorization token with the chat.postMessage permission. Then, put the info in the appropriate fields in the configuration sheet. For the Webhook, you can paste the entire URL of the webhook in the Webhook cell (column F, row 4). For the Authorization token, copy the token (in the form xoxb-[unique ID here]) into the cell below Authorization (column G, row 4).
The Slack ID field should contain a placeholder value of the Slack id of the employee. Put the value [Slack ID] in its cell (column H, row 4), and then create a "Slack ID" column in your datasheet and put the Slack IDs of the employees there.
Finally, we've configured everything for the automated script, now it's time to either run it manually or set a trigger that runs it automatically. To send all employer reports for all employees that work today in the datasheet, we can run the sendForms function in the google script:
If we want to run the script automatically every day, we can configure a trigger. Go to the "Triggers" tab of the Google Script, and press "Create new trigger" in the bottom left corner. You will get the following popover:
Choose the sendForms function, choose a Day timer and set the time of day for the script to run corresponding to when you need the forms to be sent. Press "Save" in the bottom left corner to create the trigger. Of course, you need to make sure the data sheet is updated daily as well, before this script runs.
If you run into problems setting up this script, feel free to either start a discussion in GitHub or contact me at [email protected]. I'll be happy to help out. If you have any improvements for the script or the artifacts that come with it, feel free to open a PR with your changes. If you like what you see, consider starring the project.