Google Sheets – Custom Function to Test if a Document is Shared

google sheetsgoogle-apps-scriptworksheet-function

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.

Best Answer

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';
    }
  }
}
Related Topic