Qlik Automation, Generate excel file based on the list

[DenisSinai]

Hello all,

I am using the Qlik Application Automation to create and distribute Excel reports in Office 365 developed by Matt Gayndler (https://community.qlik.com/t5/Knowledge/Using-Qlik-Application-Automation-to-create-and-distribute-E...) to generate excel report, save it on the sharepoint and distribute the link by email. It works perfectly with one file. But know I would like to distribute the reports based on the list of values in specific field in the app.

So I have updated the workflow by adding "List Values of fields" loop (see attached json file with workflow). So it works good by generating first file for the first selection but then the automation falls on the "Create Excel Table with Headers" block and the error is "The number of rows or columns in the input array doesn't match the size or dimensions of the range."

So it generates the new file but can not create a table in it for some reason and I can not figure out what needs to be adjusted.

Any help would be much appticiated.

Thank you.

[Frank_S]

Hi @DenisSinai

I would recommend that you follow up with the article owner by commenting in the comments section of the article therein if you do not get a response here in this discussion conversation thread.

• https://community.qlik.com/t5/Knowledge/Using-Qlik-Application-Automation-to-create-and-distribute-E...

Kind regards...

[DenisSinai]

Hi Frank,

Thanks for suggestion.

I have contacted Matt on private message and he responded so I hope he could help me. I will post the solution once I figure it out with Matt.

Thank you!

[Frank_S]

Perfect! 

When I tested this I ran into the '401' known error. So I did make an attempt but ran into this...

Look forward to hearing from you (and Matt).

Cheers!

[MattGrayndler · Accepted solution]

The example automation that builds a single report uses a number of variables to help to build a payload from the data,  to send to the excel API. It ends up looking like this [[‘Value1’,’Value2’]]

These variables need to be cleared when modifying the automation to loop through multiple selections and create multiple outputs. Without clearing, data gets added to the existing payload, the formatting no longer makes sense to the excel API, and it produces an error.

I had been working on a newer version of the automation that simplifies some of this logic. 

I have modified it to suit this use-case of multiple loops/outputs - see example automation here:

Scheduled Reports (Dimension Cycle).json 

Note Microsoft apply rate limiting when using their APIs too many times in succession, so depending on the volume of reports you are creating you may need to add a sleep at the end of each one to ensure you don’t trip this up.

[DenisSinai]

Thank you very much Matt.

This is working great and very helpful.

Thank you.

Denis.

[psublue98 · Accepted solution]

Hi @MattGrayndler - in using a scaled back version of this updated 2022 automation from the previous 2021 where you pulled together the video, I seem to be hitting some threshold with the Add Rows to Table (batch) block. Is there a max number of rows that can be batched?

The test table I'm using has 3 columns and 190 rows, and when executing the automation the excel table has headers but no values. If I scale back to approximately 25 rows it populates fine. Any limitations here?

 

[lewis255 · Accepted solution]

Hi Matt,

Thanks for this, this modified automation is working well for me.

The only issue I have is it throws up an error that the drive_id and item_id are missing, even though this is referenced from the OneDrive file created earlier in the automation. It doesn't prevent the data from being added to the excel spreadsheet but shows the automation as failed. Is there something I am doing wrong? 

I added a thread at Error - MS Excel Create Excel Table With Headers -... - Qlik Community - 2429339 but wondered whether you are able to assist.

Thanks,

Lewis

[lewis255 · Accepted solution]

Unfortunately it looks like Matt is no longer with Qlik, so has anyone else either found a solution or have this error too?

[AfeefaTk · Accepted solution]

Hi @lewis255 

Is this erroring out always? Or is it a random one?

Could you please share the automation json file?

Regards

Afeefa TK