I have a Google Form that saves data into a Google Sheet. The form is completed by students in an online class.

Additional data is periodically added to each row of the Google Sheets by their teacher to track their attendance. I'd like to be able to share a read-only mirror of the data with each student such that they can only see their own data. (All data from a given student is in a single row of the worksheet).

I know that could do this manually by creating a new sheet for each student, using =IMPORTRANGE() to mirror their row of data into the sheet, and share a link to the sheet with the student. However, this isn't feasible given the number of sheets that would need to be manually created.

Is there a way to automate sharing of a limited section of the data with people?

CodePudding user response：

This script will create a spreadsheet per row and will have its first row equate to the first row of the original spreadsheet (header) and use IMPORTRANGE on its 2nd row. It will then add the email in that row as viewer to the newly created spreadsheet.

Script:

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

  values.forEach((row, index) => {
    var newName = `Data: ${row[2]}, ${row[1]}`;
    var files = DriveApp.getFilesByName(newName);
    // file is already existing
    if (files.hasNext()) 
      return;

    var newSpreadsheet = SpreadsheetApp.create(newName);
    var newSheet = newSpreadsheet.getActiveSheet();
    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).addViewer(row[5]);
  });
}

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);
}

Original spreadsheet:

Sample Output:

Sample new spreadsheet content:

Note:

• Script is updated based on your sample data
• addViewer will fail if email is invalid so be sure to use one. Be sure to adjust the column passed to row to avoid failing.