FILE OPENING - MANAGEMENT

[jaichith]

Dear Friends,

I have created an bot (named : PDF Report) for creating a PDF File. File is generating properly and as usual it is creates or updates in google drive. 

through a setup in bot,  I am setting the file path for opening the file (as shown in this video  by @MultiTech matt). while clicking the file open button which is in the inline of [Report File] 

The output is 

I don't know where I have made an mistake. please help me how to fix this issue. @MultiTech 

Note : I am using default folder path. 

[MultiTech · Accepted solution]

@jaichith I see where you're running into problems:

---

@jaichith wrote:

---

1. When you specify the file name, do not specify the file extension (.pdf)
2. When writing the filepath to your database, include the file extension there.

[hapham · Accepted solution]

Try tunrn of option: Require Image and File URL Signing

[AleksiAlkio · Accepted solution]

#1 - Move your gSheet to Root > appsheet > data > Courierapp-6502531
#2 - Move your existing file to Root > appsheet > data > Courierapp-6502531 > Files
#3 - In your gSheet, change the path + file name for that record to Files/<PII removed by staff>.pdf

[MultiTech · Accepted solution]

@jaichith I see where you're running into problems:

---

@jaichith wrote:

---

1. When you specify the file name, do not specify the file extension (.pdf)
2. When writing the filepath to your database, include the file extension there.

[jaichith]

Please check this 

[hapham · Accepted solution]

Try tunrn of option: Require Image and File URL Signing

[jaichith]

Where is that option sir?

[jaichith]

1. When you specify the file name, do not specify the file extension (.pdf)Do you mean in action " SET FILE LINK "?

1. When writing the filepath to your database, include the file extension there.    I don't have any specific folder, I am just using default one .

[jaichith]

INSTRUCTION 1 AND 2 COMPLETED. STILL FILE IS NOT OPENING. 

[jaichith]

@MultiTech , @WillowMobileSys @Suvrutt_Gurjar @SkrOYC @Rifad 

still I could not fix this issue and it is pending for 4 days. I request you to help whenever time is available. 

[Rifad]

Give it a go - create a new column for a file type and upload a file. Take a look at how the file path appears. Simply copy and paste that file path, then combine it with the PDF file name that's generated in here.

---

@jaichith wrote:

---

Make sure to double-check that the relative folder path in your automation is correctly configured. This could be the simplest way to pinpoint where things might be going awry.

This might be helpful  What is the best way to retrieve and view files in... - Google Cloud Community  

[AleksiAlkio]

In general.. if the file is not found,  either the path or the file name is wrong. When the Bot generates the file, it reads the path from the app's default folder if it's not specified differently in the Bot. When writing the file name to a data source (file column), at that moment the default folder option doesn't affect anymore. It's only used when the file itsefl was generated.

That's why you need to specify the whole path starting from the folder where the gSheet is stored. This same goes to any file or image column. if the gSheet is in Root > appsheet > data > AppName-1234567 folder, then just the file name is needed. But if it's in the subfolder like Root > appsheet > data > AppName-1234567 > Files, you need to write it like "Files/filename.pdf" with the app formula.

[jaichith]

Dear Friends, 

 I have taken clone app of the appsheet. So that there are 2 apps

1. Original       2.  clone

Findings

App Original does not opens any file brings the same errors . 

Googlesheets are in -------MYDRIVE/ORIGINAL (created by me)

PDFs created by the app ORIGINAL  is stored in MYDRIVE/APPSHEET/DATA/ORIGINAL/FILES/[file name].pdf

-------------------------------------------------------------------------------------

App clone  opens the file  . 

Googlesheets are in -------MYDRIVE/APPSHEET/DATA/CLONE (created during app copying)

PDFs created by the app clone  is stored in MYDRIVE/APPSHEET/DATA/CLONE/FILES/[file name].pdf

strange behaviour I have observed that at 1st time it opens the file generated and after updating the values in table , When I reopen the file the old version of the file is displayed but if I check the google drive it has the new one .

What is the issue going here ????

@Rifad @hapham @AleksiAlkio @MultiTech @Suvrutt_Gurjar 

-------------------------------------------------------------------------------------

[MultiTech]

This is a common issue, it's a caching thing that you're running into.

Your device, your phone or your computer, downloads the PDF and holds that in its memory. When you make changes to it the system does not redownload the newly updated file right away, it only does that every so often.

The actual file is being updated, so anything that you have that uses the file for sending it or anything will use the updated file. It's just that app sheet is trying to save resources by not continuously downloading the file over and over every time.

I generally find that within 24 hours the updated version of the PDF will be present in the app, so if I click on the file link it'll open the updated file now. But if I make changes to the file contents and regenerate the file after that, I'll have to wait a few hours or so for that new version to show up in the app.

But if I were to email myself that file, I would receive the updated file. Any sort of system that I may have that makes use of the file, will actually use the updated data because it's using the real file and not the cached version on the device. It's only when I press that button in the app to view the file inside app sheet, that's the only time that the system is using that cashed version.

[jaichith]

Bro @MultiTech , give me proper method to fix this.... 

[AleksiAlkio]

And how has your original app saved the file name into gSheet?

When opening the file with the app, it shows it from the cache. You need to give a version number to your file.. and the file name needs to be updated into your gSheet.

[jaichith]

And how has your original app saved the file name into gSheet?

I have changed the concatenate expression as per @Rifad advice 

by using the bot and action  Concatenate (my drive/appsheet.... File.pdf)

_________________

When opening the file with the app, it shows it from the cache. You need to give a version number to your file.. and the file name needs to be updated into your gSheet

In that case, several new or unique files will be created and it may be filling the Gdrive. 

Actually this PDF file is the output of First dashboard's filtered results

[AleksiAlkio]

Pleaes take a printscreen from your original app's gSheet how the file name is saved.

[jaichith]

@AleksiAlkio kindly check below

earlier I used concatenate ([file name].pdf) 

[AleksiAlkio]

Because your gSheet is in MYDRIVE/ORIGINAL, your path is wrong in your file column and that's why it can't find it. When using image, file etc. columns, you need to start the path from the location where the gSheet is. Normally the app knows that and saves tem properly. But now when you have saved the file with a Bot, you need to specify the path and file name by yourself.

Now your app tries to find it from.. MyDrive/Original/mydrive/appsheet/data/Courierapp-6502531/Files which doesn't exist. If you move your gSheet to Root > appsheet > data > Courierapp-6502531, then the path + file name needs to be "Files/"&[file name]&".pdf". But if you have any images or files saved via the app, you need to move that folder as well because you need to keep the relation to your gSheet.

[jaichith]

If I understand your reply properly, you are asking ti move the gsheets to root>appsheet>data>appname

Correct ?

I will try this and update you back 

[AleksiAlkio]

But you need to change the file name as well into Files/<PII removed by staff>.pdf in your gSheet

[AleksiAlkio · Accepted solution]

#1 - Move your gSheet to Root > appsheet > data > Courierapp-6502531
#2 - Move your existing file to Root > appsheet > data > Courierapp-6502531 > Files
#3 - In your gSheet, change the path + file name for that record to Files/<PII removed by staff>.pdf

[jaichith]

we have seen a improvement in the issue, now file is getting opened, but as I said earlier how to get latest version of the file because it gets cached version (old file) of the file during 2nd or 3rd updation. kindly tell me how to fix this.

Note: I prefer not to load the drive or folder with several old files. 

[AleksiAlkio]

As you can see, the only reason was the wrong path, nothing more. That's the normal mistake people are doing.

If you want to open the file from the app like this, as far as I know, there is no other solution than versioning it somehow.

[jaichith]

Ok @AleksiAlkio , if versioning is done definitely it will create new files. How to delete old files automatically which are no longer needed? 

[AleksiAlkio]

Either with scripting or bringing the folder to your app as an table and then delete the record with an action when the file name matches.

[Rifad]

Try this method https://www.googlecloudcommunity.com/gc/Tips-Tricks/Open-File-Image-with-external-browser-without-lo... 

[AleksiAlkio]

Good catch.. I didn't remember Koichi's trick 😄

[jaichith]

Hi @Rifad , @AleksiAlkio 

based your trick, I have used this expression in virtual column [file] and system action (open a website) has been created. 

CONCATENATE(‘https://www.appsheet.com/template/gettablefileurl?appName=’, ENCODEURL(CONTEXT(AppName)), ‘&tableName=’, ENCODEURL(CONTEXT(Table)), ‘&fileName=’,ENCODEURL([_thisrow].[FILTER ID]),".pdf")

thereafter as it mentioned in the post one more action has been created FILE2

since pdf file is located in sub folder /Files/ i have included %2FFiles%2F to ‘&fileName=’ .  therefore it becomes ‘&fileName=%2FFiles%2F’

Final expression is 

CONCATENATE(‘https://www.appsheet.com/template/gettablefileurl?appName=’, ENCODEURL(CONTEXT(AppName)), ‘&tableName=’, ENCODEURL(CONTEXT(Table)), ‘&fileName=%2FFiles%2F’,ENCODEURL([_thisrow].[FILTER ID]),".pdf")

Out put :

I hope I can fix this issue with your help. there is something to be added or removed in the expression

[Rifad]

Try this simple trick to get your exact file path and location by uploading a pdf directly to your app. Is it something like that you have shown above ?

---

@Rifad wrote:

Give it a go - create a new column for a file type and upload a file. Take a look at how the file path appears. Simply copy and paste that file path, then combine it with the PDF file name that's generated in here.

---

[jaichith]

Please check it below

[Rifad]

---

@Rifad wrote:

Try this simple trick to get your exact file path and location by uploading a pdf directly to your app. Is it something like that you have shown above ?

---

@Rifad wrote:

Give it a go - create a new column for a file type and upload a file. Take a look at how the file path appears. Simply copy and paste that file path, then combine it with the PDF file name that's generated in here.

---

Did you try this and check the relative file path in google sheet?

[jaichith]

A new folder is created named Filter dashboard. 

but PDF generated by the bot is saved to 

[Rifad]

Once Uploaded cross verify these two file path and database are in same folder in the drive.

[Rifad · Accepted solution]

---

@jaichith wrote:

Out put :

---

Oops I just saw this now.

---

@jaichith wrote:

Try tunrn of option: Require Image and File URL Signing

---

Did you follow his instructions?

[Rifad]

https://support.google.com/appsheet/answer/10107317?hl=en&sjid=777657368003588149-AP 

Have a look at this for more understanding.

[jaichith]

Dear @Rifad , 

After disabling the Require sign for image and File URL Signing it works.

[peterson]

Hello, @jaichith 

Please check my post.

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Learn-how-to-open-a-file-generated-in-Appsheet/t...

[jaichith]

Hi there,

The only difference which I can see is I have used concatenate ( element 1, element 2 ) . Instead of  "& " I have used  ","  this is the reason for not getting the files ? 

[jaichith]

Thread Summary

Problem :

1. How to set file path in bot for PDF generation 

2. File retrieval

3. How to get updated version of the file and its troubleshooting

______________________________________________________________________________________

Solutions:

1. How to set file path in bot for PDF generation 

1. When you specify the file name, do not specify the file extension (.pdf)
2. When writing the filepath to your database, include the file extension there. 
3. special thanks to @MultiTech for providing clear cut procedure

2. File retrieval:

It is better have Gsheet and files in app folder since app knows the path from gsheets is located. 

Example : 

#1 - Move your gSheet to Root > appsheet > data > Courierapp-6502531
#2 - Move your existing file to Root > appsheet > data > Courierapp-6502531 > Files
#3 - In your gSheet, change the path + file name for that record to Files/<PII removed by staff>.pdf

Special thanks to @AleksiAlkio for troubleshooting with an example

_____________________________________________________________________

3. How to get updated version of the file and its troubleshooting :

Right now it is impossible to get updated file after new entries. to avoid this URL method could be followed. Click here to know more

Special thanks to @Rifad  for bringing in the wonderful trick. 

During this implementation of URL  trick it is essential is disable 

 Require Image and File URL Signing  click to know more

This option could be found in SECURITY -> OPTIONS

__________________________________________________________________________________________

special thanks to @hapham @peterson for your timely help.

With Regards

Jaichith