Document Processing - Folder as a Table

[Former Community Member]

Automation- DocProcessing-FolderAsATableExample.zip (405.9 KB)

Sample assets for trying out Folder as a Table feature of Document Processing.

[TyAlevizos]

BIG DEAL ALERT:

This gets rid of all kinds of code-y workarounds (apps script). For example. if you connect to a folder of images (1), you can create a virtual field (2) with this expression (3):
"https://drive.google.com/uc?id=" & [_ID]

Because we now have access to the Drive ID’s (4), you can get these images inline (5) in your app without any extra security fiddling, sharing, or coding. Nifty Nifty Nifty! Thanks for sharing, @prithpal

[Koichi_Tsuji]

Looking forward to testing this new feature soon.

UK looks exactly same as table config.

To add google target folder as a table, it is posible that we may have “folder picker” to add a new table? Or do we need to add new data source first for those folders? Just wondered how to add new folder as a table as user experiences.

[TyAlevizos]

@tsuji_koichi you choose "new table, and then you pick “Documents” as a type, then “folder”. Then you get the file picker, the assumption here is that you are picking a folder of existing images.

[Austin]

Wait a flipping minute
This has 0 use for myself as I don’t have any situations that call for this type of feature but I know that there’s always a question every other month or so about something like

[shaaland]

Also, putting a reference to the general announcement that has a link to the Primer pdf, which has instructions for how to use this feature…in case you are clicking on the zip file above and wondering what to do with it

About the AppSheet Automation channel AppSheet Automation

Welcome to the AppSheet Process Automation channel! The objective of this channel is for creators to collaborate (community style) with other participants to provide feedback (i.e. identify bugs, make feature requests) in order to drive product improvements in AppSheet Automation. Rules of Engagement: Please provide cordial and respectful feedback Optional: Read the optional Primer for in-depth information. The Primer also highlights the use cases to test. We’ve included test data below if y…

[Markus_Malessa]

I for one could actually use this functionality, however I am wondering what additional features are planned for this especially in regards to being able to add a REF column? Currently I have a table that has a file column [OrderDocument], so based on Appsheet if I choose a file from my computer/device a file will get created in my Appsheet repository for the application with the name key.OrderDocument.timestamp.pdf for example. Then in my file folder table in Appsheet I created a virtual column to extract the key from this file like so:

It could be helpful coming up with an internal mechanism that automatically appends a Ref Column to a document.

Also, of course the ability to add/delete/replace(version) from within Appsheet might be helpful. Last item would be to assign a category like in my case OrderDocument or something like Category so that files could be grouped in an inline table.

Just some ideas. As I am trying to incorporate some of these additional functionalities but have not yet due to the current limitations.

[Fabian_Weller]

@prithpal @Scott_Haaland this is a very cool feature. Thank you!

I found one issue:
When you click on “New Table” you can choose “Documents on Google Drive”.

After adding as Folder as a Table, I want to change the Folder. So I would click on “browse for more data”.

Here I can’t see “Documents on Google Drive”.

[Fabian_Weller]

@tsuji_koichi Are we able to get the thumbnail to PDF files?

[Koichi_Tsuji]

One possible workaround is this, but need third party service, like Filestack

1. Get file ID for PDF, using read folder as table.
2. Construct url to reach to the file, using file ID. Make sure to publish the whole folder. At the end of url, add path of “/preview”

https://drive.google.com/file/d/PDF_FILE_ID/preview

3. Using external service for screenshot out of URL, take the snapshot for this PDF preview page and return as image.

Not a perfect solution, but at least do a bit of jobs for us.

[Koichi_Tsuji]

Once the planned iframe view feature comes and plays, then we should be easily set this up, that is one of my other hopes.

[Fabian_Weller]

Thank you @tsuji_koichi
I found a way inside AppSheet that doesn’t need a 3rd party service. You also don’t need to publish your files.
This is also only a workaround. Not perfect. Just thumbnails

Show PDF Thumbnails when using Folder as a Table Tips & Tricks 💡

We can add a Google Drive folder as a data source. See this Post. I thought it would be cool to have the PDF’s thumbnail like we have in Google Drive. [image] We can do this in AppSheet with Card View and XY column Type. It will look like this: [image] Here is how to do it: Add your Google Drive folder as a new data source with “Documents on Google Drive”. This will generate a virtual table inside your App. In my case I called the table “Formulare”. [image] Add 3 Virtual Columns to …

[Koichi_Tsuji]

Nice and hacky approach
Thank you for sharing @Fabian

[Tiger1]

Prithpal,
I am struggling with this new feature. If I have a folder full of files and folder inside - will I need to add EACH folder as a table? Or the Parent folder only - then it can drill down?

[Fabian_Weller]

Hi @Tiger you have to add each single folder as a table. At the moment subfolders are not supported.

help.appsheet.com

Folder data source: The Essentials

[Tiger1]

Thank you for telling me…

[Tiger1]

If we have over 100 folders - do you have suggestions or thoughts on organizing it?

[Fabian_Weller]

If your files are located on your local server, you can install Google Backup & Sync on your Server, to upload your files to Google Drive.

You can then use Integromat to copy all your files (located in Google Drive) in one folder. You can automate this process.

So every time a new file is created or changed on your local Server, Backup & Sync uploads it to Google and Integromat copies it to your 1 folder.

Integromat

Google Drive Integrations

Integromat gives you the ability to integrate Google Drive with many other services.

[Tiger1]

I have actually thought about that route - Google Backup and Sync. However, If , by accident, one of the files gets deleted in Google Drive- doesn’t it also delete it in the server? I would hate for something like that to happen.

Instead, could I copy/paste ALL files into Google Drive, then each day copy/paste ONLY new created ones?

[Fabian_Weller]

Tiger:

However, If , by accident, one of the files gets deleted in Google Drive- doesn’t it also delete it in the server?

Yes.

Tiger:

Instead, could I copy/paste ALL files into Google Drive, then each day copy/paste ONLY new created ones?

You mean manually?

[Tiger1]

Well. At first - yes manually. Because I do not want to risk ANYTHING getting deleted. After the initial entering - can I automate copying without the possibility of deleting anything?

[Fabian_Weller]

I would recommend to play around with Integromat. You can do many cool things with it. We use it since one year without any loose of Data.
And please don’t forget: You can restore any file from trash in Google Drive.

[Steve]

Fabian:

You can restore any file from trash in Google Drive.

Assuming the trash itself isn’t cleared…

[shaaland]

We are looking at adding subfolder support – this is a feature in our backlog.

Can I ask, @Tiger - what is your use case with all these files in lots of subfolders? It would be great to understand what you are trying to do with AppSheet here. Thanks!

R,
Scott

[Tiger1]

Sure. For me - my company stores ALL invoices in a shared storage on our server. They save them by:

Company
Year
then…actual date

So - the sublevel aspect will allow me to drill down into those folders to search.

My work around?
I am basically opening up ALL folders and copying those into Google. With adding the sub levels (Folders) this will help tremendously with searching and storing.

[shaaland]

Got it…makes sense.

Are you hoping to use the Invoice document processing option we have for these invoices as well? This could auto-populate all of the data from the invoices into a table, and then you could just search by invoice contents in the app. For example, by vendor name, or invoice date, etc… If this was the case, you could just drop them all in one flat folder and use the appsheet table for searching instead of Drive folders to organize?

Thanks,
Scott

[Tiger1]

Yes. I do. I am running into a storage problem/issue with Google though. Since the original invoices are stored in our server (shared drive) I must move it all to Google. My Google account storage is almost spent! But, yes - if you support sub levels, that would help me a lot for sure.

[scott192]

When a new case is created in Appsheet, I have some Appscript that copies a new folder and file structure (every case folder has these default items) into a shared drive and returns the ID and URL of the structure back to the Case sheet.  The script is bound to that specific sheet and triggers via an onChange() event.

I also use Google Forms to replace the File Upload field in Appsheet as this allows me to prefill a URL for the form where I can pass the folderID of the folder I want to place the files into once uploaded.  I also use the Google Form because the file picker allows multiple selections, allows you to pick documents from Google Drive as well as Upload.  In addition, I have Appscript running on the recipient sheet for the uploads that can move the incoming files into the correct folders (I passed the folder ID on the form remember) and populate an 'attachments' table with the correct Keys and URLs to link them back to the created Case parent table.  This also has the advantage that clicking the URL link for the uploaded files opens them in Google Drive and not via the Appsheet file viewing mechanism.

I also use Appscript to make changes to Sharing for those folders as each case can be allocated to named users in our domain.  Again using the onChange() event the script will remove any users on the root folder of that case (The shared drive has default users who administer the case folders and these users do not get removed as per the Shared Drive permission settings) and then Add the users that are named on the row in the sheet.

All of this works very well, but it does mean a lot of duplication of code per sheet to perform these necessary functions where needed.  I do intend to spin out the code into a callable webapp and try to make it less specific to certain sheets...for instance it could be passed a rootID (Folder), a sourceID(Folder with other nested files and folders), a sheetID(The sheet we are going to make changes to), an Appsheet UniqueID(to identify the row in the sheet we are updating...etc.  You get the gist.  In fact I am possibly redoing work that Zenphi already does...now wouldn't it be cool if Zenphi was integrated as part of the Automation in Appsheet...are you listening Google?

Of course, if Appsheet implemented all of this necessary stuff for Google users as well as allow the creation of Google Docs, Sheets and Slides with all of the sharing stuff taken care of as well then all would be well and would truly make Appsheet 'no code' and satisfy all the Googlers who were already using AppMaker to do this stuff years ago!

Hope my use case helps in the development of this Appsheet feature though 😀

Scott

[Techuke]

30Jul23 1949 Ohhhhhhh my goooooodnesss .. @scott192 and @Tiger1 Google should pay you to more simply implement these ID and URL file handling tools from inside Appsheet  .. on behalf of the rest of us! Thank you very much from me in New Zealand. Love you guys (and gals).

[scott192]

Ahh well this is quite an old method for me now as I have moved most of my stuff (apart from the Google Form file upload stuff) across to Appscript Tasks within Appsheet.  Much better than using onChange() events in the backend sheet as Appsheet can respond to returned data from the script (File/Folder IDs/URLs etc) and sync a lot faster than before.

[Ratatosk]

Wow this is huge, how on earth did I miss it!!

This reduces the need for apps script by a lot!!

Quick question, is there a way to show only the filename instead of the full path?

[Koichi_Tsuji]

you have [File] field already.
What else are you looking for?

[Ratatosk]

I already use [File] as an image type.

Sorry I’ve been on a break from Appsheet developing for many months.

I’d hate to use virtual column for something this simple.

[elenaallen]

Great feature.  Would love the extension to sub-folders!