Uploading a file through appsheet API

Erik_Ferm · 11-29-2022 06:24 AM

Quick question to ask if anyone as figured out a way of doing this:

I am looking to add records to an appsheet table using the appsheet API in apps script. Ideally, one of the columns I am passing would contain a pdf file.

Adding a record works fine but I don't think I can pass a file through the API. Is this correct?

I could have apps script put the file where it needs to be and then pass the path to it through the API but this would involve giving users write access to the folder which I would like to avoid.

Has anyone else had this problem?

SkrOYC

Afaik, you can't add files through automation that are uploaded to your storage provider as it's when doing so from inside the app.

If you have a public URL, AppSheet can use that on a file column to access the file.
I don't know if AppSheet will provide base64 upload in the future or if someone has experience with it.

Erik_Ferm

Thanks for your answer. I think you are right. I could add a folder where the add-on could store the file and then pass the url to appsheet. The problem with that is that different users would be able to read each others files which is not great. (Unless I could set the folder permissions for the users to add files but not read or edit but I don''t think Google Drive lets you do that.)

I might add passing a file through the API this as a feature request.

dbaum

@Erik_Ferm wrote:

Unless I could set the folder permissions for the users to add files but not read or edit but I don''t think Google Drive lets you do that.

It's definitely possible to use Apps Script to manage user permissions for Drive files and, I assume, folders. I have an AppSheet automation that includes a task to call a script that includes functions that add/remove file permissions.

Erik_Ferm

@dbaum wrote:
@Erik_Ferm wrote:
Unless I could set the folder permissions for the users to add files but not read or edit but I don''t think Google Drive lets you do that.
It's definitely possible to use Apps Script to manage user permissions for Drive files and, I assume, folders. I have an AppSheet automation that includes a task to call a script that includes functions that add/remove file permissions.

I think you are right that apps script can change file and folder permissions. I am no apps scripts guru but it seems that the folder object has a method called setSharing which can be used to give users access.

The issue I think is that you can only set sharing to view, edit, comment, owner, organizer etc. The is no "contributor" category which doesn't also have read access as far as I can see. So my team members would be able to read each others documents which doesn't work in this particular situation.

So I don't think you can do the equivalent of "chmod 702 [file]" in Linux which would give all users write access without being able to read or execute anything.

I guess this could be achieved by having a temporary folder which everyone has edit access to, let the script team members execute store the file in this temporary folder and then have anther script running as the app creator moving the files to the appsheeet folder as they are added. It just seems like hard work...

dbaum

Alternatively, your script could set permissions at the file level as it processed each file—eg, share only to the user and app owner.

Erik_Ferm

I think the challenge is that when a team member triggers the script in the add-on, the script runs as that user.

In order to be able to create the file in the first place, the user needs write access to the folder. As mentioned, I don't think Google Drive let's you give someone write access without read access. So each use who has a right to store a file there will also have the right to read all other files in the same location.

dbaum

I don't know whether a folder's permissions propagate to its constituent files with permissions that are explicitly set differently. I assume not. Anyway, if you don't know for sure and want to test, it would be easy to try. Create a folder with Edit permission for multiple users and then within the folder create separate files with permissions that explicitly exclude some of those users. If you had to, in a script you could even provide the folder-level Edit permission only temporarily at the time of file creation.

Erik_Ferm

I think a file inherits sharing from the parent folder when you create it. So when a scripts creates a new file, you would have to stop sharing for all other users who have access to the folder.

And when the folder is shared with a new team member, you would have to stop sharing for all existing files.

It could probably be done but you would need a couple of scripts monitoring the folder.