Code is pasted below. Summary:
- Search for the searchKey (PUT) in column P on “NWSS Sidecar” sheet
- When found, get the corresponding sample ID# from column E
- If the sample ID# is not paired up, (doesn’t have a PUT entry in the directly adjacent row [the row numbers have a difference of 1]), do nothing
- If ID#s are adjacent to each other (difference of 1 in the row numbers), search “PUT PCR Data” sheet for the larger number of the pair.
- If the larger number is found on the “PUT PCR Data” sheet, replace column B’s data on the “NWSS Sidecar Sheet” with the corresponding value in column B from the “DUN PCR Data” sheet. Then, delete the row which contains the smaller number of that pair.
- If greater number is not found, paste greater sample ID# into the last row of column A on “PUT PCR Data” sheet. Then, search the “ENTRY SHEET” sheet for the greater number in column A.
- Once found, take the average of the values in the greater number’s row and the one above it in column B (which corresponds to the first PUT sample with a smaller ID. Then, paste that value into column B of the “DUN PCR Data” sheet. After that, find the average of the values in column C on the same two rows as were used for column B. Then, paste that average into column C on “DUN PCR Data” sheet. After that, find the average of the values in column D on the same two rows as were used for column B. Then, paste that average into column D on “PUT PCR Data” sheet. Then, paste the date from cell H2 on “ENTRY SHEET” sheet into column E. After that, replace column B’s data on the “NWSS Sidecar Sheet” with the corresponding value in column B from the “DUN PCR Data” sheet. Then, delete the row which contains the smaller number of that pair.
function nWSSPUTReplicateResolution() {
Logger.log("Resolving Putnam samples");
var ss = SpreadsheetApp.openByUrl('');
var sidecarSheet = ss.getSheetByName('NWSS Sidecar');
var putSheet = ss.getSheetByName('PUT PCR Data');
var entrySheet = ss.getSheetByName('ENTRY SHEET');
var putData = putSheet.getDataRange().getValues();
var sidecarData = sidecarSheet.getDataRange().getValues();
var searchkey = 'PUT'
for (var i = sidecarData.length - 1; [i] >= 0; i--) {
var row = sidecarData[i];
var putID = row[4];
var putIndex = i;
if (row[15] == searchkey && putID != '') {
var adjacentRow = sidecarData[i - 1];
Logger.log('Adjacent row: ' + adjacentRow);
if (adjacentRow[4] != '' && adjacentRow[4] == putID + 1) {
var largerputID = Math.max(putID, adjacentRow[4]);
Logger.log('larger PUT ID found on sheet:'+largerputID);
for (var j = 1; j < putData.length; j++) {
var putDataRow = putData[j];
if (putDataRow[0] == largerputID) {
sidecarSheet.getRange(putIndex + 1, 2).setValue(putDataRow[1]);
if (putID < adjacentRow[4]) {
sidecarSheet.deleteRow(putIndex + 1);
} else {
sidecarSheet.deleteRow(putIndex);
}
break;
} else if (j == putData.length - 1) {
putSheet.appendRow([largerputID, '', '', '', '', '', '', '']);
for (var k = 1; k < entrySheet.getLastRow(); k++) {
var entryRow = entrySheet.getRange(k, 1, 1, 8).getValues()[0];
if (entryRow[0] == largerputID) {
var n1Average = (entryRow[1] + entrySheet.getRange(k - 1, 2).getValue()) / 2;
var n2Average = (entryRow[2] + entrySheet.getRange(k - 1, 3).getValue()) / 2;
var rPAverage = (entryRow[3] + entrySheet.getRange(k - 1, 4).getValue()) / 2;
var testResultDate = entrySheet.getRange('H2').getValue();
var correctionFactor = entrySheet.getRange('H1').getValue();
var putAvgN1wCF = (n1Average * correctionFactor);
var putAvgN2wCF = (n2Average * correctionFactor);
putSheet.getRange(putSheet.getLastRow(), 2).setValue(n1Average);
putSheet.getRange(putSheet.getLastRow(), 3).setValue(n2Average);
putSheet.getRange(putSheet.getLastRow(), 4).setValue(rPAverage);
putSheet.getRange(putSheet.getLastRow(), 5).setValue(testResultDate);
putSheet.getRange(putSheet.getLastRow(), 6).setValue(putAvgN1wCF);
putSheet.getRange(putSheet.getLastRow(), 7).setValue(putAvgN2wCF);
sidecarSheet.getRange(putIndex + 1, 2).setValue(putSheet.getRange(putSheet.getLastRow(), 2).getValue());
if (putID < adjacentRow[4]) {
sidecarSheet.deleteRow(putIndex + 1);
} else {
sidecarSheet.deleteRow(putIndex);
}
break;
}
}
}
}
}
}
}
Logger.log("Putnam samples resolved");
}
When having the code handle samples which are new (and therefore do not have the larger number in the PUT PCR Data sheet), the code will log the Sample ID# 1 BELOW the actual desired target, delete the desired sample, then delete the first sample of the pair as well.
Initially, the code was written to start from the top and then go down, but that is ill advised when deleting rows, so I switched the ++ in the initial loop to -- and adjusted so that it would start at the bottom. Other than that, I'm just lost since this is the most complicated logic ladder I've done and I still am shaky on using loops.