Attachment Path Issues - The Adventure Continues

[mykrobinson]

I'm still having issues with AppSheet creating deeply nested folders when archiving attachments. In a previous discussion, we determined the problem was likely due to the timestamp containing slashes, which of course translates to making folders in path notation 😞

I tried encapsulating the NOW() expression inside a TEXT() expression, hoping that would fix the problem, but it persists....

I need help learning how to properly convert these timestamps and such so that it does not add slashes. I need the filenames to contain some semblance of the date, but i want the path to remain constant so I can easily find all the files without having a hundred extra folders to dig through. The resulting filename in the email attachment is fine, but all those slashes are definitely causing the issue on the back end 🤔

 

[Steve · Accepted solution]

One possibility:

TEXT(NOW(), "YYYYMMDDHHMMSS")

TEXT() - AppSheet Help

[mykrobinson]

ahhhh... i was not aware of the "format" part of that expression until... well, until NOW()
I though that putting inside TEXT() would just magically convert it to a plain text format. I'll try the above, thank you.

[mykrobinson]

that worked! Thank you!

[SkrOYC]

Btw TEXT() is quite powerful and I like to use it with dates because can reduce a lot of expressions lines.

For example, we have YEAR(), MONTH(), DAY() and sometimes you may want to mix them to get something like 2022-7 or similar.

Text() makes this a breeze

[mykrobinson]

@Steve and\or @SkrOYC as usual, this always leads to more questions.... So now that the folder issue is resolved, i was hoping to be able to extract a URL for the resulting archived attachment. I settled on using this for the attachment name:

Patient Activity Report - <<TEXT(TODAY(), "YYYYMMDDHH")>>

without a full timestamp for the time being, because if i use the actual time stamp, the next step would fail because of the time difference.

So assuming i wanted to grab the URL for this file that was created at

AppsheetArchive\Patient Activity Reports\Patient Activity Report - <<TEXT(TODAY(), "YYYYMMDDHH")>>

how would i go about targeting that? The share links in Google Drive don't necessarily correspond to the actual path and filename 🤔

Lemme see if I can find some documentation on archive path. Is there an expression that will target the file that was created in the same automation?

[mykrobinson]

found this, reading up now:
https://support.google.com/appsheet/answer/11538747?hl=en#how-url-formed

[SkrOYC]

You need to deactivate the "Require Image and File URL Signing" in order to use the "manually created URL" way, I avoid that completely.

If you manage to create a relative path on a file column to the file made through bot, you will receive a signed URL thanks to that field/column

[SkrOYC]

So you want to access the file made through bot from inside the app? You have a table for reports btw?

I go the other way around when making this kind of things, I add the file name inside a field of my reports table and then I know for sure what's the file name because the bot just takes that field as the name, you can add minutes and seconds without problems this way

[mykrobinson]

haven't made a table yet. In this case, it's an automated report i'm running once per week (app is not in production at this time, just started it over the weekend) and i'd like to push the URL to Google Chat via webhook. 

Currently reading this:

https://support.google.com/appsheet/answer/11538747?hl=en#how-url-formed

[SkrOYC]

I suggest you to add the reports table for this purpose and make two bots, one that adds a row to the reports table (scheduled and can trigger other bots) and change the current one you are using on schedule to "adds only" on the reports table.

Make sure to understand what this means for the expressions you'll use on your report.

Then you can have the benefits of custom file names mentioned above