Forum Discussion
Copy excel data to SharePoint List using Power Automate
Hello All,
I am facing challenges in copying excel data (exist is SharePoint library) to a SharePoint list. I found few articles where it was recommended to create table inside excel to copy, but I am trying to copy each row data without creating table in excel. Is there way to achive solution for this using Microsoft flow? when a new excel file is uploaded to Libarary
Raj-Microsoft-365-Dev you must format the rows in Excel as a table, there are no actions that allow you to do it without the data being in a table.
Rob
Los Gallardos
Microsoft Power Automate Community Super UserHi Raj-Microsoft-365-Dev , you can use Power Automate to format your spreadsheet tables before the import phase. You need to have an online script available in your account (or the account running the automation). Here is an example of a script which I have used to do precisely what you're wanting to do.
function main(workbook: ExcelScript.Workbook) {// Select the active sheetlet selectedSheet = workbook.getActiveWorksheet();// Create a table with format on range A1:H5000 on selectedSheetlet table1 = workbook.addTable(selectedSheet.getRange("A1:H5000"), true);table1.setPredefinedTableStyle("TableStyleLight14");// Rename table table1 to "data"table1.setName("data");// Rename worksheet to "data"selectedSheet.setName("data");}I believe that, with the Graph API call such as the ones below, you are able to select data within the defined range. The example below points to a document in OneDrive, but also a document on SharePoint can be used (with small adjustments to the API call).
https://graph.microsoft.com/v1.0/me/drive/items/{{ITEM_ID}}/workbook/worksheets/{{worksheet_id}}/range(address='A1:A15')https://graph.microsoft.com/v1.0/sites/{{SITE_ID}}/lists/{{LIST_ID}}/items/{{ITEM_ID}}/driveitem/workbook/worksheets/{{worksheet_id}}/range(address='A1:A15')
