Is it possible to setup a folder on Google Drive so that users can access the files within it if they have a direct link but they cannot see the folder if they browse to its parent?


Here's what I have:

Example

The Files folder has a bunch of Google Sheets files that I don't want users to rename or move. I realize that they can do this from the File menu when they open the spreadsheet but my average user is not familiar with that functionality. (They are very used to Windows network drives, though.)

The Log of Files spreadsheet contains basic information about all those files as well as a direct link to each.

Scripts in the spreadsheet create the files in the Files folder (using the current user's authorization) and store their direct links in the log. The files are named as their record identifier. For example, the log might have record ABC170001 and the file for that record would be named ABC170001. Users have a tendency to want to rename it to something they can recognize when browsing the folder such as Widget 1234 move to new supplier. This behavior can mess up the scripts in the log spreadsheet.


Here's what I want:

Example

The folder is still there and the direct links in the spreadsheet still work but people can't browse to the Files folder and see all the files. This would naturally encourage them from browsing to find their files and, instead, they would use the log to open what they want. This negates the most common reason the files get renamed.


Here's what I've considered:

  • Messing with access rights.
    • Problem: I haven't found a setting that hides the folder without also removing their ability to edit the files after opening them with the direct link.
  • Marking the folder as hidden.
    • Problem: I haven't find a property to do that.
  • Setting the property rights such that users can see the folder but can't open it and then setting the access rights for each file (when it is created by the script) such that anyone in the company can open/edit with a direct link.
    • Problem: I haven't trying changing access rights in Google Script yet and didn't want to spend the time with trial & error unless I thought it was likely to succeed.
  • Moving either the log or the folder somewhere else, thus obfuscating it from the user but not actually hiding it.
    • Problem: I'd like to avoid this option until I'm sure a more direct approach isn't available.
  • Share the folder with specific people instead of everyone with a link and then creating an installable trigger (or some other means) that runs under my authorization to create the files and set each authorization to "Domain with Link".
    • Problem: I haven't worked out how to let the user trigger it on demand and have it run under my access because the installable triggers seem very limited. I'm still trying to figure out how that could work.
up vote 3 down vote accepted

The solution to this seems to be quite simple. Go to your log of files document and set up each of your records e.g

ABC170001

As a hyperlink

Hyperlink to another file

Set the hyperlink to link to the appropriate file they need to edit. The Hyperlink should be generated with "Anyone with this link can edit the document"

Then when giving out the link for the log of files document set it as Anyone can view.

View Document

Don't give anyone permissions to view the folder just the files, meaning they can't edit your log of files file but the links should take them to an editable document.

Your current mistake seems to be that you are sharing the entire folder containing both the "Log of Files" and the "Files" folder

I demonstrate below what not to do:

DO NOT SHARE WHOLE FOLDER!!

  • That certainly explains my first hurdle: I can now hide the folder from users and they can still access the files. However, I forgot to clarify that the script to create files in the folder is run on demand by the users. I'm now trying to figure out a way to let them take action to create a file in a folder they can't access. That can be a different question if need be. I'm still poring through similar questions to see if I can integrate those solutions. – Engineer Toast May 15 '17 at 15:13
  • Could I see a copy of the script with any personal/identifiable information removed? – SleepingGod May 15 '17 at 15:46
  • You sent me in the right direction and I found other questions to fill in the holes that I found along that path. I posted my scripts below as an answer just because of how long it is. – Engineer Toast May 16 '17 at 12:28

I am posting this as an answer simply to capture my particular solution for the future. SleepingGod's answer pointed me in the right direction because I learned there was no way to share the folder in general while still hiding it so I had to find a way to run the functions as a different user. Stack Overflow already had a question about that so I was able to construct my solution based on that.


First, I wrote a script to copy a template file into the target folder and rename it as desired. It would take in parameters for the template file ID, the target folder ID, and the new file name. The code for that is as follows: (Note that it sets the access rights for the new file to Domain With Link so everyone can get to the file even though they can't get to the folder.)

function doGet(e) {

  //Check for missing parameters
  if (e.parameter.fileName == null) {
    return ContentService.createTextOutput('Error: Missing File Name').setMimeType(ContentService.MimeType.TEXT);
  }
  if (e.parameter.templateId == null) {
    return ContentService.createTextOutput('Error: Missing Template ID').setMimeType(ContentService.MimeType.TEXT);
  }
  if (e.parameter.folderId == null) {
    return ContentService.createTextOutput('Error: Missing Folder ID').setMimeType(ContentService.MimeType.TEXT);
  }

  //Save the parameters
  var fileName = e.parameter.fileName;
  var templateId = e.parameter.templateId;
  var folderId = e.parameter.folderId;

  //Create the copy and set the permissions
  var templateFile = DriveApp.getFileById(templateId);
  var parent = DriveApp.getFolderById(folderId);
  var newFile = templateFile.makeCopy(fileName,parent).setSharing(DriveApp.Access.DOMAIN_WITH_LINK, DriveApp.Permission.EDIT);
  var newId = newFile.getId();

  //Return the ID for the new file
  return ContentService.createTextOutput(newId).setMimeType(ContentService.MimeType.TEXT);

}

Next, I deployed it as a web app and allowed access to anyone, including anonymous. If you use some other access level, the function will return the HTML of the page asking the user to sign in.

Web App Settings


I copied the URL it gave me and plugged it into a call from the script within the sheets file.

function copyFileWithWebApp(fileName,templateId,folderId){
  var webAppURL = 'https://script.google.com/macros/s/AKfycb____________________N1vIMR-vruRzZt__________pzE07r/exec';
  var finalURL = webAppURL + "?fileName="+fileName + "&templateId="+templateId + "&folderId="+folderId;
  var newId = UrlFetchApp.fetch(finalURL);
  return newId;
}

Since the web app returns the ID of the copied file and the function returns the result of the fetch, I can get the ID of the copied file anywhere I want in the script.

var newId = copyFileWithWebApp(fileName,templateId,folderId);

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.