Issues reading shared folder using DriveApp when other users run the Apps Script

Question

I've got a Google Sheet that uses App Script to read data from an external spreadsheet saved in a shared folder. There are multiple files in that folder (all named the same thing). I have one particular function that reads all files in a given folder and returns the id of the most recent one (which I then use to load the data from that file else where in the script).

The script was created using my corporate account and works perfectly when I execute it myself. However when other users try to run the same script they receive the follow error message:

    TypeError: Cannot read property '1' of undefined
    at get_latest_file_id(Code:380:21)
    at get_timesheet_data(Code:19:12)
    at unitTest(Code:78:3)

The error is obviously related to the fact that result[0][1] is undefined, however I'm not sure why. I suspect it has to do with permissions and my use of DriveApp. I've tested the following things:

I have tried using my personal Gmail account to run the script and read the file stored shared in a folder stored on my corporate account, this works fine.
I've tried moving the shared file to my personal account and run the script from my corporate account, this too works fine.

However if I use one of my colleagues accounts it doesn't work regardless if the file is stored on my corporate account (which is part of the same org) or on my personal account. When users run the script I get the usual "this developer isn't verified" and you have to choose to give access the first time the script is being run, but other than that I don't get any error messages that seems to point to permission so I'm not sure how to debug/proceed.

I'd appreciate any direction you could give me and suggestions for possible work arounds.

//RETREIVES THE FILE ID OF THE MOST RECENTLY CREATED FILE WITH A SPECIFIC NAME FROM A SPECIFIC FOLDER.
function get_latest_file_id(foldername,filename) {
  
  var folder = DriveApp.getFolderById('XXXXXXXXXXXXXXXX');  
  var files = DriveApp.getFilesByName(filename);
  var result = [];
  while (files.hasNext()) {
    var file = files.next();
    result.push([file.getDateCreated(),file.getId()]);
 } 
    
  
  result.sort(function(a,b){
  // Turn your strings into dates, and then subtract them
  // to get a value that is either negative, positive, or zero.
  return new Date(b.date) - new Date(a.date);
  });
  
  
  var id = result[0][1];
  
  
// Logger.log('Most recent ID:', id);

  

  return id;// return most recent file IDs

}//ENDS

result[0] is undefined. This means that your array is empty; the user cannot see the files. This seems a permissions problem. Use Google Drive's web UI with one of the affected users and make sure that they can access the folder and that they can see the files. — Martí
@marti I was about to reply to you that this wasn't the issue as the folder was set to "Share with the world" but though it might just try inviting people to the folder directly as well, and it worked. Not sure why or why the DriveApp function doesn't through some sort of error message but eithe way it WORKED. Sometimes the solutions are so simple :-) — The Ginger Fox

arul selvan arul selvan · Accepted Answer · 2021-02-10T11:46:01

Try This

function get_latest_file_id(foldername,filename) {
  
  var folder = DriveApp.getFolderById('XXXXXXXXXXXXXXXX');  
  var files = DriveApp.getFilesByName(filename);
  var result = [];
  while (files.hasNext()) {
    var file = files.next();
    result.push([file.getDateCreated(),file.getId()]);
  } 
  result.sort(function(x,y){
  var xp = x[0];
  var yp = y[0];
  return xp == yp ? 0 : xp > yp ? 1 : -1;
  var id = result[0][1];
    });
}

Issues reading shared folder using DriveApp when other users run the Apps Script

2 Answers