CODEWITHSUNDEEP
javascriptarraysgoogle-apps-script
dell
dell

Reputation: 191

How to store value in an array during looping and then sum them all using GAS?

Hi Helpful Contributors,
I have a loop that supposed to do below:

  1. iterate each col starting from col 13 til col 18
  2. fetch the value (qty) in each col
  3. each qty must be multiply by weight value which is from another gsheet to get total weight.
  4. total weight for each col should be sum up to get grandtotal weight.

I'm stuck on step 4. Below is my code:-

const itemsheet=SpreadsheetApp.openById("1c0-vzYQA_9KVHuSYiKHG-3iBBGhTg3EcCZBm7rZDIpM").getSheetByName('item');
const datasheet=SpreadsheetApp.openById("1c0-vzYQA_9KVHuSYiKHG-3iBBGhTg3EcCZBm7rZDIpM").getSheetByName('data');

function weightcalculator() {
  
  const lrow=datasheet.getLastRow();
  const lastrow=itemsheet.getLastRow();
  
  for(var i=13;i<13+(lrow-1);i++){
    const qtyheader=itemsheet.getRange(1,i).getValue();
    const qty=itemsheet.getRange(lastrow,i).getValue();
    const item=getItem(qtyheader); //
    const data=datasheet.getDataRange().getValues();
    const itemrow=data.findIndex(itemID=> {return itemID[2] == item}) +1;
    const weightval=datasheet.getRange(itemrow,7).getValue();
    const weight=weightval*qty;
   
  } 
}

I don't know how to keep each total weight after each looping and then sumup all. Below screenshot is the main gsheet "item" with qty value in each column.
qty value sheet Below is screenshot of another gsheet "data" which being referred to get the unit weight.
unit weight sheet

Upvotes: 0

Views: 55

Answers (1)

Marios
Marios

Reputation: 27348

Solution:

You can push every weight into a weight array and then sum up the elements of this array.

Here is one way to calculate the total weight:

const ss = SpreadsheetApp.openById("1c0-vzYQA_9KVHuSYiKHG-3iBBGhTg3EcCZBm7rZDIpM")
const itemsheet=ss.getSheetByName('item');
const datasheet=ss.getSheetByName('data');

function weightcalculator() {
  
  const lrow=datasheet.getLastRow();
  const lastrow=itemsheet.getLastRow();
  
  const weight = [];
  
  for(var i=13;i<13+(lrow-1);i++){
    const qtyheader=itemsheet.getRange(1,i).getValue();
    const qty=itemsheet.getRange(lastrow,i).getValue();
    const item=getItem(qtyheader); //
    const data=datasheet.getDataRange().getValues();
    const itemrow=data.findIndex(itemID=> {return itemID[2] == item}) +1;
    const weightval=datasheet.getRange(itemrow,7).getValue();
    weight.push(weightval*qty);
   
  } 
  const total_weight = weight.reduce((a, b) => a + b, 0);
  }

References:

Array.prototype.reduce()

Upvotes: 1

Related Questions