How can I sort multiple ranges by one reference cell that is outside the second range?

Question

I have a roster that has (on sheet 1 ('Roster')): Name, 1st Week of Attendance, 2nd Week of Attendance, and then other non-relevant data.

On the second sheet ('Contact Info' it has Name, a cell that combines the text from 1st week and 2nd week, and then contact data columns (phone, email, address, etc.).

The important note here, for the second sheet, is that columns A and B are formulas that just get the value of 'Roster'!A and 'Roster'!B:C (through a formula that concates) respectively.

'Roster'

'Contact Info'

I would like to be able to sort all the columns on 'Roster' by Column B and have it also move the contact info correctly. Here is the first part of the code I tried:

function onEdit(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const rosterSheet = ss.getSheetByName("Roster")
  const rosterRangeOne = rosterSheet.getRange("A2:J24");
  const rosterRangeTwo = rosterSheet.getRange("A27:J31")
  const contactSheet = ss.getSheetByName("Contact Info")
  const contactRangeOne = contactSheet.getRange("B2:L24");
  rosterRangeOne.sort({column: 2, ascending: true});
  rosterRangeTwo.sort({column: 2, ascending: true});
  contactRangeOne.sort({column: 2, ascending: true});

  ss.toast('Sort complete.');
}

I did give some other code a shot, but it was janky and didn't actually sort the contact info correctly since it would move around the formulas and then be out of order.

The gist is that I want to sort two sheets based on two ranges using only one column as the column to sort by (reference).

Update: Still have made no progress on this. Not sure if it is even possible at this point.

Answer 1

Because the values come from formulas, instead of using Google Apps Script using QUERY or SORT functions.

Regarding QUERY, you will have to use the ORDER BY clause to sort the values.

Related

• Google spreadsheets sorting confusion when using formulas

Answer 2

The sort range for Contact Info doesn't include the name, and consequently, the sort column is the "Phone" field, and the "Name" field gets out of sync with the "Roster Weeks" field.

The solution is to include Column A ("Name") in the sort range.

Change:

• const contactRangeOne = contactSheet.getRange("B2:L24");
  to
• const contactRangeOne = contactSheet.getRange("A2:L24")

---

Sample Data - Contact INFO

Answer 3

are you still working on it? I think maybe you could work with the values of the second sheet as an array with .getValues(). Then you can loop through Col C and D and .setValues according to the Name in Col A.

• Briefly, you could "backup" the values of Sheet 2 in an array, sort Sheet 1, and then accommodate by clearing and setting values in Sheet 2 with a loop and Indexof. How does it sound? I'm not the best at English nor at programming but according to some projects I've made I think it could actually work! Tell me if you want me to write some draft of the code