I have a spreadsheet with multiple rows which list a name, email, filename, fileID, fileURL, and a list of permissions. I want my scrip to change the permissions on each file as it loops through the a foreach loop on the data. I can't seem to get the file id to be carried forward into the loop.

function shareFile() {
  const sheet = SS.getSheetByName('Sheet1');
  const fileID = sheet.getRange(2, 4).getValue();
  const fileToShare = DriveApp.getFileById(fileID);
  const permissionsData = sheet.getRange('A2:F').getValues();
  permissionsData.forEach(row => {
    if (row[5] === "Viewer") {
      fileToShare.addViewer(row[1]);
    } else if (row[5] === "Commentor") {
      fileToShare.addCommenter(row[1]);
    } else if (row[5] === "Editor") {
      fileToShare.addEditor(row[1]);
    } else {
      SpreadsheetApp.getUi().alert('Permissions must be set to "Viewer", "Commentor", or "Editor". Please check your input. Thank you');
    }
  })
}

What do I change here to get the correct file ID for each row to change permissions based on column F?

CodePudding user response：

function shareFile() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const vs = sh.getRange('A2:F'   sh.getLastRow()).getValues();
  vs.forEach(r => {
    let file = DriveApp.getFileById(r[3]);
    if (r[5] === "Viewer") {
      file.addViewer(r[1]);
    } else if (r[5] === "Commentor") {
      file.addCommenter(r[1]);
    } else if (r[5] === "Editor") {
      file.addEditor(r[1]);
    } else {
      SpreadsheetApp.getUi().alert('Permissions must be set to "Viewer", "Commentor", or "Editor". Please check your input. Thank you');
    }
  });
}

CodePudding user response：

Modification points:

• When I saw your sample Spreadsheet of your provided image, in your script, const permissionsData = sheet.getRange('A2:F').getValues(); includes the file ID. I thought that this can be used.
• In your sample Spreadsheet, I thought that it might be required to consider the case that columns "A" to "E" have no values while column "F" has the values.

If these points are reflected in your script, how about the following modification?

Modified script:

function shareFile() {
  const SS = SpreadsheetApp.getActiveSpreadsheet(); // Please set your Spreadsheet.

  const sheet = SS.getSheetByName('Sheet1');
  const permissionsData = sheet.getRange('B2:F'   sheet.getLastRow()).getValues().filter(([b, , d, , f]) => b && d && f);
  permissionsData.forEach(([email, , fileId, , permission]) => {
    let fileToShare;
    try {
      fileToShare = DriveApp.getFileById(fileId);
    } catch (e) {
      console.log("No file ID.");
      return;
    }
    if (permission === "Viewer") {
      fileToShare.addViewer(email);
    } else if (permission === "Commentor") {
      fileToShare.addCommenter(email);
    } else if (permission === "Editor") {
      fileToShare.addEditor(email);
    } else {
      SpreadsheetApp.getUi().alert('Permissions must be set to "Viewer", "Commentor", or "Editor". Please check your input. Thank you');
    }
  });
}

• When this script is run, the values are retrieved from "Sheet1" by filtering the rows. And, the permission of column "F" is added to the files of file IDs of column "D" using the email of column "B".

References:

• filter()
• Destructuring assignment