Qfyilyi

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.

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 });

  } 

}