I want a script to insert a student ID number in cell B5, and then download & export the sheet as a-CodePudding

I have a Student Progress Report in Google Sheets that populates data (using queries, filters, vlookups, sparklines, etc.) based on a unique student ID that is entered in cell B5. This works great for me as a teacher to show to student individually on the screen. However, my school wants to use the report to communicate to all students and parents.

This would pretty much be logistically impossible for a thousand students. Is there a way to write a script that would automatically enter a student ID in B5, download it as a PDF and name the file with the student ID number? And then repeat the process for the next student ID?

I've been working so hard to make this work and am hitting a brick wall now in terms of how to share this information with students and parents (while protecting privacy, etc. of whole-school data that is found in hidden sheets). I know my way around formulas, even more "advanced" ones, but writing scripts is not something I can do yet. If I can find one that is close to what I want to accomplish, I know enough to make small customizations, but I'm really lost on this one. I'd be so appreciative if anyone had any ideas or could help me!

CodePudding user response：

Here is a draft

function saveAllAsPDF(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh1 = ss.getSheetByName('IMPORT Masterlist');       
  const sh2 = ss.getSheetByName('Student Progress Report');            
  sh1.getRange('A2:A' sh1.getLastRow()).getValues().flat().forEach(id => {
    if (id != '') {
      sh2.getRange('B5').setValue(id)
      SpreadsheetApp.flush();
      savePDF()
    }
  })
}

function savePDF(){
  const folderID = '1N0AIh5oEszYw_NXKD0Vx1XrXUiPuUadR';
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName('Student Progress Report');      
  const d = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd")
  const filename = sh.getRange('B5').getValue()   '_'   d   ".pdf"
  const url = 'https://docs.google.com/spreadsheets/d/'   ss.getId()   '/export?';
  const exportOptions =
    'exportFormat=pdf&format=pdf'   
    '&size=A4'   
    '&portrait=true'                       
    '&fitw=true'                          
    '&sheetnames=false&printtitle=false'   
    '&pagenumbers=false&gridlines=false'   
    '&fzr=false'  
    '&gid='   sh.getSheetId();
  var params = {method:"GET",headers:{"authorization":"Bearer "  ScriptApp.getOAuthToken()}};
  var response = UrlFetchApp.fetch(url   exportOptions, params).getBlob();
  DriveApp.getFolderById(folderID).createFile(response.setName(filename));
}