0

In this spreadsheet, I have a sheet called Form Responses 1 that contains the students' data and a sheet called TEMPLATE that contains the template that needs to be printed per student. The problem is that I can only print the spreadsheet one at a time by changing the data in the C2 cell. Is there any way for me to print it quickly and easily to save time and effort?

In Google Cloud Print, I want it to say Page 1 = Student 1, Page 2 = Student 2, Page 3 = Student 3, and so on. Is it possible?

Sample

cjwrk
  • 255
  • 1
  • 13

1 Answers1

1

In your situation, how about creating a new Google Spreadsheet including each page? When this is reflected in a sample script, it becomes as follows.

Sample script:

function myFunction() {
  const srcSs = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = srcSs.getSheetByName("TEMPLATE");
  const values = sheet.getRange("C2").getDataValidation().getCriteriaValues()[0].getValues().flat().filter(String);
  const dstSs = SpreadsheetApp.create("tempSpreadsheet");
  values.forEach(v => {
    sheet.getRange("C2").setValue(v);
    SpreadsheetApp.flush();
    const tempSheet = sheet.copyTo(srcSs);
    const range = tempSheet.getDataRange();
    range.copyTo(range, {contentsOnly: true});
    tempSheet.getRange("B2:2").clear().clearDataValidations();
    tempSheet.getDrawings().forEach(e => e.remove());
    tempSheet.copyTo(dstSs);
    srcSs.deleteSheet(tempSheet);
  });
  dstSs.deleteSheet(dstSs.getSheets()[0]);
}
  • When this script is run, a new Google Spreadsheet of tempSpreadsheet is created to the root folder. When you see it, each tab has each sheet without using the formulas. By this, you can print out the Spreadsheet by one manual process.

Note:

  • When I saw your sample Spreadsheet, /** @OnlyCurrentDoc */ is used in your script. When you use my proposed script, please remove /** @OnlyCurrentDoc */. By this, the scope of https://www.googleapis.com/auth/spreadsheets is automatically detected and used it. Please be careful about this.
  • This sample script is for your sample Spreadsheet. So when the spreadsheet is changed, this script might not be able to be used. Please be careful about this.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • But is it possible to only get the range and not the whole sheet? As per the image above, the row 2 is not visible. – cjwrk Oct 26 '21 at 03:11
  • @cjwrk Thank you for replying. I deeply apologize for the inconvenience. For `But is it possible to only get the range and not the whole sheet? As per the image above, the row 2 is not visible.`, I updated my sample script. Could you please confirm it? I really apologize for my poor skill. – Tanaike Oct 26 '21 at 03:18