Recently, I encountered a situation where I was required to consolidate data from multiple sheets in Google Sheets while preserving the order based on the source sheet. Essentially, the objective was to append new data to existing entries while grouping data from each source sheet together.
The specific scenario involved working with several sheets such as Sheet1, Sheet2, Sheet3, and Sheet4. Each of these sheets featured a column for data entries along with a timestamp column indicating when the data was added. On a daily basis, fresh data would be appended to these sheets, and my task was to stack this information in a combined union sheet.
To illustrate, you can refer to this Picture.
The main goal was to organize the data in such a way that entries from the same source sheet remained intact and any new data was added below the existing records. The query was whether this could be achieved through Google Apps Script or any other techniques available. I would greatly appreciate any advice, guidance, or code snippets provided.
Thank you in advance for your help!
As an attempt to address this issue, I created a Google Apps Script function designed to extract data from each source sheet and consolidate it in a separate union sheet. However, the script encountered challenges in maintaining the order based on the original source sheet.
Below is an excerpt from the 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]);
}
}
});