addViewer Method Error... Exception: Invalid argument: permission.value-CodePudding

I am trying to build a script that will share a spreadsheet file with all the email addresses found in the B column of another spreadsheet file.

- File A contains all the emails in column B

- File B is the file to be shared

I tried to write the following code by taking the parts from different places. It is working, so I am able to automatically share the file with all the listed emails but it gives an error at the end of the execution

ERROR: Exception: Invalid argument: permission.value myFunction @ code.gs:15

This is the code:

function myFunction() {

const ss =  SpreadsheetApp.openByUrl('File A URL');
  const sheet = ss.getSheetByName('sheet1');
  var lastRow = sheet.getLastRow();
  const range = sheet.getRange("B2:B" lastRow)

  for (var i=2; i <= lastRow - 1; i  ) {
    const range = sheet.getRange(i,2)
       var emailAddress = range.getValues();
      
  if (emailAddress.length > 0) {
    var fileToShare = SpreadsheetApp.openByUrl('File B URL');
    var fileId = fileToShare.getId();
    DriveApp.getFileById(fileId).addViewer(''  emailAddress '');

  }
}
}

I also tried to change the DriveApp.getFileById(fileId).addViewer('' emailAddress ''); with the following but it gives other error related to API permission

Drive.Permissions.insert(
      {
        'role': 'reader',
        'type': 'user',
        'value': emailAddress[i]
      },
      fileToShare.getId(),
      {
        'sendNotificationEmails': 'false'
      });

can anybody help me with this thing. I am not a programmer and I barely understand it when it becomes just a bit complicated

CodePudding user response：

I made some modifications to your code. The first change was to call the sheets by ID. You can get the ID of the file (for both files A and B) instead of the URL. You can get the ID of a file from the file URL, which is the alphanumeric code at the end of the URL:

https://docs.google.com/spreadsheets/d/ID_of_the_file

Also, I use a filter to get the rows with values instead of thelastrow and the for loop. Here is my sample code:

function shareFile() {

  const ss =  SpreadsheetApp.openById('ID_file_Email');
  const sheet = ss.getSheetByName('sheet1');

  //flat will be to turn this array from 2D to 1D array. 

  let rangeEmails = sheet.getRange("B2:B").getValues().flat();
    
    // this will review the range with values only
    // so it will automatically take the last row

  rangeEmails = rangeEmails.filter((element) => {return (element !== '')});
      
  if (rangeEmails.length > 0) {
    DriveApp.getFileById("ID_file_to_share").addViewers(rangeEmails);

  };
}