Automation Help - Create PDF from template and add link to view / print it in a virtual column

[R_J]

Hi,

I'm new to AppSheet, just 8 days in, and I'm struggling with creating a workflow that automatically generates PDFs for each row of data in my table and then display a link to view / print the PDF in a virtual column. I know questions like this have been asked frequently, and I've read through numerous threads, but most lack clarity, making it difficult to follow the steps outlined. 

Could someone please provide clear steps of the process I need to implement in order to obtain my desired results? Below is a sample of what my data looks like, with a few columns omitted for security reasons.

[AleksiAlkio]

You don't need to use virtual column, just a normal File column is fine.
#1 - Generate the PDF with the data change (this part you have probably done already)
#2 - Set the PDF file name as unique like [KeyColumn].pdf
#3 - Specify the folder where files are saved. Let's assume it is saved to Root > AppSheet > Data > App-1234567 > Files, and the main spreadsheet is in App-1234567 folder.
#4 - Now you can write the file name to your file column when the record was created like "/Files/"&[KeyColumn]&".pdf". Remember the path needs to start from the folder where the main spreadsheet is.
#5 - If it's possible that you will need to modify the record (and the PDF) later, you need to use some kind of versioning with the file name. Otherwise it will find the original file from the cache when trying to view the PDF.

[peterson · Accepted solution]

Hello my dear, I have a different opinion about how you can achieve this. 
I have been doing that for a long time so here are my considerations:

Part 1 - App folder management

You'll need to see where your files are saved in your google drive (you can do this just by going to settings>Information and in the app property section you'll see the folder path)

I changed the default app folder and have in mind that they are always separated by "/". If you have /appsheet/appname123/Files - it means that every name after the slash symbol "/" is you entering in a new folder.

Part 2 - Automation settings required
Once you create your document via automation you have to pay attention to:
1. File folder path
2. File name
3. Disable timestamp
See image for reference:

You'll need to disable timestamp in order to make it work.

I set the file path to the folder I want. You don't need to create the folder because when you write the path, in case the subfolders don't exist the app will create them automatically.

In this process I recommend you have your drive open to check if you files are being saved properly where you want. Also, have your google sheet open because this ways you'll see the right path writen in the File cell.

Part 3 - How to open your file in the app

In this part you'll simply need to create a virtual column and in the expression box you need to write the whole file path. See image below:

I firmly recommend you to copy and past the file path and name of the file in the automation workflow. Don't forget to use "&" to concatenate the pieces of information and &".pdf" on the contrary it is not going to work.

PS: If you want you can create an action to open the file and set the virtual column as not show. This last part is just a personal preference.

Please let me know if you were able to do that.
Regards

[AleksiAlkio]

"In this part you'll simply need to create a virtual column".. Why do you want to create a virtual column as it needs to calculate it again and again every time when the app syncs without any reason??

And.. when you create the File column with the file name, at that point it doesn't have anything to do with the default folder anymore. It is only used where the Bot saves the file (unless it's specified in the Bot).

[peterson]

When I tried to open the file through an existing column in my sheet I wasn't able.
It used to return an error, I don't know why. I'll try later if it now opens. 

But I didn't understand your point. You're adivising to use a formula in the file column with the file path? Is that it?

Because if not, how will he open the files that Appsheet creates itself? Is there a way to store this file in a column instead of only saving it on the folder?

[AleksiAlkio]

The file the Bot creates is the same than when you save an image with the app. The file column works with the same way. The app formula can write the path and file name to a column.  Thats why you can write the path and file name to a file column and it opens the file from the Drive. Though you need to wait a little after the Bot is triggered until the file is saved to Drive. Normally 30..60 secs is enough.

And because it's a file column, the path needs to start from the folder where the main spreadsheet is. The same with images.

[peterson]

Sure thing! I know that. But just like I said it wasn't working for me, it only worked when I set the file path in one virtual column. Plus, the app has already more then 1200 rows and I haven't experiences any lag or sync delay. So far so good.

But I'll try to use file column in my sheet next time and see if it works.

[R_J]

Thanks to @AleksiAlkio  and @peterson! I tested both of your recommended solutions, and each of them worked great. I was able to generate a custom PDF quickly using either solution. I learned a lot and can easily replicate both of your steps from memory.

However, I'm going to mark Peterson's recommended steps as the solution, and I'll explain why. My app's data is generated from a Salesforce report, so using a Virtual Column to house the PDF file actually worked best for this particular kind of app that I'm building.

Again, thank you both for taking the time out of your day to help beginners like me get started in AppSheet. Please don't ever stop doing that! 👍

[AleksiAlkio]

Good to hear you were able to solve this!