Can you make "<<_ATTACHMENTFILE_URL>> Built in Variable to output the Google Drive attachment link?

[Cristian_Maria]

Hi guys,

I’m adding some workflows to my CRM app, and I was wondering if I could somehow automatically output the Google drive URL of the email attachment generated by workflow.

I’ve noticed that, by default:

1. the built in variable <<_ATTACHMENTFILE_URL>> outputs the appsheet server link to the actual file, something like:

https://www.appsheet.com/template/getappfileurl?appName=XXXXXXXX&fileName=XxXxXxXxXx.pdf&appVersion=...…&utm_medium=email&utm_source=transactional&utm_campaign=Workflow+Email

2. the actual PDF is also generated in Google Drive, according to my specified relative folder path

I would prefer the Google drive URL because I often include <<_ATTACHMENTFILE_URL>> in the email body template, and that way my co-workers could quickly access the file located in a shared Google Drive folder.

[Koichi_Tsuji]

Why dont you create virtual column and construct the URL to your files saved in your cloud?

This is just a sample formula to create a hyperlink to PDF which was generated by Workflow and saved as archive.

hyperlink(CONCATENATE(“https://www.appsheet.com/template/getappfileurl?appName=",“YOURAPPNAMEANDID”,"&tableName=",“YOURTABL... to PDF”)

Place your appnameID (from your APP url you can find) and table name etc. By default, your file should be saved in folder which are auto-generated by appsheet with name of Attachments. If your files are saved in somewhere else, then change this part accordingly to match with your folder name. Also on your workflow you do have file name for attachment and change the file name part over to it. Make sure on your workflow to take off putting timestamp out of your file name.

[Cristian_Maria]

Hi @tsuji_koichi, and thanks for your answer.

I’m guessing this won’t work, considering my relative folder path is also dynamically constructed, with an expression like

concatenate("/folderX/folderZ", [column name])

?

In case you’re wondering why I approached my relative path like that, it’s because my specific use case creates some project files, placing them automatically within the project’s “owner” organization folder

[Koichi_Tsuji]

How do you set the folder path in your workflow? I assume you might have sort of expression to generate the path as variable. My sample formula is for file path which is static and won’t change . So your case where the file path dynamically change, you simply push expression which generate dynamic path to the part of file path in the expression which will generate full path with dynamic relative folder path as well.

[Cristian_Maria]

@tsuji_koichi

tsuji_koichi:

How do you set the folder path in your workflow?

I just add the expression in the Attachment Folder Path field, found in the email content area, within the workflow’s action

please see attached screencapture

[Koichi_Tsuji]

You need to turn on disable time stamp . Right now Your PDF file in your g drive have name with time stamp all the time you run your workflow

File name out of the work flow need to be distinct.

[Cristian_Maria]

@tsuji_koichi

tsuji_koichi:

You need to turn on disable time stamp

Ok, that’s not a problem. But I don’t understand how my virtual column expression should look like, in order to output the google drive PDF file link, instead of the appsheet cloud file link. Can you please help me build it?

Here is the text version of my Attachment Folder Path:

concatenate("/TaskWorkfiles", [organization name])

And also the text version of my Attachment Name:

WorkFile_for_Task_<<[task id]>>_<<[task title]>>

AppName-id:
AstrivisCRM-1210465

Table Name"
crm_tasks

Let me know if you need something else please.

[Koichi_Tsuji]

Could you share your appname-id (see in url of your app) and your table name where you run your workflow ?

[Cristian_Maria]

@tsuji_koichi

I’ve edited my previous reply

[Koichi_Tsuji]

hyperlink(CONCATENATE(“https://www.appsheet.com/template/getappfileurl?appName=",“AstrivisCRM-1210465”,"&tableName=",“crm_t...",[organization name]),"/"),“WorkFile_for_Task_”,encodeurl([task id]),"_",encodeurl([task title])",".pdf"),"link to PDF”)

I have no way to test this expression with App editor, so not sure if this is returned as valid expression. But try.

And before you go, could you change folder path definition to

concatenate(“TaskWorkfiles”, [organization name])

Just remove “/” in the beginning as slash may cause a bit of issue when we construct URL to encode.

Then run the workflow first, to generate the new PDF file within Google drive. Check if workflow properly generated the PDF at the target folder with the proper file name.

[Cristian_Maria]

tsuji_koichi:

hyperlink(CONCATENATE(“[https://www.appsheet.com/template/getappfileurl?appName=",“AstrivisCRM-1210465”,"&tableName=",“crm_t...](https://www.appsheet.com/template/getappfileurl?appName=",“AstrivisCRM-1210465”,"&tableName=",“crm_t...",[organization name]),"/"),“WorkFile_for_Task_”,encodeurl([task id]),"_",encodeurl([task title])",".pdf"),"link to PDF”)

quick feedback: expression returns an error: …Number of opened and closed parentheses does not match.

[Koichi_Tsuji]

we need to user encodeURL expression to convert special characters like

“/” and even " " space if they are used in the file name etc.

[Cristian_Maria]

tsuji_koichi:

we need to user encodeURL expression to convert special characters like

“/” and even " " space if they are used in the file name etc.

Sure, I understand that, thanks

[Koichi_Tsuji]

hyperlink(CONCATENATE(“https://www.appsheet.com/template/getappfileurl?appName=",“AstrivisCRM-1210465”,"&tableName=",“crm_t...",[organization name],"/")),“WorkFile_for_Task_”,encodeurl([task id]),"_",encodeurl([task title])",".pdf"),"link to PDF”)

[Koichi_Tsuji]

still return error?
with this?

[Cristian_Maria]

tsuji_koichi:

still return error?
with this?

yes, unfortunately still the same error

[Koichi_Tsuji]

hyperlink(CONCATENATE(“https://www.appsheet.com/template/getappfileurl?appName=",“AstrivisCRM-1210465”,"&tableName=",“crm_t...",[organization name],"/")),“WorkFile_for_Task_”,encodeurl([task id]),"_",encodeurl([task title])",".pdf"),“link to PDF”)

[Cristian_Maria]

@tsuji_koichi

tsuji_koichi:

hyperlink(CONCATENATE(“[https://www.appsheet.com/template/getappfileurl?appName=",“AstrivisCRM-1210465”,"&tableName=",“crm_t...](https://www.appsheet.com/template/getappfileurl?appName=",“AstrivisCRM-1210465”,"&tableName=",“crm_t...",[organization name],"/")),“WorkFile_for_Task_”,encodeurl([task id]),"_",encodeurl([task title])",".pdf"),“link to PDF”)

I’m not sure it’s possible to use concatenate inside a hyperlink function…

Maybe create 2 different VC columns, one to concatenate and output as text, and the second to create the hyperlink from the previous one’s output?

[Koichi_Tsuji]

It works, but to make the story simpler, we can remove hyper link expression.

CONCATENATE(“https://www.appsheet.com/template/getappfileurl?appName=",“AstrivisCRM-1210465”,"&tableName=",“crm_t...",[organization name],"/")),“WorkFile_for_Task_”,encodeurl([task id]),"_",encodeurl([task title])",".pdf")

[Cristian_Maria]

tsuji_koichi:

It works, but to make the story simpler, we can remove hyper link expression.

CONCATENATE(“[https://www.appsheet.com/template/getappfileurl?appName=",“AstrivisCRM-1210465”,"&tableName=",“crm_t...](https://www.appsheet.com/template/getappfileurl?appName=",“AstrivisCRM-1210465”,"&tableName=",“crm_t...",[organization name],"/")),“WorkFile_for_Task_”,encodeurl([task id]),"_",encodeurl([task title])",".pdf")

The app expresion tester returns this:

The concatenation of all these text values (

…“https://www.appsheet.com/template/getappfileurl?appName=”
…“AstrivisCRM-1210465”
…"&tableName="
…“crm_tasks”
…"&fileName="
…“encodeurl(concatenate(”
…".pdf"

Does it look ok?

[Koichi_Tsuji]

Does not looks good.
For the purpose of debug, I suggest we start with the default file path first. Could you remove the you custom file path out of your workflow and make it blank? Then use the expression like this.

CONCATENATE(“https://www.appsheet.com/template/getappfileurl?appName=",“AstrivisCRM-1210465”,"&tableName=",“crm_t...",encodeurl([task id]),"_",encodeurl([task title]),".pdf")

Troublesome, but the step by step approach is always the shortest way to reach the goal.

This is expression where the pdf file will be saved to default folder with name of Attachments. Test this out first if this returns right full path. With positive test, then change the folder path part to meet your own and custom relative path. This is proper debug process.

[Cristian_Maria]

tsuji_koichi:

Could you remove the you custom file path out of your workflow and make it blank?

Maybe the custom attachment name as well?

[Koichi_Tsuji]

yes if you never mind.

[Koichi_Tsuji]

For instance, name the attachment file as “test”.
Then expression will be simply like

CONCATENATE(“https://www.appsheet.com/template/getappfileurl?appName=",“AstrivisCRM-1210465”,"&tableName=",“crm_t...")

now looks super simple!

[Cristian_Maria]

tsuji_koichi:

CONCATENATE(“https://www.appsheet.com/template/getappfileurl?appName=",“AstrivisCRM-1210465”,"&tableName=",“crm_t...")

But doesn’t that output the same thing as _ATTACHMENTFILE_URL variable? I just realized that now

[Koichi_Tsuji]

Yes, but we are going to reach the same file, dont worry.
There are few different ways to do the same.

[Cristian_Maria]

I’m sorry if I’m completely not comprehending this, but how will that help me output the google drive file link? Because that one is always something like:

`

https://drive.google.com/file/d/1abQhSON3uGmEJb8ZGo_xx/view?usp=sharing

`

[Koichi_Tsuji]

That is URL sharable link which Google provide. To get that sort of URL, we need to get file IDs. Instead of those google native i would say URL, Appsheet is providing a way to reach to your file with expression we are talking here.

Again forget about Google sharable url, as well as url which template variable returns.
We are talking about the same stuffs, but from different angles.

[Koichi_Tsuji]

To capture the google file IDs, it is tricky. Appsheet does not have native support to that. But they are giving a way to construct URL to reach to the file on any cloud service you are hosting. We are looking and accessing to the file you save through appsheet app, through Appsheet hosting server, so URL looks different, thats about it.

[Cristian_Maria]

I understand that, but the purpose of my post was to find out if I can somehow compute the google drive link, because I have no problem with including the https://www.appsheet.com/template/getappfileurl?appName=… types of URL’s in my email templates

[Koichi_Tsuji]

Oh, sorry I miss-read your post and query.
To construct ULR alike google sharable link, then there is no native feature to do it for now. Only Google Apps Script could do, to get file Ids, but I understand it wont make sense with you.

But providing URL to your apps user with URL starting with appsheet.com is to give the same experience to your end users.

Actually, I do have hyperlinks to the saved attachment files on all of my apps as well.

[Aman_Khan]

@tsuji_koichi sir it is showing the error Bad request while clicking to the url generated.

[APP_Account]

Excuse,me for adding a side question related to this topic.
I am creating an archive file on the DRIVE, and constructing link starting with appsheet.com as suggested - no problem. However I need to have functionality to delete file at certain scenario and re-generating it again with the same name and in the same folder. There is no problem deleting file itself, but when I generate new file, I really generate new (pdf) and when I access it directly in DRIVE, I see new file, but when I try to access via generated link in the app (appsheet.com…) I get the old deleted file, which I actually have already deleted from drive. Is it stored somehwere in cash and how can I reset it, to see only the new created file?
thanks, and sorry for adding extra topic. I thought this might be good to know.

[Dyako_Amin]

I think this may resolve your issue with link generation, you need to make sure you have IMAGE AND FILE URL SIGNING Turned OFF.

The concatenation displayed by tsuji_koichi is 100% correct once file signing is turned off.

[Haduaen123]

I've been reading whole of the tips but still don't understand 😂