Solved: How to retrieve the file names of Google Documents...

soushi

Hello,

I'm developing a task management tool that allows teammates to share tasks. When registering a task, users can attach URLs for related documents such as Google Docs, Slides, and Sheets. However, since AppSheet only permits running AppsScript as an app owner (=me), if the app owner does not have access permission to these URLs, the file names cannot be displayed. As a result, I am unable to show titles of Google documents (e.g., "Weekly Meeting Minutes with ABC company") on the app, which is problematic.

Previously, when using Area120Tables, we had the "Drive attachments" column type, which allowed viewing file types and names even without access permission to the file. Additionally, Tables Bots could run with end-user permissions, enabling us to change file access permission and grant view permissions to everyone with access to the table.

I considered having end-users set up AppsScript time triggers to periodically fetch file names, but since triggers are limited to 20 users per script (?), I imagine this could be difficult.

Do you have any ideas or methods? I've been thinking about this for three days and am quite exhausted...

Joseph_Seddik

You are right!

How about inviting the users to upload the files in the app instead of copy/pasting the URLs? or may be instructing them to move the files into a Drive folder with an ANYONE, READ permission? or even deploy your Apps Script as a web app that can be launched from your app but not by the app? This is what I could think of.

View solution in original post

Joseph_Seddik

In your script, use the setSharing method available to DriveApp's File and Folder classes, to change the file/folder permissions before displaying it to the users.

soushi

Thank you for your reply, Joseph.

In this case, the app owner does not have view permissions for the file. Additionally, according to this help article, in AppSheet, the script always operates with the app owner's permissions, meaning it is not possible to change permissions using methods like setSharing()...

Are there any other methods available?

Joseph_Seddik

You are right!

How about inviting the users to upload the files in the app instead of copy/pasting the URLs? or may be instructing them to move the files into a Drive folder with an ANYONE, READ permission? or even deploy your Apps Script as a web app that can be launched from your app but not by the app? This is what I could think of.

soushi

Hi Joseph,

Thank you for your prompt reply. I hadn't thought of invoking Apps Script as a web application. I gave it a try and was asked for access to Drive, and I was able to display a list including file names successfully. Thank you very much!!

Now that the access issue is resolved, it seems I can create the desired functionality by using the Google Picker API to select files. This might take some time, but I wanted to thank you first.

Here is the code that displays 100 entries for others who want to do the same (as the first step).

1. Create a new Apps Script file
2. Read files in Google Drive via DriveApp (ref. Advanced Drive Service - Google Developers)

function doGet() {
  // No guarantee to work, no support
  const MAX_NUM_OF_FILES = 100;
  let sampleFiles = {};
  let repeat = 0;
  let files = DriveApp.getFiles();
  while (files.hasNext()) {
    const file = files.next();
    const fileName = file.getName();
    const url = file.getUrl();
    const fileId = file.getId();
    sampleFiles[fileId] = { name: fileName, url: url };
    if (repeat++ > 100) MAX_NUM_OF_FILES;
  }

  let template = HtmlService.createTemplateFromFile("file_uploader");
  template.filesJSON = JSON.stringify(sampleFiles);
  return template.evaluate();
}

3. Add HTML file (named "file_uploader.html") in the Apps Script project

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h1>Your Google Drive Files</h1>
    <table border="1">
      <tr><th>File Name</th><th>File ID</th><th>File URL</th></tr>
      <? let files = JSON.parse(filesJSON); ?>
      <? for (let key in files) { ?>
      <tr><td><a href='<?= files[key]["url"] ?>'><?= files[key]["name"] ?></a></td><td><?= key ?></td><td><?= files[key]["url"] ?></td></tr>
      <? } ?>
    </table>
  </body>
</html>

4. Deploy the code as Web application (ref. Create and manage deployments - Google Developers). You can get a Web App URL through the deployment.
5. In AppSheet, create an Action, and select "External: go to a website" in the [ Do this ] field, then paste the WebApp URL in the [ Target ] field.

6. Select "Primary" as [ Position ]
7. Save it, and click the button to trigger the action.
8. You'll be required to give the script access to your Google Drive. <= This is the proof that the script is running as an end-user credential.
9. After allowing it, you can see File name and File IDs like this:

@Joseph_Seddik HUGE THANKS for your help! I couldn't have done it without you!

Thank you!

Joseph_Seddik

Excellent work @soushi and thanks much for sharing the code! 🌷

How to retrieve the file names of Google Documents (Docs/Slides/Sheets) shared by other users?