Cannot Stack Data from Multiple Sheets While Maintaining Source Order (GoogleSheet + Apps Script)

Question

`I have a scenario where I need to stack data from multiple sheets in Google Sheets while maintaining the order based on the source sheet. Essentially, I want to append new data to existing data while keeping the entries from each source sheet together.

Here's the scenario I'm dealing with:

I have multiple sheets (e.g., Sheet1, Sheet2, Sheet3, Sheet4). Each sheet contains a column for data entries and a separate timestamp column to indicate when the data was added. Each day, new data is appended to these sheets, and I want to stack this data in a separate union sheet. For instance, Picture

My goal is to stack the data while keeping entries from the same source sheet together and append new data below the existing data.

Is there a way to accomplish this using Google Apps Script or other techniques? Any guidance or code samples would be greatly appreciated.

Thank you in advance for your assistance!

I wrote a Google Apps Script function that extracted data from each source sheet and attempted to stack it in a separate union sheet. However, the script wasn't able to maintain the order of entries based on the source sheet. `

Here is my code:

function myFunction() {
  function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var destSheet = ss.getSheetByName("Approver");
  var sourceSheetNames = ["Assessor 1", "Assessor 2", "Assessor 3", "Assessor 4", "Assessor 5", "Assessor 6"];

  var newData = [];
  var prevData = [];
  
  sourceSheetNames.forEach(function(sheetName) {
    var sourceSheet = ss.getSheetByName(sheetName);
    var lastRow = sourceSheet.getLastRow();
    var lastColumn = sourceSheet.getLastColumn();
    var dataRange = sourceSheet.getRange(2, 19, lastRow - 1, 30);
    var dataValues = dataRange.getValues();
    
    // Separate data into newData and prevData based on criteria (e.g., day change)
    for (var i = 0; i < dataValues.length; i++) {
      if (dataValues[i][0] === "AB") {
        newData.push(dataValues[i]);
      } else if (dataValues[i][0] === "A") {
        prevData.push(dataValues[i]);
      }
    }
  });

Answer 1

Recommendation:

Since the data have timestamps, you can instead extract the data from your other sheets, sort the data via the timestamps, then overwrite the data that's on the union sheet.

Let me know if this code works for you:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var destSheet = ss.getSheetByName("Approver");
  var sourceSheetNames = ["Assessor 1", "Assessor 2", "Assessor 3", "Assessor 4", "Assessor 5", "Assessor 6"];

  var sheetData = [];

  sourceSheetNames.forEach(sheetName => {
    var sourceSheet = ss.getSheetByName(sheetName);
    var lastRow = sourceSheet.getLastRow();
    var lastColumn = sourceSheet.getLastColumn();
    var dataRange = sourceSheet.getRange(2, 1, lastRow - 1, lastColumn); //Adjust the range as needed
    var dataValues = dataRange.getValues();
    sheetData.push(dataValues);
  });

  var newData = sheetData.flat().sort((a, b) => a[1] - b[1]); //Sorts data based on date. Adjust the index as needed.
  var destRange = destSheet.getRange(2, 1, newData.length, destSheet.getLastColumn()); //Adjust the range as needed
  destRange.clearContent().setValues(newData);
}

Sample data on all 6 Assessor sheets

End result on the Approver sheet

Reference:

• .sort()

Answer 2

Try this formula in cell A2 of "Approver" sheet:
=query({query({Sheet1!A2:B},"select Col1, Col2 where Col1 is not null");query({Sheet2!A2:B},"select Col1, Col2 where Col1 is not null");query({Sheet3!A2:B},"select Col1, Col2 where Col1 is not null");query({Sheet4!A2:B},"select Col1, Col2 where Col1 is not null")},"select Col1, Col2")

---

SAMPLE