Populate Google Sheets with PDF links from Google Drive

[Peter_S1]

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.

[Steve]

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.

[Peter_S1]

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

[Steve]

For reference:

help.appsheet.com

Compiling folder contents

Compile a list of files and metadata from a folder in Google Drive.

[bonameblisto]

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

[Peter_S1]

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])
)
)
)

[Steve]

Peter_S1:

Would you have any recommendations on how to implement Security Filters for the files within Google Drive?

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

[Peter_S1]

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?

[Steve]

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.

[Peter_S1]

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.

[Steve]

Peter_S1:

Could I use the split() function to extract the company name from path within a certain delimiter?

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

[Peter_S1]

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?

[Steve]

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:

help.appsheet.com

INDEX()

One specific list item

[Peter_S1]

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

[Peter_S1]

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?

[Steve]

In the security filter:

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

[Peter_S1]

Sorry, let me provide some context. I’m using the file name to extract Company Name into a virtual column. Then, use the Company Name to look up and fill in Company ID (another virtual column). Once this data is populated, I’m using the security filter below (for my other views at least) to only show data pertinent to users relating to a specific company.

ISNOTBLANK(
FILTER(
“Users”,
AND(
ISNOTBLANK([Email]),
([Email] = USEREMAIL()),
ISNOTBLANK([Company ID]),
([Company ID] = [_THISROW].[Company ID])
)
)
)
Then get the error below:
The expression is valid but virtual columns such as ‘Company ID’ are not allowed.

I’ve tried researching this on how to identify Company ID so AppSheet recognizes it, but no luck. Appreciate any help.

[Steve]

Like this?

ISNOTBLANK(
  FILTER(
    “Users”,
    AND(
      ISNOTBLANK([Email]),
      ([Email] = USEREMAIL()),
      ISNOTBLANK([Company ID]),
      ([Company ID] = INDEX(SPLIT([_THISROW].[Path], "-"), 2))
    )
  )
)

Also FYI:

help.appsheet.com

Configuring data processing

Control how AppSheet processes data by setting configuration options.

And perhaps consider this:

Current_User (Slice) - How to conform your app around WHO is using the app Tips & Tricks 💡

One of the core basic functions, included in just about EVERY app I make, is the ability for the app to know WHO is using the app. If the app knows who’s using it, then I can easily control many different aspects of the app: Add/edit/delete permissions How data should be filtered What views are shown What buttons, or Actions, are visible Which workflows should fire off Which columns should be shown or editable etc. Requirements To accomplish this functionality in your app, you need the follo…

[Peter_S1]

The filter code completely blocks the content for all users.
I don’t think the Split() function should be part of the filter as it’s used to extract the Company Name from the file name. Once that’s achieved, I’m using the Company Name to populate Company ID (virtual column), which is then used in a security filter.

The code below works for my other views, but the error I’m getting for this particular view is because Company ID is a virtual column and that’s not allowed. Any way to handle this?

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

[Steve]

Peter_S1:

Any way to handle this?

No.

[Koichi_Tsuji]

Peter_S1:

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?

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

[Peter_S1]

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.

[Rafael_ANEIC-PY]

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:

Uploading images from app into google sheets Questions

Hi, I’ve created a App from an existing google form and it appears to be working great with the exception of the photo upload. The app is a reporting app for field engineers and they are required to take a photo for each submission but I cannot find a way to get the link or picture into my google sheet so it can be opened directly from the sheet. I need a hyperlink to the exact photo and an embedded copy ideally. All I can do at the moment is get the images to upload to a folder in my google dr…

[Peter_S1]

I’ll check it out. Thank you.

[MAGG_Group_Engi]

Hi Rafael,

I hope your keeping safe and well?
I don’t suppose you would be able to help me out again would you with the same script as last time but on a new sheet?

Kind regards
Loren

[Rafael_ANEIC-PY]

Hello @MAGG_Group_Engineers, you can ask me what you need in your old post, it’s easier to reference the code I posted back there hehe