Create Excel table with dynamic column names and populate quickly Office Scripts/GraphAPI

[schwibach]

Hello,

my use case is creating students lists with columns that a user (teacher) specified in a PowerApp and place the file in the user's onedrive.

I have everything working, but populating is very slow:

I pass class name, array of column names and an array of objects with all the student data to PowerAutomate.

I then create a new excel file with file of an empty excel file content stored in a compose action.

Then I create the table (named after the class) with the column names from the array.

I can then loop through the array of objects and add rows. But this takes about a second per row which is too long.

So, I am looking for a faster way (probably using Office Scripts of Graph API)

Creating the table with the Excel connector works reasonably fine.

I would would just like a script that will accept the json and then populate the excel table.

It should only be a few lines of code, but none of the videos I've watched really helped me.

Thank you guys!

[creativeopinion]

@schwibach Can you upload a screenshot of what your current flow looks like?

[schwibach]

@creativeopinion 

Here is a screenshot of the flow. It works as it should, but it takes much too long, so I would like to replace the apply_to_each loop with calling an office script that I will send the array of objects with the content.

If the script accepts the table name and the column names as separate parameters, then I would replace the create table step, too.

[schwibach]

I actually just had a quick fix for my use case.... I just turned on concurrency control and set it to max.

Now, it performs fast enough for me.

However, I see great potential in office scripts.

So, if somebody has a template for a working script that accomplishes that, I would be highly interested.

[schwibach]

Unfortunately, it's not an ideal fix for me....

The lists are not in alphabetical order when the Concurrency Control allows multiple inserts in parallel.

So, I'll have to find a solution with a script to pass on the array of objects to the excel file. 😞

[MichelleRanMSFT]

Hi @schwibach - you could try the following script:

function main(workbook: ExcelScript.Workbook, tableName: string, dataJson: string) {
    let table = workbook.getTable(tableName);
    let data: (string | number | boolean)[][] = JSON.parse(dataJson);
    data.forEach(values => {
        // Insert new row at end of the table
        table.addRow(-1, values);
    });
}

Depending on how your data is formatted, you might have to adjust this script to make sure the data can be parsed into the appropriate type. If you'd like to share some sample data, I can help with that.

Let me know if you have any questions!

[schwibach]

@MichelleRanMSFT 
Thank you

I created the script

Here is the JSON Array with a dummy object. Except for nbg_id all all values could also be null (even if selected to be added to the Excel table)

[

    {

      "Abwesenheiten_Empfänger_Anrede": "Herr",

      "Abwesenheiten_Empfänger_EMail": "aaaa@bnbb.net",

      "Abwesenheiten_Empfänger_Nachname": null,

      "Alter": 22,

      "Ausbilder_Nachname": "TestNachname",

      "Ausbilder_Vornamen": "TestVorname",

      "Ausbildungsberuf": "TestBeruf",

      "Austrittsdatum": "2024-12-01",

      "Bemerkung": "TestBemerkung",

      "Bemerkung_Mittelschule": "TestBemerkung2",

      "Betrieb_EMail": "Email@Betrieb.de",

      "Betrieb_Firma": "Betrieb AG",

      "Betrieb_Hausnummer": "1",

      "Betrieb_Kürzel": "ABCD",

      "Betrieb_Mobil":"01234/34670",

      "Betrieb_Ort": "Testort",

      "Betrieb_PLZ": "99999",

      "Betrieb_Straße": "Teststraße",

      "Betrieb_Telefon": "09000 958-0",

      "Direktorat": "B15",

      "Doppelmeldung_Direktorat": "B14",

      "Doppelmeldung_Klasse": "10a",

      "Doppelmeldung_Klassenart": "Fachklasse",

      "Eintrittsdatum": "01.06.2021",

      "Gastschulgenehmigung": "0",

      "Gastschülerart": "Gastschüler (Fachsprengel)",

      "Gastschülerberechtigung": "Bay. GSch, (n. Umschl.)",

      "Geburtsdatum": "01.01.2001",

      "Geschlecht": "Männlich",

      "Hauptansprechpartner_Anrede": "Frau",

      "Hauptansprechpartner_EMail": "email@email.net",

      "Hauptansprechpartner_Geschlecht": "weiblich",

      "Hauptansprechpartner_Hausnummer": "1",

      "Hauptansprechpartner_Mobil": "12345/3457",

      "Hauptansprechpartner_Nachname": "TestNachname",

      "Hauptansprechpartner_Ort": "TestOrt",

      "Hauptansprechpartner_PLZ": "12345",

      "Hauptansprechpartner_Personentyp": "Mutter",

      "Hauptansprechpartner_Straße": "Teststraße",

      "Hauptansprechpartner_Telefon": "12346/3423",

      "Hauptansprechpartner_Vornamen": "Sandra",

      "Klasse": "10b",

      "M365_EMail": "aaa@contoso.com",

      "Merker_AP": "AP",

      "Merker_Alle": "JOA alt1",

      "Merker_H": "H",

      "Merker_SZ": "SZ",

      "Merker_Z": "Z",

      "Nachname": "Muster",

      "Religionszugehörigkeit": "römisch-katholisch",

      "Rufname": "Max",

      "Schulnummer": "6999",

      "Schüler_EMail": "email2@gmx.de",

      "Schüler_Hausnummer": "3",

      "Schüler_Mobil": "017634974587234",

      "Schüler_Ort": "Nürnberg  ",

      "Schüler_PLZ": "90489",

      "Schüler_Straße": "Hauptmarkt",

      "Schüler_Telefon": "012489453480 342",

      "Stellung_im_Beruf": "mit Ausbildungsvertrag",

      "Teilnahme_Religion_Ethik": "EV",

      "Vorbildung_schulisch": "Quali. Mittelschulabschluss",

      "Vornamen": "Max",

      "Vorzeitige_Prüfung": "0",

      "nbg_id": "e74KsckNe+nerTESTNBGID/7yytQzmcVlsdfwefqA="

    }]

I get this error. 

I already found a script to do auto width of the columns afterwards.
When we manage to add the data I would like to order the table ascending, using the first column in the table.

Thank you so much for your help!

[MichelleRanMSFT]

How about this?

function main(workbook: ExcelScript.Workbook, tableName: string, dataJson: string) {
    let table = workbook.getTable(tableName);
    let data: object[] = JSON.parse(dataJson);
    data.forEach(obj => {
        // Insert new row at end of the table
        let values: (string | number | boolean)[] = Object.values(obj);
        table.addRow(-1, values);
    });
    table.getSort().apply([{ key: 0, ascending: true }]);
    table.getRange().getFormat().autofitColumns();
}

[schwibach]

I get this error:

Before running the script, I use the file content of an empty excel file to create an new one:

Then I create the table with the columns using the regular connector:

If it makes things easier, I could leave out the last step and we create a new table with a table name that we pass to the script and then column names, that we pass in an array to excel.

[MichelleRanMSFT]

Could you share the script that's causing the error? The one I shared earlier doesn't call console.error so I'm not sure where the error would be coming from. Additionally, how many columns does the table you create through the regular connector have?

[schwibach]

@MichelleRanMSFT  Sorry, I sent the wrong error message.

This is the one I get when using your script.

I tried to play around with it myself.

[MichelleRanMSFT · Accepted solution]

Hmm, it looks like it wasn't able to get the table. Have you confirmed that the table was successfully created through the previous action? We could also try creating the table in the script itself:

function main(workbook: ExcelScript.Workbook, worksheetName: string, tableRange: string, tableName: string, dataJson: string) {
    let data: object[] = JSON.parse(dataJson);
    // Create table, using object keys as headers
    // IMPORTANT: table range must match the number of columns
    let table = workbook.getWorksheet(worksheetName).addTable(tableRange, false);
    table.setName(tableName);
    table.getHeaderRowRange().setValues([Object.keys(data[0])]);

    // Insert a row for each object
    data.forEach(obj => {
        // Insert new row at end of the table
        let values: (string | number | boolean)[] = Object.values(obj);
        table.addRow(-1, values);
    });

    // Sort and autofit
    table.getSort().apply([{ key: 0, ascending: true }]);
    table.getRange().getFormat().autofitColumns();
}

Because the table range should match the number of columns, based on the sample data you provided, the range should be A1:BM1. Does that work?

[schwibach]

The table gets created with the action. (When I use the add rows action, the table works fine, too)

I tried the new script without creating a table, just the newly created file and I get this error:

We were unable to run the script. Please try again.
Runtime error: Line 7: Cannot read properties of undefined (reading 'addRow')
clientRequestId: e5ddd41b-5a8c-4fdc-9832-1674612f80f5

[MichelleRanMSFT]

When you say the "new script", do you mean the one I shared in the most recent post? The newest version I shared shouldn't be calling addRow on line 7.

Also, to make sure I understand what you're trying to accomplish: the table you shared a screenshot of only has 3 columns. Is that intended, or did you want a column for every key in the object? I was assuming it was the latter, but if not, the script would have to be modified.

[schwibach]

Oh dear... sorry for the confusion..

I think I forgot to save the script.

This is the error I get now:
We were unable to run the script. Please try again.
Office JS error: Line 5: {"message":"Das Argument ist ungültig, fehlt oder hat ein falsches Format.","code":"InvalidArgument","type":"Workbook","method":"getWorksheet","line":5}
clientRequestId: ea39e5b3-e2b7-4db8-8fe0-c0d0e51b1384


About the creation of the table: I wanted to pass on the json array of objects and have the script pick the values that match the column names. (The excel add row action does exactly that)

However, I could also use a select action to make a json array of objects that will only contain the columns needed. That would be fine, too. Whichever is easiest.

With the ShowColums and JSON function in PowerApps it's just as quick. 

[MichelleRanMSFT]

I see - so, two things here. First, when you edit a script that's being used in a flow, you might want to re-select it in the run script action to make sure the parameters update. In the latest version of the script I shared, I added a parameter for the worksheet name and I think it wasn't being picked up.

Second, if you only want to take specific properties from the objects, I think the most performant way to do this would be to use the select action to modify the JSON array. You can then pass the modified JSON array into the script from above. If it's still not working, you can debug it by using console.log (instead of console.error).

Let me know if that helps, or if you have any questions!

[MM]

ahhhh! this thing about concurrency I didn't think about made me crazy! thanks for pointing this out!