Home > Enterprise >  Make the high/low values in step wise from a range in google sheet
Make the high/low values in step wise from a range in google sheet

Time:05-08

Need google app script to make stepwise to an array of numbers taken from a range cell values. Example suppose the taken cell values from A column are as: A1 1 A2 4 A3 2 A4 1 A5 4 The result cell values in B column should as follows: B1 1 B2 1 B3 2 B4 2 B5 3 B6 3 (B7 4) (B8 4) B9 3 B10 3 (B11 2) (B12 2) (B13 1) (B14 1) B15 2 B16 2 B17 3 B18 3 (B19 4) (B20 4) The logic is make the every recent high/low numbers by adding/subtracting by 1 from previous number till reach the recent numbers by double entry of the same to make stepwise. In result B column The numbers in bracket are the cell values of A column and not in bracket are new numbers to be add as per logic.

CodePudding user response:

Description

I believe what you want is that between 2 values, increment between them either 1 or -1 depending on the difference between the 2 values.

Script

function test() {
  try {
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test");
    let values = sheet.getDataRange().getValues();
    let result = [values[0][0],values[0][0]];
    for( let i=0; i<values.length-1; i   ) {
      let diff = values[i 1][0]-values[i][0];
      let inc = diff < 0 ? -1 : 1;
      diff = Math.abs(diff);
      let current = values[i][0];
      for( let j=0; j<diff; j   ) {
        current = current inc;
        result.push(current,current);
      }
    }
    result = result.map( item => [item] );  // turn result into 2D array
    sheet.getRange(1,2,result.length,1).setValues(result);
  }
  catch(err) {
    console.log(err);
  }
}

Screen shot

enter image description here

  • Related