Dynamically Create Sheets with Dynamic Table Columns. Doable?

(0) Share

Report

Posted on by karimabdelrazek

294

HI,

I have a survey saved in a DataVerse table called "Surveys". The survey consists of categories, and each category contains a list of questions under it (e.g.

SurveyName: TestSurvey1

CategoryA
- Q1UnderCategoryA
- Q2UnderCategoryA
- Q3UnderCategoryA
Category B
- Q1UnderCategoryB
- Q2UnderCategoryA

I would like to create a flow that creates an Excel Workbook. The name of the workbook should be "Survey Name". Inside this workbook should be the sheets for each category (i.e. first sheet would be called "CategoryA" and second sheet called"CategoryB"). Inside each sheet, the questions should be in columns.

Important is that the categories and questions will be dynamic (i.e. different surveys will contain different categories and questions).

Is this doable? If yes, could you please walk me through it?

Thanks!

Karim

Categories:

Building flows

I have the same question (0)

All responses (9)

Answers (1)

Nived_Nambiar 18,129 Super User 2025 Season 2 on at

Like (0)

Report

Hi @karimabdelrazek

need one confirmation on dataverse table design, would each row be each question ?

Thanks & Regards,
Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

Was this reply helpful? Yes No
karimabdelrazek 294 on at

Like (0)

Report

@Nived_Nambiar . Yes, that is correct.

Was this reply helpful? Yes No
Nived_Nambiar 18,129 Super User 2025 Season 2 on at

Like (0)

Report

Hi @karimabdelrazek

may be forget to ask a question, does survey name is also a column here ?

Thanks & Regards,
Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

Was this reply helpful? Yes No
karimabdelrazek 294 on at

Like (0)

Report

@Nived_Nambiar . Survey name would be the name of the Excel Workbook

Was this reply helpful? Yes No
Nived_Nambiar 18,129 Super User 2025 Season 2 on at

Like (0)

Report

Hi @karimabdelrazek

Ok so there would be a workbook with a name SurveyName: TestSurvey1 right ?

Thanks & Regards,
Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

Was this reply helpful? Yes No
karimabdelrazek 294 on at

Like (0)

Report

@Nived_Nambiar just "TestSurvey1". Apologies if I didn't clarify that in my first post.

Was this reply helpful? Yes No
Verified answer

Nived_Nambiar 18,129 Super User 2025 Season 2 on at

Like (1)

Report

Hi @karimabdelrazek

Finally i build a flow which is simmilar to your requirement :), learned something new while trying 🙂

For demo purposes, i have created a survey table in dataverse like this

Here category column is a choice column.

Also I have created a workbook named TestSurvey.xlsx and placed in SharePoint folder.

Now let's see the flow

1. Use list rows to list all rows present in surveys table

2. use select action to select category labels like below

Expression -

item()?['cr1c0_category@OData.Community.Display.V1.FormattedValue']

Note- in my case, Category is choice column and in dataverse choice column value is represented by integer, so to get the label of that i have went through attributes present in List Rows output to check which attribute contains category label , For me the above expression represent the category label.
3. After that loop through output of select actions after applying union function/expression

The next 5 steps will be done under inside the loop

3.1 use create worksheet to create the worksheet with name of sheet as CategoryLabel

3.2- use filter array to get list of all rows corresponding to category

Expression used-

item()?['cr1c0_category@OData.Community.Display.V1.FormattedValue']
This would be different for you (as discussed above)

3.3- Now use select action to select all questions corresponding to that category

3.4 - since your table is dynamic, you need to provide dynamic range while specifying in Create Table action. So for that we need to get end column by which we can create table out of that.For that we will create office script which will take number of columns as input and give the column letter of last column as output

function main(workbook: ExcelScript.Workbook, iCol: number) {

    let a: number;

    let b: number;

    a = iCol;

    let result: string = "";

    while (iCol > 0) {

        a = Math.floor((iCol - 1) / 26);

        b = (iCol - 1) % 26;

        result = String.fromCharCode(b + 65) + result;

        iCol = a;

    }

    console.log(result);

    return result;

}

write this office script in the excel file and use Run script to run the script or use run script from SharePoint if your script is stored somewhere.

Now use run script to run the above script (I have written this script in TestSurvey.xlsx so will call script from same)

3.5- use create table to create table of given range like below

So flow will create table with same name as of sheet.

So you will see results like below

Thanks & Regards,
Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

Was this reply helpful? Yes No
karimabdelrazek 294 on at

Like (0)

Report

@Nived_Nambiar Nice work! Amazing stuff! I am by no means a techie and the solutions I get show me just how little I know. Thank you!

Was this reply helpful? Yes No
karimabdelrazek 294 on at

Like (1)

Report

@AlexEncodian. Thank you for the info. I am in the process of checking out their product set as we speak. Looks like I'm going to setup an account and explore it further. Thanks!

Was this reply helpful? Yes No