-1

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);
}
5
  • Holy, I found someone who gave it a shot, will investigate further but could help anyone helping me out get a kickstart stackoverflow.com/questions/72093967/… Commented Oct 9, 2023 at 4:05
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Commented Oct 9, 2023 at 4:27
  • Welcome to Web Applications SE. Posts should be about a specific question. Because of this, a post typically will include a single question sentence. Also, questions should be self-contained, meaning that while links to spreadsheets might be helpful, the question should include all the relevant details to be answered without following the links as the linked spreadsheet might not be changed or could not be longer available for future readers. Commented Oct 9, 2023 at 5:16
  • I rolled back the question to the previous rev. because follow-up questions derived from answers to a question like this should be posted as new questions. Editing a post should done to clarify it, or when the question has not gotten a helpful answer, to add progress updates about the efforts done by the OP to solve the problem or the continued research efforts. You might want to create a webpage about your project to include a list of all the questions related to your project. The options are many, it could be a document in Google Docs, a site in Google Sites, a blog in Blogger, ... Commented Oct 9, 2023 at 15:35
  • Will do, sorry for the hassle, and thank you for the clarification. Commented Oct 9, 2023 at 17:45

2 Answers 2

0

From the question

  1. 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);
1
  • Thank you Rubén I really appreciate your help. I edited my question to reflect what is solved and what I specifically needed help with. I apologize for the broadness earlier. Commented Oct 9, 2023 at 15:07
0

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.

1
  • Thank you Rubén I really appreciate your help. I edited my question to reflect what is solved and what I specifically needed help with. I apologize for the broadness earlier. Commented Oct 9, 2023 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.