Auto sorting 2 sheets based on 1 of them












1















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.










share|improve this question

























  • Try Query in Sheet1. It will update as the data in Sheet 3 changes (including sorting).

    – Tedinoz
    Dec 6 '18 at 9:15











  • Just for clarification... why have you shown us the "autosort" code that sorts Sheet3 when the problem is actually displaying data on Sheet1?

    – Tedinoz
    Dec 6 '18 at 22:31











  • @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
    Dec 7 '18 at 22:53











  • (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
    Dec 7 '18 at 22:57











  • Well, I'm glad you figured it out

    – Tedinoz
    Dec 7 '18 at 23:33
















1















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.










share|improve this question

























  • Try Query in Sheet1. It will update as the data in Sheet 3 changes (including sorting).

    – Tedinoz
    Dec 6 '18 at 9:15











  • Just for clarification... why have you shown us the "autosort" code that sorts Sheet3 when the problem is actually displaying data on Sheet1?

    – Tedinoz
    Dec 6 '18 at 22:31











  • @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
    Dec 7 '18 at 22:53











  • (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
    Dec 7 '18 at 22:57











  • Well, I'm glad you figured it out

    – Tedinoz
    Dec 7 '18 at 23:33














1












1








1


0






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.










share|improve this 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.







sorting google-apps-script google-sheets






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 22 '18 at 19:06









Rubén

10.6k43467




10.6k43467










asked Nov 22 '18 at 16:13









JuggerJugger

64




64













  • Try Query in Sheet1. It will update as the data in Sheet 3 changes (including sorting).

    – Tedinoz
    Dec 6 '18 at 9:15











  • Just for clarification... why have you shown us the "autosort" code that sorts Sheet3 when the problem is actually displaying data on Sheet1?

    – Tedinoz
    Dec 6 '18 at 22:31











  • @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
    Dec 7 '18 at 22:53











  • (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
    Dec 7 '18 at 22:57











  • Well, I'm glad you figured it out

    – Tedinoz
    Dec 7 '18 at 23:33



















  • Try Query in Sheet1. It will update as the data in Sheet 3 changes (including sorting).

    – Tedinoz
    Dec 6 '18 at 9:15











  • Just for clarification... why have you shown us the "autosort" code that sorts Sheet3 when the problem is actually displaying data on Sheet1?

    – Tedinoz
    Dec 6 '18 at 22:31











  • @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
    Dec 7 '18 at 22:53











  • (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
    Dec 7 '18 at 22:57











  • Well, I'm glad you figured it out

    – Tedinoz
    Dec 7 '18 at 23:33

















Try Query in Sheet1. It will update as the data in Sheet 3 changes (including sorting).

– Tedinoz
Dec 6 '18 at 9:15





Try Query in Sheet1. It will update as the data in Sheet 3 changes (including sorting).

– Tedinoz
Dec 6 '18 at 9:15













Just for clarification... why have you shown us the "autosort" code that sorts Sheet3 when the problem is actually displaying data on Sheet1?

– Tedinoz
Dec 6 '18 at 22:31





Just for clarification... why have you shown us the "autosort" code that sorts Sheet3 when the problem is actually displaying data on Sheet1?

– Tedinoz
Dec 6 '18 at 22:31













@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
Dec 7 '18 at 22:53





@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
Dec 7 '18 at 22:53













(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
Dec 7 '18 at 22:57





(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
Dec 7 '18 at 22:57













Well, I'm glad you figured it out

– Tedinoz
Dec 7 '18 at 23:33





Well, I'm glad you figured it out

– Tedinoz
Dec 7 '18 at 23:33












1 Answer
1






active

oldest

votes


















0














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





share|improve this answer

























    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53434815%2fauto-sorting-2-sheets-based-on-1-of-them%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














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





    share|improve this answer






























      0














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





      share|improve this answer




























        0












        0








        0







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





        share|improve this answer















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






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Dec 8 '18 at 23:26

























        answered Dec 7 '18 at 23:08









        JuggerJugger

        64




        64






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53434815%2fauto-sorting-2-sheets-based-on-1-of-them%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            404 Error Contact Form 7 ajax form submitting

            How to know if a Active Directory user can login interactively

            TypeError: fit_transform() missing 1 required positional argument: 'X'