Google Sheets Print All IDs in one click-CodePudding

I'm working on a student information template and I'm wondering if it's possible to print all of the data for each student in one go? I used data validation on my spreadsheet to modify the student ID so that their data will be easily view and print. Because I have to print it one by one for each pupil, this is a time-consuming process so I come up with this kind of flow to save time. Is it possible?

Please see the sample

CodePudding user response：

I believe your goal is as follows.

You want to reduce the cost of printing the data from the Spreadsheet.

I thought that when the Spreadsheet is printed out using Google Apps Script, the Google Cloud Print can achieve this. But I thought that in this case, the settings might be a bit complicated. So, in this case, I would like to propose a workaround. How about the following flow?

Retrieve the values from the source sheet and create the output values as the array.
Create a new Spreadsheet and put each value on each page.
Print out the Spreadsheet.

By this flow, you can print out all pages by one manual process.

The sample script is as follows.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet. And run myFunction.

function myFunction() {
  const rowHeader = ["STUD ID", "LAST NAME", "FIRST NAME", "MIDDLE NAME", "NAME EXTENSION"];
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DATA");
  const [header, ...values] = sheet.getDataRange().getValues();
  const ar = values.map(r => header.reduce((o, h, i) => Object.assign(o, { [h.toUpperCase()]: r[i] }), {}));
  const newValues = ar.map(e => [rowHeader, ...[rowHeader.map(f => e[f])]]).map(e => e[0].map((_, c) => e.map(r => r[c])));
  const ss = SpreadsheetApp.create("tempSpreadsheet");
  newValues.forEach((v, i) => (i == 0 ? ss.getSheets()[0] : ss.insertSheet()).getRange(1, 1, v.length, v[0].length).setValues(v));
}

When you run this script, a new Spreadsheet of tempSpreadsheet is created to the root folder. When you open it, you can see the expected values for each worksheet. By this, you can print out them.

Note:

This sample script is prepared from your sample Spreadsheet. So when your sample Spreadsheet is different from your actual situation, this sample script might not be able to be used. Please be careful about this.

Sample script:

Note:

References: