Trying to change sharing access of team drive files(more than 2,000 files) and generate link on google sheet via google script [closed]

Question

I have a lot of files on a team/shared drive that I want to generate links of. I managed to do it, but I also want to make the links viewable by anyone with the link. When I added the script to change each files sharing access, it always gives me Maximum time exceeded error or something. Here is the code, by the way, I aint really good on coding so this is mostly copy paste from here and there:

function myFunction() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var s=ss.getActiveSheet();
  var c=s.getActiveCell();
  var fldr=DriveApp.getFolderById("FOLDER_ID_HERE");
  var files=fldr.getFiles();
  var names=[],f,str;
  while (files.hasNext()) {
    f=files.next();
    // f.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW); //
    str='=hyperlink("https://drive.google.com/uc?id=' + f.getId() + '&export=download","' + f.getName() + '")';
    names.push([str]);
  }
  s.getRange(c.getRow(),c.getColumn(),names.length).setFormulas(names);
}

I can even settle for a script that makes all of the files accessible by anyone. This script works without the one in comments. But I need that to make all the generated links accessible by anyone :(

Answer 1

You are getting a time out while creating creating hyperlinks to files on Google Drive AND setting the access permissions.

1. Remove the access permission command from your script.
2. Run your existing script (to make hyperlinks).
3. Edit you existing script to capture only the file name and file ID, and use setValues() to write this information to a new sheet in your spreadsheet.
4. Run the following script against the new sheet to get the File by ID (getFileById) and update the access permissions. If you are still getting "Time out" errors, then you can limit the number of files that are processed by the function.

---

function wa13905502() {

  var sheetname = '<<INSERT SHEET NAME>>';
  // declare this sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetname);

  var LR = sheet.getLastRow();
  // assumes two columns - Name and File ID
  var data = sheet.getRange(2,1,LR-1,2).getValues();

  for (var i=0; i<LR-1;i++){

    var file = DriveApp.getFileById(data[i][1]).setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW);

  }
}