Power Pages: Dynamically Generating and Uploading Excel Data to Dataverse

In this blog post, I'll explain how to upload a file from Power Pages to SharePoint, read its contents, and insert the data into a Dataverse table. This method enables seamless data transfer from a file uploaded via Power Pages to Dataverse, facilitating more efficient data management.

?

This approach helps Power Platform developers bypass the limitations associated with connecting to SharePoint Document Libraries or other file repositories. It eliminates the need to configure SharePoint Document Integration with Dataverse, streamlining the process and saving valuable development time.

?

To address this, we will use JavaScript code to upload files to the document library.

?

The document transfer process will be: Source (Power Pages External Page) -> Power Automate -> Destination (SharePoint Document Library).

?

First, set up the SharePoint Document Library, either by using an existing library or creating a new one.

?

Next, create a Power Automate Cloud flow to capture and save the file to the SharePoint Document Library.

The flow starts with the trigger "When an HTTP Request is received" from the "Request" connector, which is initiated by the external page.

Use this script to initiate the flow.

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>
  function uploadDocument() {
      debugger;
      var files = document.getElementById('file_upload').files;
      // Show error if no file is chosen
      if (files.length === 0) {
          alert("Please choose Excel file...");
          return;
      }
      // Extract the file content
      var file = files[0];
      // Extract the file name
      var filename = files[0].name;
      // Check if the file is an Excel file
      var allowedExtensions = /(\.xls|\.xlsx)$/i;
      if (!allowedExtensions.exec(filename)) {
          alert("Please upload a valid Excel file.");
          return;
      }
      // Create form data for passing file content to flow
      var form = new FormData();
      // Append properties to the form for sending data to cloud flow
      form.append("", file, "");
      form.append("filename", filename);
      // AJAX call to Cloud Flow
      $.ajax({
          method: "POST",
          // Cloud Flow URL. The URL to be copied from cloud flow trigger.
          url: 'https://prod-134.westus.logic.azure.com:443/workflows/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,
          timeout: 0,
          processData: false,
          // Important parameter
          mimeType: "multipart/form-data",
          contentType: false,
          data: form,
          success: function (result) {
              // Checks if the document upload was a success
              if (result == "Uploaded") {
                  // Show initial message and loader
                  $('#uploadsuccess').html("Uploaded Successfully");
                  
          error: function (result, status) {
              // Shows error in case of failure
              alert("There is an issue encountered while uploading the file.");
          }
      });
  }
</script>
        

Add rows to the Dataverse table when a file is uploaded to the SharePoint Library.

Conclusion

In this blog post, we've explored how to streamline the process of uploading files from Power Pages to SharePoint, then transferring and mapping that data into a Dataverse table. By setting up a SharePoint Document Library, configuring a Power Automate Cloud flow, and using JavaScript for file uploads, you can efficiently manage file data across different platforms.

?

This approach not only simplifies the integration between Power Pages, SharePoint, and Dataverse but also bypasses the need for complex configurations or additional integrations. With these steps, you'll be able to automate data transfers and enhance your Power Platform solutions, saving time and increasing productivity.