1

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'

actual spreadsheet sheet1

'Contact Info'

actual spreadsheet sheet2

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.

3 Answers 3

0

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

1
  • I don't think this is what I want. I do not want to have one sheet where I update the contact info, and then another sheet that has it sorted. The goal is to have it how it is right now, just with an apps script to auto-sort the data in-place
    – ZachTheDev
    Apr 3 at 15:29
0

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

sample

0

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
1
  • If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. - From Review Oct 25 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.