Insert .png file in MSFT Word template breaking at Create File step

[tagustin2020]

@WarrenBelz 

Hello Warren,

I hope you are doing well. I'm tagging you on this post since I'm attempting to use a technique you outlined in your helpful blog on how to work with images. PowerApps SharePoint Images – Storing and viewing – Practical Power Apps

Looks like this post has had 60+ views, but no one has responded yet so I'd be very grateful if you could take a look. You've helped me many times before in the Power Apps forum. I'm so close to getting the QR Code image embedded into my Word doc. Just need a little help getting over this hurdle. I'll be working all day tomorrow (Saturday PST) trying to wrap up my app and flows so if you respond I'll see your message. Thank you!

I am generating a QR Code that provides a deep link to the record in Power Apps. I have been able to accomplish that. The next step is to embed the image in a Microsoft Word doc template that can be converted to a PDF, printed on a sticker label that contains other record data and adhered to a box. I'm stuck at the Create File in SharePoint (for the .png file) in my flow. Haven't done it before...I've tried both using base64ToBinary and dataUriToBinary expressions, but I keep getting the error that I need to have a string rather than an object in order for this to work.

This is an automated cloud flow. I am not calling the flow from Power Apps as I am using a premium connector (MS Word) and don't want to turn my app into a premium app (currently a Standard license). The trigger for the flow is 'When an item or a file is modified'. I have a condition that checks if the 'Generate PDF Label' flow is supposed to run. If it does, the flow does a 'Get item'. The flow steps that pertain to the image steps can be seen below. If someone could let me know what I need to do at the SharePoint 'Create file' step (and/or right before it), I would be grateful. If you see any obvious errors in the steps that fall below the 'Create file' step, I would appreciate if you could let me know that as well. Thanks!

Step 1 - Compose

The QR code is stored as multiline text in my SharePoint list so I added a Compose step to replace the quotation marks with an empty string.

 

Step 2 - Upload file from URL

This is how I hope to convert the multiline text to a png file. I can upload to SharePoint.

Step 3 - Get the file content from OneDrive.

The outputs are detailed below.

Step 4 - Create a QR code file in SharePoint.

Do I have to end the File Name with a file extension of .png? I've tried it both with and without the .png and the flow breaks as this step.

Step 5 - Update file properties

Is DisplayName okay to use for the Title? I wasn't sure what to put there...

Step 6 - Get file content using path

If you could verify whether or not this looks correct, I would appreciate it.

Step 7 - Populate a MS Word template

If you could confirm that I have the correct dynamic content plugged in, I would appreciate it. If you can help me get this far in the flow, I should be able to get the rest as I have written flows to convert Word doc templates into PDFs before, attached them to the SharePoint list, etc. I just haven't had to insert a dynamic image yet. Thanks again for the time you are taking to help me. I really appreciate it! Teresa

[WarrenBelz]

Hi @tagustin2020 ,

A couple of things here that are relevant. Firstly, you need to store the QR code in your multi-line text field with the "headers" removed

With(
   {
      wJSON: 
      JSON(
         YourImageControlName.Image,
         JSONFormat.IncludeBinaryData
      )
   },
    Mid(
      wJSON,
      Find(
         ",",
         wJSON
      ) + 1,
      Len(wJSON) - 
      Find(
         ",",
         wJSON
      ) - 1
   )
)

then you can use base64ToBinary to get it into an image format, however there is one more step I have found necessary with Word merging Images  - you need to create a temporary jpg file (I use OneDrive) using the base64ToBinary (TempFile.jpg or whatever) and then get the content (Get file content using path) and use that in the Word Merge field.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[tagustin2020]

@WarrenBelz 

Hello Warren, 

Thank you for the quick response. My QR code is working properly with the following method in Power Apps. The deep link is going to the correct record. It is just the conversion steps I'm having trouble with. Here are the steps starting with the Power Apps form. I don't mean to be rude in not using your With formula, but I wasn't sure where to put it since my Image property contains a QR code url.

Image:

"https://quickchart.io/qr?text="& EncodeUrl("https://apps.powerapps.com/play/e/default-xxxx?tenantId=xxxx&hidenavbar=true&DeepLink=AdminScreen&RefID=" & varRecord.ID & ".png")

The OnSelect of my "Create PDF" button is:

Set(varBlob, JSON(Image4.Image, IncludeBinaryData));
Patch(
    'CSO Archive Requests',
    {
         ID: varRecord.ID,
        'Generate PDF': true,
        'PDF Version': Value(valPDFVersion.Text) + 1,
        'PDF Email': varUser.Email,
        QRCodeML: varBlob
    }
);
Notify("A box label will be sent to your email and attached to the record shortly.", Success)

The Data Card formulas are:

Default: ThisItem.QRCodeML

Update: varBlob

I'm removing the quotation marks with the Compose step.

As I alluded to before, this gives me a QR code that works fine in an email, but where I am hitting a snag is converting it to an image file that can be used on the box label. 

You know I appreciate your advice, but I'm still so green when it comes to Power Automate I need step by step guidance. I've gotten a little confused as to the order of what comes next. I was having the next step be "Upload file from URL", but I got rid of that when I read your response and put in a OneDrive Create file step instead. The Outputs are coming from the previous Compose step. As you saw from the Image property in the Power App, the file extension is .png. Do I need to add a file extension in the Create file step as well? The flow breaks if I try to put in base64tobinary code as shown in my original post.

I deleted my 'Get file content' step and put in a 'Get file content using path' step per your note.

I then insert the Body of the Get file content using path step. 

As you might have guessed, the QR code image is not coming through.

Would you mind telling me exactly what I need to do with each of these steps? I'm sure I have it all messed up. I'm sorry I wasn't able to discern what you were trying to share. It's 10pm here now so I'm going to "hit the hay" for now. Thanks for sticking with me!

Teresa

[WarrenBelz]

Hi @tagustin2020 ,

The fundamental issue is here

With(
   {
      wJSON: 
      JSON(
         Image4.Image,
         JSONFormat.IncludeBinaryData
      )
   },
   Set(
      varBlob,
      Mid(
         wJSON,
         Find(
            ",",
            wJSON
         ) + 1,
         Len(wJSON) - 
         Find(
            ",",
            wJSON
         ) - 1
      )
   )
)

You need a "raw" base64 format if you want to use base64ToBinary on it to get it back into an image - removing the leading and trailing quotes (as you are doing) allows the code to be resolved in an Image Control in Power Apps, but not to be converted to a file in a Flow.
I also tend to use jpg as the extension of the file in OneDrive as I know this works with Word. I use this method on hundreds of forms monthly with signatures and it works fine (except I send the code - your varBlob -  directly to the Flow rather than get it out of a SharePoint field.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[tagustin2020]

@WarrenBelz 

Hello Warren,

Thank you for the response. I really appreciate it. Apologies in advance for the length of this post, but I really want to learn your technique and want to make it as easy as possible for you to spot what I am doing wrong right away out of respect for your time.

Per your advice, I changed the URL in my Image control from a .png to a .jpg extension.

"https://quickchart.io/qr?text="& EncodeUrl("https://apps.powerapps.com/play/e/default-xxxxx?tenantId=xxxx&hidenavbar=true&DeepLink=AdminScreen&RefID=" & varRecord.ID & ".jpg")

OnSelect formula of my 'Create Box Label PDF' button to the formula you gave me. There are no error/red lines.

With(
   {
      wJSON: 
      JSON(
         Image4.Image,
         JSONFormat.IncludeBinaryData
      )
   },
   Set(
      varBlob,
      Mid(
         wJSON,
         Find(
            ",",
            wJSON
         ) + 1,
         Len(wJSON) - 
         Find(
            ",",
            wJSON
         ) - 1
      )
   )
);
Patch(
    'CSO Archive Requests',
    {
         ID: varRecord.ID,
        'Generate PDF': true,
        'PDF Version': Value(valPDFVersion.Text) + 1,
        'PDF Email': varUser.Email,
        QRCodeML: varBlob
    }
);
Notify("A box label will be sent to your email and attached to the record shortly.", Success)

The Default and Update properties of the data card are still set to:

Default: ThisItem.QRCodeML
Update: varBlob

My flow trigger is still 'When an item or a file is modified' since I don't want my app to be converted to a Premium license app. I really don't need to store the .jpg files in a SharePoint doc library for all posterity so I just added a OneDrive 'Create file' step.

When I run the flow, it breaks at the Create file step. It says it cannot be evaluated because 'Createfile_FileContent' doesn't exist and then goes on to give a list of things I can choose from. QRCodeML is the name of my multi-line text column.

When I added a 'Get file content using path' step. File content wasn't one of the dynamic content options to choose from. File locator and Path were the 2 closest options. I'm doubtful this step will work, but it is untested since the flow breaks at the Create file step.

When this approach didn't work out, I went back to your blog to follow the steps for uploading an image to a SharePoint doc library calling the flow from SharePoint. It all seemed to go pretty smoothly until I inserted a new button in my app to call the flow from. This is the formula for the OnSelect. What am I doing wrong?

Here are the steps I constructed based on your very detailed blog post (thank you). I didn't add any Word doc steps as, again, I don't want to risk turning my app into a premium license app, but I thought I could at least experiment to see if I could get the QR code converted into a usable file that could later be inserted into the Word doc flow by pulling it down from the SharePoint doc library location via a "ParentID" tie in. I added a number type column to my doc library named "ParentID". Can you let me know how I could pass the ID from the Power Apps record I am into the SharePoint doc library? Seems like it would have to be passed in the flow run, versus a Patch statement that falls after the flow run, but I'm not sure how to add that in.

I noticed a small discrepancy between this step and the step shown in your blog post. Mine reads Title and Description. Your screenshot reads Title and IDRef. Not sure if that is significant or not, but thought I would mention it.

Add attachment Id expression.

Add attachment File Name expression.

Technically, I don't really need to add the attachment to the record, but I am trying to learn from your post as I would really like to nail down the technique for future projects. Please let me know if I got any of these steps wrong. I really appreciate your help, Warren. Thank you so much for your patience.

Teresa

[WarrenBelz]

HI @tagustin2020 ,

I assumed you had a captured image in there when you mentioned QR Code - if this is simply a rendered URL then all JSON is going to do (check out varBlob content) is capture the URL parameters, which is not the image you want. The only way I know of converting a URL image to an actual SharePoint usable image is using the Upload file from URL component of OneDrive for Business in Power Automate as below (with a test chart)

which means (hopefully) all you have to store in your SharePoint list is the URL that resolves the QR code.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[WarrenBelz]

Hi @tagustin2020 ,

Further testing of the OneDrive upload OneDrive thinks a URL with parameters is dangerous and it refuses to do it

So time to get creative . . . I have tested this and it works - put your image control inside a Container (I will call it Container1 here) and do this

With(
   {
      wBlob:
      With(
         {
           wJSON: 
           JSON(
              PDF(Container1),
              JSONFormat.IncludeBinaryData
           )
         },
         Mid(
            wJSON,
            Find(
              ",",
              wJSON
            ) + 1,
            Len(wJSON) - 
            Find(
               ",",
               wJSON
            ) - 1
         )
      )
   },
   Patch(
      'CSO Archive Requests',
      {
         ID: varRecord.ID,
         'Generate PDF': true,
         'PDF Version': Value(valPDFVersion.Text) + 1,
         'PDF Email': varUser.Email,
         QRCodeML: wBlob
      }
   )
);

Also ensure you turn on your PDF function if you have not already done that

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[tagustin2020]

@WarrenBelz 

Hello Warren,

Thank you for the time and effort you are putting into helping me. I'm getting an error message with the Container so I'm wondering if you can spot what I'm doing wrong.

Here is the error message:

Here is my tree view. I put the image control inside the container and did not change the name of the container.

I turned on the PDF experimental feature and pasted in the formula you gave me with no changes. I noticed you did away with varBlob which is what is in the Update property of my data card. What should I put there now?

With(
   {
      wBlob:
      With(
         {
           wJSON: 
           JSON(
              PDF(Container1),
              JSONFormat.IncludeBinaryData
           )
         },
         Mid(
            wJSON,
            Find(
              ",",
              wJSON
            ) + 1,
            Len(wJSON) - 
            Find(
               ",",
               wJSON
            ) - 1
         )
      )
   },
   Patch(
      'CSO Archive Requests',
      {
         ID: varRecord.ID,
         'Generate PDF': true,
         'PDF Version': Value(valPDFVersion.Text) + 1,
         'PDF Email': varUser.Email,
         QRCodeML: wBlob
      }
   )
)

Thanks,

Teresa

[WarrenBelz]

Hi @tagustin2020 ,

You do not need an update as you are patching it, but you can simply make the Default of the Text Control

ThisItem.QRCodeML

and then the Update

TextControlName.Text

however, what has me puzzled it the error on the container name - below is the one I did with my test list, which has the same field types as yours and I also used the same Variable and control names

and this tested as expected and patched a valid Base64 string into my multi-line text field. I can see however that you have the Container inside a Data Card - you might try it outside.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[tagustin2020]

@WarrenBelz 

Hello Warren,

Maybe this is the difference between your test and mine. While my SharePoint field is a multi-line text field, I don't have a text field in my datacard, only an image control. I deleted the Container and image control from the Form control and inserted a new container on the screen outside of the form which I renamed "ContainerQR" and then I inserted an image control inside of it.

The Image formula is:

"https://quickchart.io/qr?text="& EncodeUrl("https://apps.powerapps.com/play/e/default-0f634ac3-b39f-41a6-83ba-8f107876c692/a/16df3cf4-5af7-4e1c-aa13-2e72f92d9680?tenantId=0f634ac3-b39f-41a6-83ba-8f107876c692&hidenavbar=true&DeepLink=AdminScreen&RefID=" & varRecord.ID & ".jpg")

The OnSelect of the button is:

With(
   {
      wBlob:
      With(
         {
           wJSON: 
           JSON(
              PDF(ContainerQR),
              JSONFormat.IncludeBinaryData
           )
         },
         Mid(
            wJSON,
            Find(
              ",",
              wJSON
            ) + 1,
            Len(wJSON) - 
            Find(
               ",",
               wJSON
            ) - 1
         )
      )
   },
   Patch(
      'CSO Archive Requests',
      {
         ID: varRecord.ID,
         'Generate PDF': true,
         'PDF Version': Value(valPDFVersion.Text) + 1,
         'PDF Email': varUser.Email,
         QRCodeML: wBlob
      }
   )
)

The text that is written back to the QRCodeML SharePoint column is massive. I copy/pasted it into a MS Word doc and it was 30 pages long with hundreds of ////////// so that can't be right.

I then went into my flow and inserted an 'Upload file from URL' step per your earlier post. When I got an error at this step I tried entering a forward slash before QRCode.jpg

In either case I get the same error message, "The provided workflow action input is not valid."

Here are the other steps in the flow if we can get past the "Upload file" error. I added a 'Get file content using path' step per your earlier post.

I inserted the File content into the Word step.

Warren, thanks again so much for your patient help. I wanted to let you know that I'll be taking the rest of Sunday off to be with my husband. His mother had a small heart attack and mine fell and hurt herself. He was out-of-state last week taking care of his mom (she's back home from the hospital and doing well), while I stayed home to take care of mine (my mother lives with us). We've been apart for a week so this is our first chance to be together for a while. I'll keep an eye out for your responses, but let me know if you need to take a breather too. Take care and God bless you for all you do!

Kind regards,

Teresa

[WarrenBelz · Accepted solution]

Hi @tagustin2020 ,

Bad news I am afraid - I got what appeared to be valid Base64 code using the container and followed it all the way through to the Word Merge and it worked fine, except all I got was the outline of the container (no picture inside). It appears that Image Controls containing a URL-resolved images simply do not exist as image content in any Power Apps function trying to capture them. On reflection, this makes a bit of sense, but that does not really help you. I will tag one of the Flow gurus @Pstork1 to see if he has any idea here.

[tagustin2020]

@WarrenBelz 

Thank you for letting me know Warren. If Paul doesn't know a workaround for this, I was thinking I might be able to take a 2-step approach, creating a PDF using the Word Doc flow approach to achieve the look and margins that I need (a snapshot of my Word doc template is below) and then running a separate OneDrive flow utilizing CSS/HTML with 2 divs side-by-side for the year/color-coded rectangle to the left and QR Code to the right. The idea is that the color-coded year (the requestor has a 10 color coding system based on the last digit of the archive year) and QR Code would be printed on Avery labels and stuck onto the PDF which will also be printed onto Avery labels and adhered to each side of the Archive box. As you know, the MS Word premium connector does not yet support rich text controls so it wasn't going to be possible for me to pass a background color/year via Word, whereas I can do it using html. I'm hoping to give this approach a try tomorrow (Tuesday). I'll let you know how it goes. I'm still curious to see if Paul knows of a workaround for QR codes since I may get future requests that don't involve the double-challenge of adding background colors as well. Thanks again for all your help! Teresa

[tagustin2020]

@WarrenBelz 

Hello Warren,

Just a note to let you know that I haven't had a chance to work on this yet due to other work responsibilities. I will post an update as soon as I have a chance to work on it. Have a great day! 

Teresa

[tagustin2020 · Accepted solution]

@WarrenBelz 

Hello Warren,

Apologies for the delay in getting back to you. First off, I thank you so much for all the time you spent trying to help me figure out how to get deep link QR codes working in either OneDrive or MSFT Word. Per my last update, I spent more cycles trying to create a PDF based on CSS/HTML to generate labels for just the color-coded year requirement and QR code. I tried leveraging the CSS/HTML contained in this blog post on how to print barcodes on Avery 5160 Address labels, but was unable to get it to work for my use case. The CSS/HTML worked fine as viewed in the W3 Schools interface, but did not work once pasted into a Compose statement and run through OneDrive steps in Power Automate. Because the deadline for this project is fast approaching, I ended up inserting the QR codes into the email step and I printed up a bunch of Avery 5160 address labels with the color-coded year labels. It is a disappointing, embarrassing work-around, but I'm not a developer and have already spent so many weeks trying to figure out how to do it "right" that I finally had to tap out since the project is due shortly. I'm hoping that at the very least, Microsoft will enable rich text html in the premium Microsoft Word steps so I can pass the hex codes for the colored label backgrounds so it is no longer necessary to print Avery labels for the years.

I added placeholders to the box label so it is clear where the Year and QR Code labels need to be adhered. The QR codes do work when scanned with a handheld 2D barcode scanner. Taping over the QR codes with either scotch tape or packing tape does not adversely impact the scan. The QR Codes do launch the deep link to the record as intended.

In regards to Power App and flow steps, I did end up sticking with the multiline text SharePoint column approach for capturing/storing the QR code data and using the replace expression to replace the double quotes with empty strings. I then composed an image tag that could be inserted into the final email step.

Thanks again Warren for your dedication to the Community and for helping me gain a better understanding of what my options were. I hope you have a wonderful day.

Kind regards,

Teresa

[WarrenBelz]

Hi @tagustin2020 ,

Glad you got it sorted