I'm trying to find out if a Google document is shared with me before I open it. I collect student work via a google form and I want to add a function to the responses spreadsheet that tells me 'if' the document is shared with me. Is this possible?

I've tried the following, but I couldn't get it to work. Plus, if it does work, it only tells me 'if' it has editors, not if the document has me as an editor.

function isShared(){
  var editors = DriveApp.getFileById().getEditors()
  if(editors.length > 0){
    return true
  }
  return false 
}

Target Document

demo

Document ID's are in column G and I'd like "true" or "false" displayed in column H.

up vote 1 down vote accepted

Short answer

  1. DriveApp can't be used in a custom function because it requires authorization to access files1, so run the script from the script editor or from a custom menu.
  2. If a file isn't shared with you, getFileById will return an error, so it's not necessary to check any other file property.
  3. By one side, Google Apps Script has execution time limit of 6 minutes. If you has many file IDs it could be possible that you should run the script by subsets.

1: https://developers.google.com/apps-script/guides/sheets/functions#using_apps_script_services

Explanation

From https://developers.google.com/apps-script/reference/drive/drive-app#getfilebyidid

getFileById(id)

Gets the file with the given ID. Throws a scripting exception if the file does not exist or the user does not have permission to access it.

Code

Run myFunction from the Script Editor or by using a custom menu.

function myFunction(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Period6');
  var range = sheet.getRange('G2:G20'); //Gets the file's ID range
  var output = SHAREDWITHME(range.getValues()); //Checks each value on the selected range
  //Sets the results to the right of the selected range
  range.offset(0, 1).setValues(output); 
}
/**
 * Check files IDs are from files shared with me
 *
 * @param {A1:A5} input The range with file IDs.
 * @return TRUE or FALSE
 * 
 */

function SHAREDWITHME(input) {
  if (input.map) {            // Test whether input is an array.
    return input.map(SHAREDWITHME) // Recurse over array if so.
  } else {
    try {
      var file = DriveApp.getFileById(input);
      return 'TRUE'
    } catch(e) {
      return 'FALSE';
    }
  }
}
  • thanks for this! I'm still very very new to app script and need a little guidance using your solution. I've added the above to script editor - i.imgur.com/qBxGPGC.png and tried using =SHAREDWITHME - i.imgur.com/3oOxUex.png in my spreadsheet. But I only receive "false" even if I have edit rights to the document. I'm sure I'm missing something in script editor. I get errors - i.imgur.com/sVOafVn.png when I try to add ranges in script editor. I've added a link to the target document in my original question. Any help is greatly appreciated. – Mr. B Nov 7 '16 at 21:52
  • 1
    I updated my answer. – Rubén Nov 8 '16 at 1:38
  • quick question. There are a few options I want to add to the solution you provided me. Should I create a new question or update this question or ask a new question? – Mr. B Nov 8 '16 at 6:22
  • 1
    Post a new question, please. – Rubén Nov 8 '16 at 13:08

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.