2

I have a list of students assigned to various test rooms, and each room has a different test. (Although there are duplicates.) I would like to create a separate sheet for each test room where the name of the sheet includes both the test name and room number. I found a Youtube video that showed me how to create a script to make multiple filtered sheets from a master sheet and name each sheet based on the filter criteria. When I used the script for myself I used the room numbers as the filter criteria, but I would like to know how I could adjust the script so that when it is naming each new sheet it includes the test name as well as the room number.

I have tried simply adding a column in the master list that concatenates the desired info and reading from there, and that works, but I am hoping there is a way to do it in the script directly. (That an absolute novice like me can understand, lol.)

Here is the script I have been using (for a similar set of data, but without test name):

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceWS = ss.getSheetByName ("PSAT9");

  const testLocations = sourceWS
    .getRange(2, 6, sourceWS.getLastRow()-1,1)
    .getValues()
    .map(tr => tr[0]);

  const uniqueTestLocations = [...new Set(testLocations)];
  const currentSheetNames = ss.getSheets().map(s => s.getName());

  let was;

  uniqueTestLocations.forEach(testRoom => {
    if (!currentSheetNames.includes(testRoom)){
      ws = null;
      ws = ss.insertSheet ();
      ws.setName(testRoom);
      ws.getRange("A2").setFormula(`=SORT(FILTER(PSAT9!A2:J, PSAT9!F2:F="${testRoom}"),4,1)`);

      sourceWS.getRange("A1:J1").copyTo(ws.getRange("A1:J1"));
    }
  }) 
}
0

1 Answer 1

1

Assuming that your room is in column 6 (F) and your location is in column 7 (G) you need only make some tweaks to the constant testLocations:

  1. expand its range to include 2 columns instead of 1
  2. Join the values from the two columns. I delimited with a dash - but you can tweak as you wish:
const testLocations = sourceWS
    .getRange(2, 6, sourceWS.getLastRow()-1, 2)
    .getValues()
    .map(tr => tr.join('-'));

Trigger from Sheet

Add this bit of script at the end of your script and it will install a menu item so you can run this from the sheet.

function onOpen() {
  SpreadsheetApp.getActive().addMenu(
    // "⚙️ Custom Menu",
    "⚙️",
    [{ name: "Sync Sheet Names and Data",
       functionName: "myFunction" }]
  );
}

Sync sheet names and data button

1
  • 1
    That worked very well, thank you so much! Apr 4 at 16:46

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.