Upload large quantities of files via PowerApps and into SPO Library

[leonsig93]

Hi all,

As the title states, I'm trying to create an upload interface in PowerApps that would allow a user to select 1000+ files and then add them to a SPO document library I have it working to some extent, the problem is that it cuts off after 11 files. 

I followed the video Shane Young has on the process (and he does indicate this limitation). 

https://www.youtube.com/watch?v=7UeRzfPo8RE

After the 11th file, the JSON input changes completely. It seems that it is not properly converting the base64 for the uploaded file. I tried increasing the MaxAttachments limit on the attachment control, but it seems that only controls how many files you can drop in and has no effect on the actual upload formula itself. The formula on the upload button is the following:

ClearCollect(
    colFiles,
    ShowColumns(
        AddColumns(
            Gallery1.AllItems,
            "base64",
            With(
                {
                    varAttachmentControl: JSON(
                        imgXML_File.Image,
                        JSONFormat.IncludeBinaryData
                    )
                },
                Mid(
                    varAttachmentControl,
                    Find(
                        ",",
                        varAttachmentControl
                    ) + 1,
                    Len(varAttachmentControl) - Find(
                        ",",
                        varAttachmentControl
                    ) - 1
                )
            )
        ),
        "base64",
        "Name"
    )
);
PowerAutomateFlowName.Run(
    JSON(
        colFiles,
        IndentFour
    )
)

Is there a way to increase or alter the JSON functions so that it grabs every file properly?

If not, any suggestions or ideas would be welcome. My last resort would be to send the user directly to the target SPO document library and have them drop the files into the source, but we were trying to limit access to SPO when possible.

Appreciate any feedback and ideas, and thanks for taking the time to read this!

[leonsig93 · Accepted solution]

I got it! So after some mulling it over, lots of trial and error, and a genius idea from my wife. I've figured out how to upload a lot of files.

Step 1: Watch both of these videos

Shane Young's video on how to upload multiple files (10 limit)

https://www.youtube.com/watch?v=7UeRzfPo8RE

R2Power's video on how to set up a While Loop in PowerApps

https://www.youtube.com/watch?v=jQtRdvpF9F0

Once you've watched those two the rest of this makes sense.

The short version is that I ended up creating a staging collection so I can cycle through the files in batches of 10 and remove them. Then, once all the files are prepped, you run your flow so the files are actually created.

Alright the actual steps. Note that you don't have to follow the same naming conventions I do. I list out the names in the steps so its easier to follow

You will need:

• Everything in Shane's video
• Another button
• A toggle
• A variable (gvarStartLoop)
• Another collection (colStagedFiles)

1. Again, do everything in Shane's video first

2. For your new button, name it btnStageFiles and set the OnSelect to the following.

ClearCollect(
    colStagedFiles,
    attachmentControlName.Attachments
);
Set(gvarStartLoop, true)

3. Change the Items property of the gallery to your new collection and sort it by a column. In my case I just used "Name" as order didn't matter. We just want every instance of the files to be in the same order so we grab all files without fail. Whatever you pick, you will need to add this everytime you refer to this collection.

SortByColumns(colStagedFiles, "Name", Descending)

4. Rename Shane's button to btnUploadFiles and change the formula slightly by adding FirstN 10 to the gallery reference as well as the SortByColumn you added before. And the extra stuff after it. Should look like this:

Collect(
    colFiles,
    ShowColumns(
        AddColumns(
            FirstN(
                SortByColumns(
                    galStagedFiles.AllItems,
                    "Name",
                    Descending
                ),
                10
            ),
            "base64",
            With(
                {
                    varAttachmentControl: JSON(
                        imgXML_File_SG.Image,
                        JSONFormat.IncludeBinaryData
                    )
                },
                Mid(
                    varAttachmentControl,
                    Find(
                        ",",
                        varAttachmentControl
                    ) + 1,
                    Len(varAttachmentControl) - Find(
                        ",",
                        varAttachmentControl
                    ) - 1
                )
            )
        ),
        "base64",
        "Name"
    )
);
Remove(colStagedFiles, FirstN(SortByColumns(colStagedFiles, "Name", Descending), 10));
Set(gvarStartLoop, true)

(imgXML_File_SG si the image control in the gallery)

Note that you have to add your SortByColumns here

5. For your toggle, set the Default property to just your variable (gvarStartLoop)

6. Set the OnCheck to the following:

If(
    CountRows(galStagedFiles.AllItems) <> 0,
    Set(
        gvarStartLoop,
        false
    );
    Select(btnUploadFiles),
    Set(
        gvarStartLoop,
        false
    );
    'YourFlowName'.Run(
        JSON(
            colFiles,
            IndentFour
        )
    )
)

(galStagedFiles is what I renamed the gallery to)

What your end user will be doing is dropping their files into the attachment control then clicking your new button instead of Shane's. The new button will then create the staging collection and cause the toggle to check itself. Then the OnCheck will trigger which will go ahead and click on Shane's button while simultaneously unchecking itself. Shane's modified button will then grab the first 10 items from the staging collection and transform them, then immediately remove those same 10 from the staging collection and check the toggle again. The toggle will always check to see if there are any files left, and once it finds there are none, it will end the loop.

Set-up wise you set the Visible property to false for the gallery, Shane's button, and the toggle. Your end-user only needs to click on the new button (btnStageFiles). I've tested it with 50+ files so far and its been working fantastically.

Please note I'm testing this with XML files. Haven't tried other things.

Shout out to Shane Young, R2Power, and my wife!

Hopefully one day, we can just have a straight forward upload process. For now, this seems to be doing the trick!

[v-qiaqi-msft]

Hi @leonsig93.,

Thanks for your sharing.