1

I have a spreadsheet that I want to create about 100 copies of at once, all copies with different titles. I found a script that allows me to do that, all I have to do is fill out information from cell A8 downward on this spreadsheet and run the script to make the 100 copies at once.

The Script is the following: 

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Options')
      .addItem('Make copy for list below','copyfilefromsource')
      //.addItem('Hide data sheet from students','hidesheet')
  .addToUi();
}



function copyfilefromsource() {
 var ui = SpreadsheetApp.getUi();
  
  var sheet_merge = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List");
  var last_row = sheet_merge.getLastRow();
  
   var newsheet_firstname = null;
  var newsheet_surname = null;
  var newsheet_email = null;
  var source_folder = null;
  var file_owner = null;
  var dest_folder = null;
  var sheets_created = 0;
  var new_file = null;
  var source_file = null;
  var student_id = null;
  var google_domain = null;
  var range = sheet_merge.getRange(1, 1, last_row, 4);
  var temp = null;
  
  source_file = DriveApp.getFileById(range.getCell(1, 2).getValue()); //gets the source file to copy  
  dest_folder = DriveApp.getFolderById(range.getCell(2, 2).getValue()); //gets the ID of the folder to place the copied files into
    file_owner = range.getCell(3, 2).getValue();  //person to own the files
  file_editor = range.getCell(4, 2).getValue();  //person to edit the files
    google_domain = range.getCell(5, 2).getValue();  //extension of the email address 
  
  if (last_row <= 7) {
    SpreadsheetApp.getUi().alert("No rows to process!");
    return
  }
  
  for (var i = 8; i <= last_row; i++) { // loop to go down evenery row from 7 until the end
    if (range.getCell(i, 4).getValue() == '') {   //makes sure there is nothing in the column, i.e. can run the script with some students already been processed if it fails half way through.
    student_id = range.getCell(i, 1).getValue();  //take the first column for the     
    newsheet_email =  student_id + google_domain;  //combines with username with the domain; 
    // should add some user checking code here!!!!! and only run the below if user exists (future improvement)
    
    newsheet_firstname = range.getCell(i, 2).getValue(); //gets first name from the sheet
    newsheet_surname = range.getCell(i, 3).getValue(); //gets surname from the sheet
           
    new_file = source_file.makeCopy(source_file.getName() + " | " + student_id, dest_folder);
    new_file.setOwner(file_owner);  //allocates an owner to the sheet
    new_file.addEditor(file_editor); //allocates an editor to the sheet
    new_file.addViewer(newsheet_email); //allocates a viewer to the sheet
    //new_file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW); //adds anyone with the link as a viewer
    
    //source_folder.removeFile(new_file); // hides from root folder  not required as going directly to correrrect folder
   // new_file.setActiveSheet('My Grades');
    //new_file.getRange(2,3).setValue(student_id);
    
    //change the a cell in the copied spreadsheet to the student ID 
    //var ss = SpreadsheetApp.openById(new_file.getId());
    //var sheet = ss.getSheets()[0]; // "access data on different tabs"
    //var sheet = ss.getSheetByName('My Grades');
     // ss.setActiveSheet(sheet);
   // ss.getRange('C2').setValue(student_id);
    
    //Add the details that the process has worked
    SpreadsheetApp.getActiveSheet().getRange(i,1).setFormula('=HYPERLINK("' + new_file.getUrl() +'/","'+student_id+'")');
    SpreadsheetApp.getActiveSheet().getRange(i,4).setValue(new_file.getId());
    
    sheets_created++; // add to spreadsheets created
    }
  }
  ui.alert(sheets_created + " files were created!")
}






function testFolder(folderName){
  var exist = true;
  try{var testFolder = DocsList.getFolder(folderName)}
  catch(err){exist=false}
  return exist;
}

The problems that I have ran into and need help with are:

  1. I have protected ranges on my original spreadsheet and when I make the mass copies using the script, those protected ranges remain protected. However, the editor no longer has editing access to the protected ranges. Is there a way to allow the editor to have editing access to protected ranges of the spreadsheet?

  2. In the original spreadsheet I have disabled the option to make a copy, download, or print (see image below), but when I make the mass copies using the script, they are able to copy, download, or print. I do not want the viewer to have any of these options. Is there a way to keep the same settings that are present in the original spreadsheet? enter image description here

Improve this question
0

0

Reset to default