The Code:
function UpTrendDetection_1h() {
var currentHigh = SpreadsheetApp.getActive().getRange("1h!M2").getValue()
var currentHH = SpreadsheetApp.getActive().getRange("1h!B16").getValue()
var highDate = SpreadsheetApp.getActive().getRange("1h!L2").getValue()
var lowDate = SpreadsheetApp.getActive().getRange("1h!N2").getValue()
// IF M2 > B16 AND L2 > N2
if ((currentHigh > currentHH) && (highDate > lowDate))
// Copy previous contents of P2:S2 down one row to P3:S3 shift any other cells with values down a row
var newTrendData = SpreadsheetApp.getActive().getRange("1h!L2:O2").getValues()
for (var row = 0, numRows = newTrendData.length; row < numRows; row ) {
var rowContainsData = newTrendData[row].some(function isNonBlanky_(element, index, array) {
return element !== null && element !== undefined && element !== '';
});
if (rowContainsData) {
SpreadsheetApp.getActive().appendRow(newTrendData[row]);
}
}
SpreadsheetApp.getActive().getRange("1h!P2:S2").clearContent();
// Print values of L2:O2 in P2:S2
SpreadsheetApp.getActive().getRange("1h!P2:S2").setValues(newTrendData)
{
}
}
The Problem:
The range copies when the M2 > B16 and L2 > N2 condition is met but the cells do now copy downward in the P2:S2 range to create a history/logging effect of the range.
Example Sheet:
CodePudding user response:
SUGGESTION
In my understanding, here are your goals:
- If
M2 > B16 AND L2 > N2
aretrue
, get the value of rangeL2:O2
. - Make sure the
L2:O2
values are not empty. - Copy the value of
L2:O2
and place it on theP2:S2
range. - If the
P2:S2
range already has an existing value, it will be moved to the next row, making sure new values on theL2:O2
range are always added at the top.