Interacting with Data from Excel without a table

[ahall]

I have an excel file that is emailed to me that I want to add into a SQL database. My issue is the excel file is not configured with a table. How can I get these rows into a table format using flow?

I see the create table function from excel but that creates and empty table with the column headings I choose. Is it possible to create the table with data?

I can get my whole process to work if I start with a file that has a table in it, but my emailed report is not possible to be created with the table from the source so I have to automate that somehow.

[majorfriend]

@CSteinhilber 

You might want to try creating the script yourself as there could be some variables in your Excel document that are keeping it from working.

It isn't too difficult to do. 
1. Open document in Excel online
2. Click Automate tab

3. Click record
4. Record your actions to create the table

5. Save and test your script

[Golly]

Thanks to Anonymous and @nich3play3r  for the script code!  One thing to note with the error correction, it will only return true if the table name is Table1. I did this slightly differently because I needed to format existing data as a table without knowing how many rows/columns there were.

// This script formats all used cells into a table and returns the name or, 
// if a table already exists, returns the name of the existing table

function main(workbook: ExcelScript.Workbook) {
 
  let targetTable = workbook.getTable("Table1");
  if (!targetTable) {

    // Get the active worksheet.
    let sheet = workbook.getActiveWorksheet();

    // Get the entire data range.
    let usedRange = sheet.getUsedRange(true);

    // Create a table using the data range.
    let newTable = workbook.addTable(usedRange, true);
    return targetTable.getName();
  } else {
    return targetTable.getName();
  }	
}

[virakones]

Hi,

Just wanted to say these scripts have been wonderful to get to the data in the excel sheet.

I've gotten data now in the output of a "List Rows present in a table" action -- any suggestions on returning the whole dataset back to powerapps?

[Golly]

Unfortunately, the Respond to Powerapps action does not have an array datatype

but you could try sending the result back as a text string and store it in a collection in PowerApps or push the data into a SharePoint list where the PowerApp could access it more easily.

[CSteinhilber]

You should be able to stringify the dataset:

function main(workbook: ExcelScript.Workbook) {
     :
    let finalRange = selectedSheet.getRange(`A6:N${lastRow}`);
    let myDataset = JSON.stringify(finalRange.getTexts());
    return myOutput; // return the stringified JSON
}

Then use the Parse JSON step to turn it back into an actionable dataset to loop through:

I'm sure there's a limit to how big the returned dataset can be, but I haven't hit it yet and I've returned several thousand rows.

[JasonWS]

Sorry for reviving this thread, but this is amazing. Wanted to say thanks. I also wanted to offer one slight correction. If targetTable doesn't exist, the newly created table is not stored in targetTable, resulting in the first return function failing. I changed it to this, and it works:

// This script formats all used cells into a table and returns the name or, 
// if a table already exists, returns the name of the existing table

function main(workbook: ExcelScript.Workbook) {
 
  let targetTable = workbook.getTable("Table1");
  if (!targetTable) {

    // Get the active worksheet.
    let sheet = workbook.getActiveWorksheet();

    // Get the entire data range.
    let usedRange = sheet.getUsedRange(true);

    // Create a table using the data range.
    let newTable = workbook.addTable(usedRange, true);
    // Following line changed
    return newTable.getName();
  } else {
    return targetTable.getName();
  }	
}

[eliotcole]

No scripts and finanglings are necessary, fwiw, there is a graph endpoint which solves it all:

GET UsedRange

Once you have that in play you can then work with the data, and / or even create a table to then use the standard actions with. 👍

I believe I've used it in solutions before, but it was so long ago, so I encourage folks to delve a little further. It's not going to be plain sailing ... but it's there. 🙂

EDIT

OK, I actually just cracked the exact way to get the SharePoint connector to do this by simply plonking in the graph domain in the 'site' section. 😅

Check out my solution to the 'Delete all worksheet except one specific sheet' question here.

The only thing that you would change in the call (apart from any circular references) would be the endpoint, whereby you would simple add a '/usedRange' to the end of the Uri. 🏆