Question 239081 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Finding cell range and appending specific cell value

Hi guys I need a hand with my code.

What I am trying to do is get values from a sheet and appending to another BUT I will only append values on a condition of finding only numbers in a specific column range and if found I will append the values around it, like so:

function saveData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheets = ss.getSheets();
  const pricingWS = sheets[1]
  const takeoffWS = sheets[2]

  var dataRange = pricingWS.getRange(1,4,45,1).getValues()  

  var dataArray = []

  debugger

  for (var i = 1; i <= dataRange.length; i++)
  {
    if(dataRange[i - 1]<=0|| /^[a-zA-Z]+$/.test(dataRange[i - 1])){
      continue
    }
    else{ 
      dataArray.push(pricingWS.getRange("A7").getValue())
      dataArray.push(pricingWS.getRange(i, 1).getValue())
      dataArray.push(pricingWS.getRange(i, 2).getValue())
      dataArray.push(pricingWS.getRange(i, 4).getValue())
      dataArray.push(pricingWS.getRange("A8").getValue())
      takeoffWS.appendRow(dataArray)
      dataArray = []
    }   
    
  }
  
}

Just to add more context this is the sheet I am getting the values from:
sheet

This is the result sheet:
Result

I am not sure if this code is the best approach to it. I am googling everything and coding it step by step. lol.

BUT, that even isn’t my actual problem.

My problem is with the first and last push, I want to find a way to read the cell location I am in while doing the for loop and if it is out of the range I want, I want it to to change the getRange value of the first and last push.

To better explain, in the result sheet image above, my first and last column are repeating themselves because I hard coded them in my push. But what I want is when the var i of my dataRange is in the range D10:D15 I want it to push A7 and A8, when it gets to the range D40:D45 I want it to push A36 and A38 and so on.

Can somebody help me with that? please? lol

Thank you very much in advance.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0 
    function centreHouse() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheets = ss.getSheets();
  const pricingWS = sheets[1]
  const takeoffWS = sheets[2]

  var dataRange = pricingWS.getRange(1,5,118,1).getValues()  

  var dataArray = []

  debugger

  for (var i = 1; i <= dataRange.length; i++)
  {
    if(dataRange[i - 1]<=0|| /^[a-zA-Z]+$/.test(dataRange[i - 1])){
      continue
    }
    else{ 
      dataArray.push('=if(('+ pricingWS.getRange(i, 1).getValue()+'>=10)*('+ pricingWS.getRange(i, 1).getValue()+'<=15),Pricing!B8,if(('+ pricingWS.getRange(i, 1).getValue()+'>=20)*('+ pricingWS.getRange(i, 1).getValue()+'<=24),Pricing!B17,if('+ pricingWS.getRange(i, 1).getValue()+'=28,Pricing!B26,if(('+ pricingWS.getRange(i, 1).getValue()+'>=40)*('+ pricingWS.getRange(i, 1).getValue()+'<=45),Pricing!B38,if(('+ pricingWS.getRange(i, 1).getValue()+'>=50)*('+ pricingWS.getRange(i, 1).getValue()+'<=54),Pricing!B47,if('+ pricingWS.getRange(i, 1).getValue()+'=58,Pricing!B56,if(('+ pricingWS.getRange(i, 1).getValue()+'>=70)*('+ pricingWS.getRange(i, 1).getValue()+'<=75),Pricing!B68,if(('+ pricingWS.getRange(i, 1).getValue()+'>=80)*('+ pricingWS.getRange(i, 1).getValue()+'<=84),Pricing!B77,if('+ pricingWS.getRange(i, 1).getValue()+'=88,Pricing!B86,if(('+ pricingWS.getRange(i, 1).getValue()+'>=100)*('+ pricingWS.getRange(i, 1).getValue()+'<=104),Pricing!B98,if(('+ pricingWS.getRange(i, 1).getValue()+'>=110)*('+ pricingWS.getRange(i, 1).getValue()+'<=114),Pricing!B107,if('+ pricingWS.getRange(i, 1).getValue()+'=118,Pricing!B116))))))))))))')
      dataArray.push(pricingWS.getRange(i, 1).getValue())
      dataArray.push(pricingWS.getRange(i, 2).getValue())
      dataArray.push(pricingWS.getRange(i, 4).getValue())
      dataArray.push(pricingWS.getRange(i, 3).getValue())      
      dataArray.push(pricingWS.getRange(i, 5).getValue())
      dataArray.push(
        '=if(('+ pricingWS.getRange(i, 1).getValue()+'>=10)*('+ pricingWS.getRange(i, 1).getValue()+'<=28),Pricing!B6,if(('+ pricingWS.getRange(i, 1).getValue()+'>=40)*('+ pricingWS.getRange(i, 1).getValue()+'<=58),Pricing!B36,if(('+ pricingWS.getRange(i, 1).getValue()+'>=70)*('+ pricingWS.getRange(i, 1).getValue()+'<=88),Pricing!B66,if(('+ pricingWS.getRange(i, 1).getValue()+'>=100)*('+ pricingWS.getRange(i, 1).getValue()+'<=118),Pricing!B96))))')
      takeoffWS.appendRow(dataArray)
      dataArray = []
    }       
  }  
}

  2. 0

    Try this:

    function saveData() {
  const ss = SpreadsheetApp.getActive()
  const shts = ss.getSheets();
  const sh1 = shts[1]
  const sh2 = shts[2]
  const vs1 = sh1.getRange(1, 4, 45, 1).getValues().flat()
  vs1.forEach((e, i) => {
    if (!isNaN(e)) {
      let dA = [];
      dA.push(sh1.getRange("A7").getValue())
      dA.push(sh1.getRange(i + 1, 1).getValue())
      dA.push(sh1.getRange(i + 1, 2).getValue())
      dA.push(sh1.getRange(i + 1, 4).getValue())
      dA.push(sh1.getRange("A8").getValue())
      sh2.getRange(sh2.getLastRow() + 1, 1, 1, dA.length).setValues([dA]);
    }
  });
}
    Login or Signup to reply.
  3. 0

    If you look at my code I am emptying my array on every loop because otherwise this is the result

    result of your code

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search