Home > Software design >  Apps Script to copy a Sheets range, move the value down a row and enter a new value in the original
Apps Script to copy a Sheets range, move the value down a row and enter a new value in the original

Time:12-10

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:

minimal example of sheet

CodePudding user response:

SUGGESTION

In my understanding, here are your goals:

  1. If M2 > B16 AND L2 > N2 are true, get the value of range L2:O2.
  2. Make sure the L2:O2 values are not empty.
  3. Copy the value of L2:O2 and place it on the P2:S2 range.
  4. If the P2:S2 range already has an existing value, it will be moved to the next row, making sure new values on the L2:O2 range are always added at the top.

I have tweaked your script using an array method called enter image description here

  • Related