Populate Google Sheets with PDF links from Google Drive

Hi,

Wanted to see if anyone can provide a way to accomplish the following:
I have PDF files being dropped into Google Drive folders every week. Is it possible for AppSheet to check these folders and paste links to newly dropped PDF files into Google Sheets column?

These are new statements being generated every week and I would like to automatically display them in the app.

I’ve not yet used it, but AppSheet can use a Google Drive folder as a data source. Once connected as a data source, I imagine you could use Automation to examine the folder and record new files in another tab;e within the app.

To add a folder as a data source, go to Data >> Tables in the app editor and add a new table. The pop-up should offer the option to add documents. Choose that.

2 Likes

Thank you, Steve. I will give that a try.

1 Like

For reference:

2 Likes

Wy Im can’t sets two "like"s?

Thank you, Steve. I played around a bit with using Google Drive as the data source as you suggested and so far it looks very promising.
Would you have any recommendations on how to implement Security Filters for the files within Google Drive? I plan to dump PDF statements from all companies into a folder and would obviously want users to only see statements relating to their company.

Currently I’m using the Security Filter below for data within google sheets. Is there any way to implement something similar for PDF files from Google Drive.

ISNOTBLANK(
FILTER(
“Users”,
AND(
ISNOTBLANK([Email]),
([Email] = USEREMAIL()),
ISNOTBLANK([Company ID]),
([Company ID] = [_THISROW].[Company ID])
)
)
)

How does the app know which files belong to a given company?

Well, currently it doesn’t. I have Power Automate pull files from a website and dump it into Google Drive every week. I’m trying to come up with an approach to differentiate files from different companies. I’m considering these two options:

  1. Program the automation tool to drop files from different companies into different folders. The issue I’m seeing with this approach is that AppSheet will only use one folder as a data source, not multiple folders (let me know if this is incorrect)
  2. Since the files from all companies will be dropped into one folder, I can program the automation tool to insert company name into the file name. Then, maybe create a virtual column within AppSheet and extract company name then set security filter on that. Do you think this is feasible?

Can your automation tool update the spreadsheet directly? If so, you could organize the files however you want and you wouldn’t have to rename them.

that’s the right way to do it on this occasion while I have no idea on powerapp stuffs.

1 Like

It probably could, but I think letting AppSheet extract the name into a virtual column is probably best as the chances of automation tool running into an error increase.

Could I use the split() function to extract the company name from path within a certain delimiter? For example, anything between hyphens (-Company Name-) gets placed into the virtual Company Name column.

1 Like

Yes, I will explore this route. The power automate takes it up to the point of dropping files into Google Drive. After that I think there’s too much room for error. Need to figure out how to extract company name from path in AppSheet.

1 Like

I have posted a solution for the problem of getting Google Drive links into google sheets using google scripts, you can check my replies to this post if you’re interested in going that route:

2 Likes

You certainly could. Note that you must be able to ensure extra delimiters occur in the path.

I’ll check it out. Thank you.

1 Like

Yep, the automation tool will include the hyphen delimiter in the file name like this: -Company Name-. I’m reading your article on Split() and currently have this “split([Path],”-")" in the formula for Company Name.
When I look at the data, the virtual column Company Name includes the entire path but separates the string by a comma based on the delimiter. Any way to just have company name in the column?

1 Like

SPLIT() produces a list of text fragments from the input text. For instance, if your expression is:

SPLIT("Image Uploads/IMG77383.jpg-Acme Industries-", "-")

The result would be a list of the following Text values:

  1. Image Uploads/IMG77383.jpg
  2. Acme Industries
  3. (blank)

To access one specific item in that list, use INDEX():

INDEX(SPLIT("Image Uploads/IMG77383.jpg-Acme Industries-", "-"), 2)

That would give Acme Industries.

See also:

1 Like

Perfect! Thank you, Steve, for the thorough explanation. Really appreciate it.

1 Like

Seems that my approach to populate a virtual column with Company Name would not solve the security issue as security filters do not work with virtual columns. Just tried and got an error. Any way around this?

In the security filter:

([Company] = INDEX(SPLIT([Path], "-"), 2))