File Column Should Store Google Drive URL

[Markus_Malessa]

I am having a hard time grasping the File type column concept as it relates to how Appsheet treats a file when it is stored on Google Drive. It appears that Appsheet creates a file path to the file that essentially opens an encoded URL that allows the file to be uploaded and viewed from the application.

What I need, is the file upload capability however the actual column should store a URL of the file in Google Drive. I need this capability because I have other programs where I need a URL that could be opened by someone whereas currently that column shows a file path only and does not actually open a file. The other program I am using is Google Data Studio and for that I would need a URL so I can open it from GDS.

Could someone clarify this for me? Thank you.

[Steve]

Markus_Malessa:

It appears that Appsheet creates a file path to the file that essentially opens an encoded URL that allows the file to be uploaded and viewed from the application.

You find it odd AppSheet would store the file specification in a form most suited for use with AppSheet?

[Markus_Malessa]

Not really odd, I suppose, but since my data is stored in MySQL and I access the SQL tables from elsewhere I need a way to store the file as a Gdrive URL so that I can pull up the document just via link.

That is where I am stuck. Either store the document as a Gdrive URL directly in the column or would I need to create another column in my SQL table that would store the URL? I guess that is my real question and how would I convert the appsheet file to a Gdrive URL to be stored in the table?

[Steve]

Markus_Malessa:

I guess that is my real question and how would I convert the appsheet file to a Gdrive URL to be stored in the table?

I’ve not attempted what you’re trying to do, so I can only speculate.

The stored path is relative to the app’s data directory, so you’ll need to construct the appropriate shareable URL from there. The concern that comes to mind is whether Gdrive doc URLs even use fully-qualified paths, or if you have to use the doc’s doc ID. If the doc ID, I don’t know how to get that in an automated way.

@Bellave_Jayaram, do you have any insight to offer here?

[Marc_Dillon]

help.appsheet.com

Displaying Images and Documents

How to display documents and images in your app.

Scroll down about halfway to the “Showing Images in Google Sheets” section where it details how to construct a public URL (in a separate column) for an image (or any uploaded file).

[Markus_Malessa]

@Marc_Dillon would you be able to provide some additional clarity on this. I added a virtual column for testing purposes and entered the formula below and then changed the column type to URL. From the application I then click on the link and the browser opens a new tab with a result of ‘Bad Request’.

I appreciate your additional insight. Thank you.

[Marc_Dillon]

Did you disable the appropriate security options as detailed in the article?

[Markus_Malessa]

Sure did. I made the entire folder viewable by anyone with the link in my Gdrive. The actual URL in the browser window is
https://www.appsheet.com/template/gettablefileurl?appName=TWUAData-1294841&tableName=TempWaterUseAgr...

The result URL appears to include the folder directory, and I am wondering if that may be the problem, but I am not sure.

[Markus_Malessa]

This is the actual value stored in the column
TempWaterUseAgreements_Files_/17208c1f.OrderDocument.170306.pdf

So I suppose that may not be the problem as it stores the folder directory along with the file name.

[Marc_Dillon]

You say you are using MySQL for your database? Is the table “TempWaterUseAgreements” part of that SQL database?

[Markus_Malessa]

Yes, the field in the MySQL DB is of type string. So in the DB itself the field actually stores ‘TempWaterUseAgreements_Files_/17208c1f.OrderDocument.170306.pdf’ as a value for one of the rows.

What I would like though is a URL, whether that needs to be accomplished via a second column in the MySQL table or otherwise. I am presenting data from this table in a Google Data Studio report and just need a URL link in the report to the document for each of the rows. Public access is not a problem as this is public information anyways.

[Marc_Dillon · Accepted solution]

Please double check that all 3 of these Security Options are turned off:

---

---

---

EDIT: Below info was not applicable to this scenario, but I’m leaving it here because it could be useful to someone in the future…maybe?

---

My thought was that the fileName parameter in the URL needs to be a relative path to the location of the file where tableName is in. Here is something you can try:

1. Create a new GSheet in the folder directly above “TempWaterUseAgreements_Files_”
2. Just put “id” into cell A1, and “1” into cell A2 (or similar, just so it isn’t blank)
3. Load that as a new table into your app.
4. Modify the URL to replace the tableName argument with the name of the table you just loaded.

Although I’m not sure if that is going to work since I just did a test on an app where I have created public file URLs where I just changed the tableName argument, and got an error different from just “Bad Request”:

[Markus_Malessa]

Ah, it was the security settings that prevented the URL from opening. I unchecked the ‘Require Image and File URL Signing’ and that did the trick.

I guess to completely solve my problem though do I need one column for the file and another for the URL to the file in the MySQL table?

[Marc_Dillon]

Oh ok, great. Yes I would advise a 2nd real column to hold the public URL.

[andriesellis]

Hi @Marc_Dillon . I know this is a solution from long ago, but looks like exactly what I need. I do want to know though, my PDF files are created by appsheet and stored in different folders based on the date they are generated. Will the file name be enough to find the correct location or will the path need to be encoded also?

[Marc_Dillon]

You need to encode the full value in the file column, the relative path and filename.

[andriesellis]

Thanks @Marc_Dillon . I'll try this. Not sure if I'm doing something wrong though. I'm storing the file name and url value inside the main table, then I have an external sheet in the Drive directory where the folders for the PDF files resides. This sheet extracts the relevant values out of the main table (including the url value) through a query. All seems to work fine, but then I get the feared "BAD REQUEST" when trying to open the link.

Also, I'm not sure how safe it is to give public access to this link.😬

Here is the Folder with the subfolders and the file that does the extraction from the main table:Then here is the file inside the folder.The hyperlink in the sheet. Im not sure if the folder/file name is written properly.Folder name: Jobs Completed-Week33

Filename: 2022-08-15_22 Hajjee, Primindia Brits.pdf

I tried adding a "/" between the folder and filename like https://www.appsheet.com/template/gettablefileurl?appName=&tableName=&fileName=Jobs%20Completed-Week...,

but the same Bad Request message.

[Marc_Dillon]

Your URL has neither an appName nor tableName value. Those are both required. Make sure you thoroughly read the help doc linked above.

[andriesellis]

@Marc_Dillon : Yes, I see where I messed that up and fixed it. Now it looks like it is creating the URL correctly. But clicking on the link, I get another error. 

Filepath: JobsCompleted-Week33/2022-08-16_8-LotusStreet-Brits.pdf

URL: https://www.appsheet.com/template/gettablefileurl?appName=HomeConnects-1001461187&tableName=Jobs&fil...

Error:

 

[EmadK]

Were you able to find a solution for this? I am having the same issue.