How to Automatically Route Form Data to the Correct Monthly Excel Sheet
IN THIS ARTICLE
If your team collects date-based data through a form tool, but that service does not send data directly to Microsoft Excel, you may still need every submission to appear in the correct monthly worksheet automatically.
In this example, users select different dates in one form, and the data must be sent to the correct Excel sheet based on the selected month and year. Before automation, this process was handled manually by a manager.
With Albato, you can automate this workflow using a form trigger, a JavaScript step to calculate the correct row, and the Branching tool to send each submission to the correct worksheet in Excel.
This setup is useful when:
- one form is used for many different dates;
- each month is stored on a separate worksheet;
- data must be written into a specific calendar-style position instead of being added as a simple new row;
- your team wants to replace repetitive manual updates.
Here is an example of the form used in this scenario.
And here is an example of the Excel template where the first day of the month starts in row 13, column C.
Before building the automation, make sure both services are already connected to Albato.
If needed, use these setup guides:
Create the automation
Go to the Automations tab and click Create new automation.
Switch the workspace to Canvas.
The interface will open in the new workspace view. Click Add trigger.
Select the form service and configure the trigger.
After the trigger is added, it will appear as the first step in the flow.
Click Continue.
Calculate the correct row from the selected date
Next, you need to calculate which Excel row should be updated.
In this example, the first day of the month starts on row 13, so the target row is calculated as: day of month + 12.
The most convenient way to do this is with a JavaScript step.
First, add a code step after the trigger.
Then configure the step.
For the date field, change the format to d-m-Y.
Add the incoming and outgoing fields and insert this code:
function mapDateToValue(dateStr) {
const match = dateStr.match(/^(\d{2})-(\d{2})-(\d{4})$/);
const day = parseInt(match[1], 10);
const mon = match[2];
const yer = match[3];
const row = day + 12;
return { row, mon, yer };
}
const { row, mon, yer } = mapDateToValue(date);
In this code, the line below determines which row in Excel will be updated: const row = day + 12;
If your template starts the first day of the month on another row, change this value accordingly. For example, if day 1 starts on row 10, replace 12 with 9.
After saving, your flow should look like this.
Use Branching to send data to the correct month
Now add the Branching tool so Albato can route the form data to the correct worksheet depending on the selected month.
Open the tool and save it first without conditions.
Then add the first branch condition.
Give the branch a name, for example sep, and save it.
After that, you will see the branch structure in the workflow.
Now configure the branch condition. The condition should use the month returned by the JavaScript step.
Add the Excel action for the matching month
Inside that branch, add an Excel action.
Choose the Microsoft Excel action to update the worksheet.
Then map the fields.
In the row field, use the value calculated earlier in the JavaScript step. This is what makes the record land in the correct place in the monthly calendar.
Duplicate the branches for other months
Once the first month is ready, copy the branch.
After copying, update the branch name and condition for the next month.
For example, if the previous branch was for September, change the new one to October and update the condition accordingly.
Then make sure the Excel action writes to the correct worksheet for that month.
You can repeat this process for as many months as needed.
In the worksheet selection field, choose the correct tab for each branch.
When all branches are configured, the automation will look like this.
Test and launch the automation
After all branches are ready, start the automation and submit a test entry through the form.
From that point on, each form submission will be:
- received by Albato;
- analyzed to identify the selected day, month, and year;
- routed through the correct branch;
- written into the correct monthly Excel worksheet and row.
This approach is especially useful when your team needs to maintain a structured reporting or calendar template in Excel, but the source form tool does not support direct delivery into the right sheet automatically.
Did this answer your question?