I'm trying to figure out how to write an Apps Script that checks "IF" a Google Drive URL is shared with a specific user. I do not want this to be a continuous check, just a "one time check".

Example Sheet

https://docs.google.com/spreadsheets/d/1dfLpgTdS-8AFUiD5Z7sFJvLn0mMid3_00TrxhDVkXno/edit#gid=432338521

I need "column H" in the sheet above to check the URL in "column G" to see if it is shared (with edit rights) with a specific gmail account (tbenrud@guhsd.net). Not a continuous check, just a "one time check". If the URL is shared (with edit rights) I'd like to return a "yes" in "column H" and return a "no" if not.

This sheet is populated by a google form. I hope the "one time check" can be triggered on form submit, but I'm not sure.

Any help is greatly appreciated.

CODE I'M EXPERIMENTING WITH

function myFunction(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('responses');
  var range = sheet.getRange('G2:G'); //Gets the file URL
  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';
    }
  }
}

Use the on form submit installable trigger.

Related Q&A

  • thanks for helping. I'm unclear as to the Apps Script I need to use in conjunction to the installable trigger. I've added a sample of what I was experimenting with to my original question. Thank you for any additional help you can offer. – Mr. B Aug 1 at 20:12

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.