We are going to build an excel online file which will download into different worksheets the contents of all excel files inside a OneDrive folder. We used this approach to build a stock management tool so one of our clients could monitor all of his suppliers.
Why do we download all the content using office scripts instead of simply referring the excel files shared with the suppliers? Because Excel online doesn’t allow to reference other files.
To implement this solution we are going to use the following approach:
- Create a “Master Stock” excel file, which will have per Worksheet the stock available of each supplier.
- Implement a Microsoft Automate flow to monitor a folder of the suppliers excel files and check if there are any updates.
- Write an Office Script which receives from the powerflow the contents of the changed files.
One big advantage of this approach is the client doesn’t need any Microsoft 365 licenses, only who designs the solution does. The licenses we need can be a simple Microsoft 365 Business Standard which costs 9.5โฌ/month and inludes 50GB mailbox + 1TB OneDrive and All Office Software. The reason we can’t go with Business Basic is in order to activate Office Scripts on the tenant you need at least one License which includes Office Desktop apps.
We are going to create a power automate flows which monitors for suppliers stock changes and downloads the content into the Office Script in the Master Stock excel.
First: create the folder “Suppliers Stock” inside our OneDrive for business. This is where we’ll have all our Suppliers stock files.
Second: Let’s create the flow which monitors the Suppliers folder for any changes and sends the data to the Office Script on the Master stock file.
The result variable (which will be sent to the Office Script) holds the following JSON structure:
{
"file_name": "intel.xlsx",
"tables_config": {
"Table1": {
"table_headers": "{\"@odata.etag\":\"\",\"ItemInternalId\":\"379d1f43-5d4d-44dc-ae04-fe52ffc85756\",\"CPU\":\"i5\",\"Production Line 1\":\"244\",\"Production Line 2\":\"311\",\"Production Line 3\":\"221\"}",
"table_rows": [
{
"@odata.etag": "",
"ItemInternalId": "379d1f43-5d4d-44dc-ae04-fe52ffc85756",
"CPU": "i5",
"Production Line 1": "244",
"Production Line 2": "311",
"Production Line 3": "221"
},
{
"@odata.etag": "",
"ItemInternalId": "cf94a360-550b-42a4-a8fa-efa8a17e7fc2",
"CPU": "i7",
"Production Line 1": "111",
"Production Line 2": "234",
"Production Line 3": "155"
},
{
"@odata.etag": "",
"ItemInternalId": "26edb10d-be45-4492-a027-0ce4b3059e56",
"CPU": "i9",
"Production Line 1": "21",
"Production Line 2": "55",
"Production Line 3": "33"
}
]
}
}One small note about the power automate flow:
- Why the first row of each table is converted into a string ?
Because we need to know the order of the table header and if we wouldn’t save on a seperated variable and send it with the data to the Office Script, we would need to use theObject.keys()over the table rows, this doesn’t assure the correct table headers order.
Finnaly we need to create the Office Script in Excel online which will receive the stock data from the supplier and create on the Master Stock excel a copy of that table. Make sure you have Office Scripts enabled on your tenant, which requires at least one Microsoft 365 Business Standard account
- Open the previously created Master Stock excel file and on the Top Riboon click “Automate”.
- Then create a “New Script” and paste the following code
interface files_with_tables {
file_name?: string,
table_config?: {
[table_id: string]: {
table_header: string,
table_rows: { [header: string]: string }[]
}
}
}
let files_with_tables: files_with_tables = {}
function main(workbook: ExcelScript.Workbook, files_with_tables_json: object = {}): string {
const row_columns_to_ignore = ['@odata.etag', 'ItemInternalId'];
files_with_tables = files_with_tables_json as files_with_tables;
let table_start_cell_row = 0;
const file_name = files_with_tables.file_name;
//if there is no worksheet with the file_name, then create
let worksheet = workbook.getWorksheet(files_with_tables.file_name);
if (worksheet == null) {
worksheet = workbook.addWorksheet(file_name);
}
Object.keys(files_with_tables.table_config).forEach((table_name: string) => {
let there_is_table = false;
const table_id = `${file_name}_${table_name}`;
table_start_cell_row += 2;
const table_headers: string = files_with_tables.table_config[table_name].table_header || null;
if (table_headers != null) {
//this is where we clean the table headers string to get only the headers in the correct order
const row_columns: string[] = table_headers.match(/([^"]+?)"\s*:/g).map((elem) => elem.replace('\":', '')).filter((row_column: string) => row_columns_to_ignore.indexOf(row_column) == -1);
for (let i = 0; i < row_columns.length; i++) {
worksheet.getCell(table_start_cell_row - 1, i).setValue(row_columns[i]);
}
let table: ExcelScript.Table = worksheet.getTable(table_id);
if (!!!table) {
const startRange = 'A';
const endRage = String.fromCharCode('A'.charCodeAt(0) + row_columns.length - 1);
const range_address = `A${table_start_cell_row}:${endRage}${table_start_cell_row}`;
table = worksheet.addTable(range_address, true);
table.setName(table_id);
}
else {
there_is_table = true;
}
const table_rows: string[][] = [];
const rows: { [header: string]: string }[] = files_with_tables.table_config[table_name]['table_rows'] || [];
rows.forEach((row: { [header: string]: string }) => {
const holder: string[] = [];
row_columns.forEach((header: string) => {
holder.push(row[header] || "");
});
table_rows.push(holder);
});
if (there_is_table) {
table.getRangeBetweenHeaderAndTotal().setValues(table_rows);
}
else {
table.addRows(-1, table_rows);
}
table_start_cell_row += table_rows.length + 1;
}
});
return 'done';
}This script will run everytime a file on the Suppliers’ folder is change and will copy all the tables into the respective supplier worksheet (based on the file name of the Supplier stock excel). You can then reference the suppliers tables on the Master Stock file excel
Office Scripts in Excel expose a great API, and they allow developers through Javascript (TypeScript) to basically do whatever they need.