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

(0) Share

Report

Posted on by schwibach

2 Moderator

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!

Categories:

Building flows

I have the same question (0)

All responses (16)

Answers (1)

creativeopinion 10,502 Super User 2025 Season 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No
schwibach 2 Moderator on at

Like (0)

Report

@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.

Was this reply helpful? Yes No
schwibach 2 Moderator on at

Like (1)

Report

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.

Was this reply helpful? Yes No
schwibach 2 Moderator on at

Like (0)

Report

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. 😞

Was this reply helpful? Yes No
MichelleRanMSFT Microsoft Employee on at

Like (0)

Report
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!

Was this reply helpful? Yes No
schwibach 2 Moderator on at

Like (0)

Report

@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!

Was this reply helpful? Yes No
MichelleRanMSFT Microsoft Employee on at

Like (0)

Report
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(); }

Was this reply helpful? Yes No
schwibach 2 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
MichelleRanMSFT Microsoft Employee on at

Like (0)

Report

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?

Was this reply helpful? Yes No
schwibach 2 Moderator on at

Like (0)

Report

@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.

Was this reply helpful? Yes No