I have several google forms on my website that customers fill in. All of the responses to these different forms have been linked to go to one spreadsheet. I then used APPSCRIPT to merge all responses on one tab called “MasterSheet”.
My issue is this. I am using timestamps as my unique identifier so the script knows when there is a new submission and ONLY appends the new submission from each individual tab into my mastersheet. BUT if 2 submissions came at the same time, the script only picked up one. So I adjusted it in an attempt to pick up all new submissions, but now the script duplicates the data when there is a new submission.
I think the problem in in my
const data
HERE IS FULL SCRIPT:
const data = activeSheet.getRange(2, 1, lastRow - 1, activeSheet.getLastColumn()).getValues();
const newData = data.filter(row => !masterTimestampsAndEmails.has(row[0] + row[1])); // Check Timestamp + Email combo
if (newData.length > 0) {
// Add the tab name to the "GUIDE REQUESTED" column
const headers = masterSheet.getRange(1, 1, 1, masterSheet.getLastColumn()).getValues()[0];
const guideRequestedIndex = headers.indexOf('GUIDE REQUESTED (DO NOT CHANGE)');
if (guideRequestedIndex === -1) {
throw new Error('GUIDE REQUESTED column not found in the MasterSheet.');
}
newData.forEach(row => {
row[guideRequestedIndex] = activeSheetName; // Set the GUIDE REQUESTED column to the sheet name
});
// Append only new rows to the MasterSheet
masterSheet.getRange(masterSheet.getLastRow() + 1, 1, newData.length, newData[0].length).setValues(newData);
Logger.log(`Appended ${newData.length} rows from sheet "${activeSheetName}" to MasterSheet.`);
} else {
Logger.log(`No new data to append from sheet "${activeSheetName}".`);
}
This was my original scrip which worked BEAUTIFULLY, but unfortunately if 2 form submissions
const data = activeSheet.getRange(lastRow, 1, 1, activeSheet.getLastColumn()).getValues();
const newData = data.filter(row => !masterTimestamps.has(row[0].toString().trim()));
if (newData.length > 0) {
newData.forEach(row => {
// Update state/province based on country
const country = row[5]; // Country column
const stateIndex = 6; // "U.S. State / Canada Province" column
if (country !== 'US - United States of America (the)' && country !== 'CA - Canada') {
row[stateIndex] = 'Not applicable (located outside of the United States and Canada)';
}
// Set the GUIDE REQUESTED column to the active sheet name
const guideRequestedIndex = 14; // Adjust if the column position changes
row[guideRequestedIndex] = activeSheetName;
});
masterSheet.getRange(masterSheet.getLastRow() + 1, 1, newData.length, newData[0].length).setValues(newData);
Logger.log(`Appended 1 row from sheet "${activeSheetName}" to MasterSheet.`);
} else {
Logger.log(`No new data to append from sheet "${activeSheetName}".`);
}