Auto sorting 2 sheets based on 1 of them











up vote
1
down vote

favorite












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 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 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 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 at 22:57










  • Well, I'm glad you figured it out
    – Tedinoz
    Dec 7 at 23:33















up vote
1
down vote

favorite












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 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 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 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 at 22:57










  • Well, I'm glad you figured it out
    – Tedinoz
    Dec 7 at 23:33













up vote
1
down vote

favorite









up vote
1
down vote

favorite











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 at 19:06









Rubén

9,93043265




9,93043265










asked Nov 22 at 16:13









Jugger

64




64












  • Try Query in Sheet1. It will update as the data in Sheet 3 changes (including sorting).
    – Tedinoz
    Dec 6 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 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 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 at 22:57










  • Well, I'm glad you figured it out
    – Tedinoz
    Dec 7 at 23:33


















  • Try Query in Sheet1. It will update as the data in Sheet 3 changes (including sorting).
    – Tedinoz
    Dec 6 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 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 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 at 22:57










  • Well, I'm glad you figured it out
    – Tedinoz
    Dec 7 at 23:33
















Try Query in Sheet1. It will update as the data in Sheet 3 changes (including sorting).
– Tedinoz
Dec 6 at 9:15




Try Query in Sheet1. It will update as the data in Sheet 3 changes (including sorting).
– Tedinoz
Dec 6 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 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 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 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 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 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 at 22:57












Well, I'm glad you figured it out
– Tedinoz
Dec 7 at 23:33




Well, I'm glad you figured it out
– Tedinoz
Dec 7 at 23:33












1 Answer
1






active

oldest

votes

















up vote
0
down vote



accepted










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',
    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








    up vote
    0
    down vote



    accepted










    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



























      up vote
      0
      down vote



      accepted










      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

























        up vote
        0
        down vote



        accepted







        up vote
        0
        down vote



        accepted






        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 at 23:26

























        answered Dec 7 at 23:08









        Jugger

        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.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • 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

            What visual should I use to simply compare current year value vs last year in Power BI desktop

            How to ignore python UserWarning in pytest?

            Alexandru Averescu