I have the following script in a Google Spreadsheet:

 /** 
     * Create CSV file of Sheet2
     * Modified script written by Tanaike
     * https://stackoverflow.com/users/7108653/tanaike
     * 
     * Additional Script by AdamD.PE
     * version 13.11.2022.1
     * https://support.google.com/docs/thread/188230855
     */
    
    const date = new Date();
    
    /** Extrai a data de hoje */
    let day = date.getDate();
    let month = date.getMonth()   1;
    let year = date.getFullYear();
    
    
    if (day < 10) {
        day = '0'   day;
    }
    
    if (month < 10) {
        month = `0${month}`;
    }
    
    let currentDate = `${day}-${month}-${year}`;
    
    
    function sheetToCsvModelo0101() {
      var filename = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetName()   "-01"   " - "   currentDate; // CSV file name
      filename = filename   '.csv';
      var ssid=SpreadsheetApp.getActiveSpreadsheet().getId();
      var saveTo = DriveApp.getFileById(ssid).getParents().next().getId();
      
      // Tanaike script to create csv file
      var csv = "";
      var v = SpreadsheetApp
              .getActiveSpreadsheet()
              .getActiveSheet()
              .getRange("A2:AB3")
              .getValues();
      v.forEach(function(e) {
        csv  = e.join(",")   "\n";
      });
    
      var newDoc = DriveApp.createFile(filename, csv, MimeType.CSV);
      var file = DriveApp.getFileById(newDoc.getId());
      DriveApp.getFolderById(saveTo).addFile(file);
      DriveApp.getRootFolder().removeFile(file);
    } 

The spreadsheet can be accessed here: Worksheet Test Script

This script basically creates a .CSV file in the same folder where the worksheet is, using the range defined in .getRange("A2:AB3").

The script is applied to the first button and triggered by it.

The issue is that if the button is pressed using an account other than the one that created the worksheet, the script returns the error Exception: Cannot retrieve the next object: iterator has reached the end.

I've already checked if the other accounts have access to the folder where the spreadsheet is and if they have the right to edit it, apparently everything is ok.

How can I get around this?

My idea is that someone else edits this spreadsheet and generates the .CSV file.

CodePudding user response：

I think that in your script, when the user has the write permission for the folder of DriveApp.getFileById(ssid).getParents(), the script works. But, in your script, there are several modification points.

• addFile(child) and removeFile(child) have already been deprecated. Ref Please be careful about this.
• In your script, the folder object can be directly used for creating the file.

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

Modified script:

In this modification, sheetToCsvModelo0101 is modified.

function sheetToCsvModelo0101() {
  var filename = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetName()   "-01"   " - "   currentDate; // CSV file name
  filename = filename   '.csv';
  var ssid = SpreadsheetApp.getActiveSpreadsheet().getId();

  // I modified below script.
  var folders = DriveApp.getFileById(ssid).getParents();
  var folder;
  if (folders.hasNext()) {
    folder = folders.next();
    var user = Session.getEffectiveUser().getEmail();
    if (!(folder.getOwner().getEmail() == user || folder.getEditors().some(e => e.getEmail() == user))) {
      throw new Error("This user has no write permission for the folder.");
    }
  } else {
    throw new Error("This user has no write permission for the folder.");
  }
  var csv = "";
  var v = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A2:AB3").getValues();
  v.forEach(function (e) {
    csv  = e.join(",")   "\n";
  });
  var newDoc = folder.createFile(filename, csv, MimeType.CSV);
  console.log(newDoc.getId()); // You can see the file ID.
}

• When this script is run, if the user has no write permission to the folder, an error like This user has no write permission for the folder. is shown.
  • From I've already checked if the other accounts have access to the folder where the spreadsheet is and if they have the right to edit it, apparently everything is ok., if an error related to the write permission of the folder, please confirm this again. If the same error occurs even when you confirm the permission again, I think that it is required to consider another issue.

Reference:

• createFile(name, content, mimeType) of Class Folder