cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ea1409
Frequent Visitor

Creation of a table containing all the data from a sharepoint list within an excel sheet in an excel file

As they are, I wanted to ask you if this is possible:

what happens is that I have a sharepoint list that has records, what they asked me is that all these records can be taken from this sharepoint list to a table within an excel document
They ask me if it is possible that in that file a sheet is created with the name of the report and the date, and within the spreadsheet that is created, include a table with all the records that the list contains

after this it must be sent by email to the administrators
I would like to know if this is possible, I could not find much information about it
I know that sharepoitn has a button to be able to export to excel but the spreadsheet remains with a generic name and I would like to do the same but with the aforementioned, is this possible? Any information you can give me in this regard, I will be very grateful, thank you very much 🙂 

1 REPLY 1
DamoBird365
Super User
Super User

Hi @Ea1409 

 

I would say yes it's possible with an office script, albeit I haven't tested this on large datasets.  You would "get items" from your list to return an array and then following this recent article, create a blank excel file. Then this is where you want to write an office script to create a table and accept an array as input.  This will then populate your table in one action and potentially rename the sheet as required.  Save the below script to Excel Online and call it from the run script action in PowerAutomate.  It will accept an array input to populate a table with 4 columns.  The 2nd input is the parameter for your sheet name.  Please let me know how you get on.

 

function main(workbook: ExcelScript.Workbook,
  examplevar4: arrayvar[],   //Multiple rows in Table
  sheetname: string

) {
  let selectedSheet = workbook.getActiveWorksheet();
  
  // Set range A1:D1 on selectedSheet
  selectedSheet.getRange("A1:D1").setValues([["Col1", "Col2", "Col3", "Col4"]]);
  
  // Add a new table at range A1:D1 on selectedSheet
  let newTable = workbook.addTable(selectedSheet.getRange("A1:D1"), true);
  newTable.setName('MyTableName')

  selectedSheet.setName(sheetname);
  
  //Populate rows below Header Row with Array Variable 
  const starterrow = 2; //starting row for "table" data

  for (let i = 0; i < examplevar4.length; i++) {
    const currentObject = examplevar4[i];

    const formattedrow = [[currentObject.Col1, currentObject.Col2, currentObject.Col3, currentObject.Col4]];

    const rowRange = `A${starterrow + i}:D${starterrow + i}`;
    selectedSheet.getRange(rowRange).setValues(formattedrow);
  }
}

//Defining Interfaces for MultiVar and Array

interface arrayvar {
  Col1: string,
  Col2: string,
  Col3: string,
  Col4: string
}

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (2,813)