Hello Zharevski,
I have forgotten I had written this response, it’s been a while since then. In any event, the code has change since then making it a bit easier to implement although perhaps not as easy to follow. In the end, it yields the same results. Here is what has changed.
The main steps are to convert any incoming data into an Excel range. First, I determine some necessary parameters from the incoming columns list, and then use those parameters to determine the range.
The first step is to determine how many chunks of 26 are in the incoming columns list (an array or JSON records) using the MOD operator and the remainder (what’s left over from the last 26) using the DIV operator as follows.
mod(length(Columns_List), 26)
div(length(Columns_List), 26)
The results are then used on the next step to determine the Excel Table range as follows.
concat('A1:',
if(and(equals(outputs('Set_Excel_Range_parameters')['MOD'], 0),
equals(outputs('Set_Excel_Range_parameters')?['DIV'], 0)),
substring(outputs('Set_Excel_Range_parameters')?['LIST'],outputs('Set_Excel_Range_parameters')?['DIV'],1),
if(equals(outputs('Set_Excel_Range_parameters')?['DIV'], 1),
substring(outputs('Set_Excel_Range_parameters')?['LIST'],sub(outputs('Set_Excel_Range_parameters')?['DIV'],1),1),
if(greater(outputs('Set_Excel_Range_parameters')?['DIV'], 1),
substring(outputs('Set_Excel_Range_parameters')?['LIST'],sub(outputs('Set_Excel_Range_parameters')?['DIV'],2),1),'')
)
),
if(and(equals(outputs('Set_Excel_Range_parameters')?['MOD'],0),
equals(outputs('Set_Excel_Range_parameters')?['DIV'],0)),
'',
substring(outputs('Set_Excel_Range_parameters')?['LIST'],
if(equals(outputs('Set_Excel_Range_parameters')?['MOD'],0),25,sub(outputs('Set_Excel_Range_parameters')?['MOD'],1)),1)
),
'1'
)
I hope this helps you. Let me know if you have any other questions.
