Google Apps Script for gradebook sharing: Create duplicates of current sheet, one per row of data, with importrange of the source and header rows

Question

The goal is to create a workaround to the typical problem of not having per-row privileges. I want students to only see their row of the grading document. Typical solution is to share another sheet with importrange of that row.

Manually this would look like this

1. Right click sheet and copy to new workbook. This maintains the styles and conditional formatting as well as dropdowns and so forth. Rename it with the name of the source tab and the students' name from the first column of the relevant row (note here that regex might be necessary to get rid of commas)
2. Clear all values from cells, including dropdowns
3. In A1, Import 1:1 so that minor updates to headers also sync to student's document
4. In A2, Import #:# # being the row that the student is on
5. Approve access to the original document
6. Share with student, allowing them to comment but not edit so that they cannot change formula to see others

Source sheet containing all students where I would do the grades/comments and equipment checkouts: https://docs.google.com/spreadsheets/d/1uk4pGtB1D1QE5asKIJoyKPtpJimPIH1jRrSDFfgeOOM/edit?usp=sharing

Example of a sheet that I would want the script to generate: https://docs.google.com/spreadsheets/d/1ig2CBKq-WDkTa1AiyGqB1VIF2cr7Jj42ZFeVfDbe_xM/edit?usp=sharing

Here is what I was able to write so far. I have a couple questions

1. The clear() function is leaving behind blank dropdowns on the copy of the template. When manually clearing dropdowns, I can hit delete twice to remove them without clearing colors etc. First backspace clears dropdown of selected value, second backspace clears drodowns themselves from cells. What is the script equivalent of this?

2. How could I add a link to the newly created document to the end of row it was created for in the source file?

function exportData() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var sheetName = sheet.getName();
  var range = sheet.getDataRange();
  var values = range.getValues();
  var header = values.shift();
  var ssId = spreadsheet.getId();

  values.forEach((row, index) => {
    var newName = sheetName + " - " +`${row[0]}`;
    var files = DriveApp.getFilesByName(newName);
    // file is already existing
    if (files.hasNext()) 
      return;
    
    // Make new spreadsheet with the name set above
    var newSpreadsheet = SpreadsheetApp.create(newName);


    // Copy source sheet over to carry conditional formatting.
    var copiedSheet = SpreadsheetApp.getActiveSheet().copyTo(newSpreadsheet);
    copiedSheet.activate();
    var newSheet = newSpreadsheet.getActiveSheet();

    // Delete Sheet1
    var scrapSheet = newSpreadsheet.getSheetByName('Sheet1');
    newSpreadsheet.deleteSheet(scrapSheet);
    
    // Clear copied data to leave behind formatting
    newSheet.clear({ formatOnly: false, contentsOnly: true });

    var rowNum = index + 2;
    var newSsId = newSpreadsheet.getId();
    // append header to new file's first row
    newSheet.appendRow(header)
    // use importrange on A2
    newSheet.getRange(2, 1).setFormula(`=IMPORTRANGE("${spreadsheet.getUrl()}", "${sheet.getSheetName()}!A${rowNum}:${rowNum}")`);
    // add permission to automatically allow access to importrange
    addImportrangePermission(ssId, newSpreadsheet.getId());
    // add the email in the third column as the viewer
    DriveApp.getFileById(newSsId).addCommenter(row[1]);
  });
}

function addImportrangePermission(donorId, ssId) {
  // adding permission by fetching this url
  const url = `https://docs.google.com/spreadsheets/d/${ssId}/externaldata/addimportrangepermissions?donorDocId=${donorId}`;
  const token = ScriptApp.getOAuthToken();
  const params = {
    method: 'post',
    headers: {
      Authorization: 'Bearer ' + token,
    },
    muteHttpExceptions: true
  };
  
  UrlFetchApp.fetch(url, params);
}

Answer 1

From the question

2. How could I add a link to the newly created document to the end of row it was created for in the source file?

The code has the following lines

// Make new spreadsheet with the name set above
var newSpreadsheet = SpreadsheetApp.create(newName);

The variable newSpreadsheet already contains the spreadsheet created by the code, after the above code, you could insert the following sentence:

var url = newSpreadsheet.getUrl();

To add the URL to the row, after the above sentence, you could insert the following sentence:

// Write the new spreadsheet URL to the right of the last row's cell
sheet.getRange(index + 1,sheet.getLastColumn() + 1).setValue(url);

Google Sheets automatically will convert the string value into a link. If you want to display the spreadsheet title, instead of the URL.

// Write a formula to add a link with the spreadsheet name and URL to the right of the last row's cell
sheet.getRange(index + 1,sheet.getLastColumn() + 1).setFormula(`HYPERLINK(${newName},${url})`);

Another option is to add the link as Rich Text.

// Create a Rich Text Object
var richValue = SpreadsheetApp.newRichTextValue()
      .setText(newName)
      .setLinkUrl(url)
      .build();
// Write the Rich Text Object to the right of the last row's cell
sheet.getRange(index + 1,sheet.getLastColumn() + 1).setRichTextValue(richValue);

Answer 2

From the question

1. The clear() function is leaving behind blank dropdowns on the copy of the template. When manually clearing dropdowns, I can hit delete twice to remove them without clearing colors etc. First backspace clears dropdown of selected value, second backspace clears drodowns themselves from cells. What is the script equivalent of this?

In-cell dropdowns are part of a feature called Data Validation. The code to remove the data validation of the active range is

SpreadsheetApp.getActiveRange().setDataValidation(null);

Note: The behavior described here occurs when the dropdown is a smartchip. When the data validation uses the Display style an Arrow instead of Chip, the Arrow isn't removed by pressing the Delete key multiple times.