Trying to capture the content of an excel file to update a schedule - struggling with iterators

How To
1

Hello Community !

I’m struggling with something that I thought would be pretty simple but seems more tricky than expected.
I have a Microsoft Excel file on a Sharepoint.
I connect to it to get the content of a table.
The structure of my file is the following :
First Column : roles in an ‘on duty’ service (L1 Active Agent / L1 Backup Agent / L2 Active Agent / L2 Backup Agent’)
First line : dates in format YYYYMMDD. I didn’t put a ‘header’ on my excel table to be able to read the first line using ‘excel list rows’.
4 next lines : Name of agents that are on duty according to their role and the date.

I would like to isolate in this excel file the agents that are on duty in the 7 next days to finally get a JSON like this :
[
{
“day”: “tuesday”,
“date”: “20251001”,
“l1ActiveAgent”: “Bob”,
“l1BackupAgent”: “Fred”,
“l2ActiveAgent”: “Alice”,
“l2BackupAgent”: “Sam”,
“l3ActiveAgent”: “Ben”
},
{
“day”: “wednesday”,
“date”: “20251002”,
“l1ActiveAgent”: “Bob”,
“l1BackupAgent”: “Fred”,
“l2ActiveAgent”: “Alice”,
“l2BackupAgent”: “Sam”,
“l3ActiveAgent”: “Ben”
},…]

First I set some variables based on ‘now date’ and using ‘format’ to set the next 7 days dates
Then I’m trying to read the table in the excel file to extract the values for each day and agents per day but

  • I couldn’t find an easy way to get the column index based on the date formatted as YYYYMMDD. The only solution I ofund is to work with an iterator, put some tests on the value and save the index in variables, but then I have 7 distinct iterations in hands and I can’t come back to one single flow…

  • I’d like after that to read all other rows and set the values I found based on indexes in variables, but again I have to loop with an iterator and I can’t come back to one single flow easily

The final step after reading all values would be to post to an API the updated list of agents being on duty for the 7 next days.

I’m probably on the wrong strategy… I’m pretty sure there is an easy way to do it with Make.

Can you help ?

1 Like
3

Hi @Frederic_Batteux Welcome to the community!

Please share the necessary details of the scenario with screenshots, errors received and explain the configuration or mapping so we can help further.

Best,
@Prem_Patel

4

Hey Frederic,

Can you share the output bundle from the excel module to confirm what the data looks like?

If I understand correctly you are using the table sideways right? So the first column is one entry with a header, a date and then 4 rows with names? Then I assume the next column is the same but a new entry for another date? Is this correct? Can you share the output bundle from the excel module to confirm what the data looks like?

5

Thank you for your responses. I won’t waste your time. I found another way to meet my needs by creating a JSON file that is easy to manipulate.

1 Like