Auto sorting 2 sheets based on 1 of them

Question

I'm running a spreadsheet which contains multiple sheets, in Sheet3 I'm inputting some data and running an auto sorting code, which sorts it ascending by column D.

Sheet3 Example | Sheet1 Example

The "name" and "location" in Sheet1 are imported from Sheet3 so they swap position when Sheet3 does the sorting, however, the problem is that the info from D to F (Sheet1) isn't swapping and it will display for wrong people.

This is the script I'm using:
Modified it slightly to work for a specific sheet, since I didn't need to auto sort the whole document at the time.

/*
 * @author Mike Branski (@mikebranski)
 * @link https://gist.github.com/mikebranski/285b60aa5ec3da8638e5
 */

var SORT_COLUMN_INDEX = 4;
var ASCENDING = true;
var NUMBER_OF_HEADER_ROWS = 2;
var SHEET_NAME = 'Sheet3';
var activeSheet;

function autoSort(sheet) {
  var s = SpreadsheetApp.getActiveSheet();
  if (s.getName() == SHEET_NAME) {
    var range = sheet.getDataRange();
    if (NUMBER_OF_HEADER_ROWS > 0) {
      range = range.offset(NUMBER_OF_HEADER_ROWS, 0, (range.getNumRows() - NUMBER_OF_HEADER_ROWS));
    }

    range.sort( {
      column: SORT_COLUMN_INDEX,
      ascending: ASCENDING
    } );
  }
}

function onEdit(event) {
  var s = SpreadsheetApp.getActiveSheet();
  if (s.getName() == SHEET_NAME) {
    var editedCell;
    activeSheet = SpreadsheetApp.getActiveSheet(); 
    editedCell = activeSheet.getActiveCell(); 
    if (editedCell.getColumn() == SORT_COLUMN_INDEX) {
      autoSort(activeSheet);
    }
  }
}

function onOpen(event) {
  var s = SpreadsheetApp.getActiveSheet();
  if (s.getName() == SHEET_NAME) {
    activeSheet = SpreadsheetApp.getActiveSheet();
    autoSort(activeSheet);
  }
}

function onInstall(event) {
  onOpen(event);
}

So basically when I edit Sheet3 and it does the auto sorting, I want the rows from D to F in Sheet1 to carry along with repositioning that comes from Sheet3. I hope I did manage to explain properly what I want.

I've tried without success to make it work; I can't figure out the proper way of doing this, especially due to the fact that Sheet1 table has different range.

Try Query in Sheet1. It will update as the data in Sheet 3 changes (including sorting). — Tedinoz
Just for clarification... why have you shown us the "autosort" code that sorts Sheet3 when the problem is actually displaying data on Sheet1? — Tedinoz
@Tedinoz I tried with Query before but I still didn't manage to achieve what I wanted. I'll try to explain again using different ranges. So basically Sheet3 is a master sheet which contains a list of people in column A, and location in column B, the next columns up to column M contain various info about each individual. The autosorting is done alphabetically based on location (column B) on edit. Now Sheet1's A and B columns are basically imported from Sheet3, and from column C onwards users input data manually for themselves. — Jugger
(continuing previous comment) Now that's fine for columns A & B, they update whenever sorting in Sheet3 occurs, but from C onwards it won't carry along, so all the data introduced by users won't correspond anymore to their name/location. I posted the script because I was expecting to get help 'upgrading' it to do what I want. However, I managed to figure out a way of doing this but I was kinda busy and I didn't get around to post the answer here. — Jugger

Jugger Jugger · Accepted Answer · 2018-12-07T23:08:53

I figured out how to fix the issue so I'll post the code here. Basically whenever you edit the column that you choose to sort by in Sheet3 (master sheet) it will first copy in the Sheet1 (target sheet) what changes you've made in A & B columns and then it will sort both sheets at the same time, this way the data from following columns in Sheet1 will carry along.

I used A & B columns in this example, since that's what I commented above, but can be different ranges as long as they're similar in size.

// Master Sheet Settings (Copy ranges must be similar in size)
var msName = 'Master Sheet';
var msSortCol = 4;  // which column to trigger the sorting when you edit
var msSkipRows = 6; // how many rows to skip, if you have header rows
var msCopyRange = 'A7:B51'; // the range you want to copy

// Target Sheet Settings
var tsSortCol = 3;
var tsSkipRows = 10;
var tsName = 'Target Sheet'; 
var tsCopyRange = 'A11:B55';

var sortAscending = true;
var activeSheet;

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var editedCell = ss.getActiveRange().getColumnIndex();

  if (ss.getSheetName() == msName) {
    activeSheet = SpreadsheetApp.getActiveSheet();
  if (editedCell == msSortCol) {
    copyRow();
    autoSort(activeSheet);
    }
  } 
}

function copyRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(msName);
  var values = sheet.getRange(msCopyRange).getValues();
  ss.getSheetByName(tsName).getRange(tsCopyRange).setValues(values);
  SpreadsheetApp.flush();
}

function autoSort() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var msheet = ss.getSheetByName(msName);
  var tsheet = ss.getSheetByName(tsName);
  var mrange = msheet.getDataRange();
  var trange = tsheet.getDataRange();

  if (ss.getSheetName() == msName) {
    if (msSkipRows > 0) {
      mrange = mrange.offset(msSkipRows, 0, (mrange.getNumRows() - msSkipRows));
    } 
    if (tsSkipRows > 0) {
      trange = trange.offset(tsSkipRows, 0, (trange.getNumRows() - tsSkipRows));
    }

    mrange.sort({ column: msSortCol, ascending: sortAscending });
    trange.sort({ column: tsSortCol, ascending: sortAscending });
  } 
}

Auto sorting 2 sheets based on 1 of them

1 Answers