How to sum the contents of corresponding cells of multiple tables in google sheets using google app script?

Question

I have multiple product tables with the same column names. I need to take all the data from one table, compare this data by name ("product") with the data in the summary table. If the name of the product matches, you need to sum up the cost and weight of the product in the "Monthly report" table, if not, add a new record to it. An example of the content of the tables is shown in the figures: Daily report, Monthly report.

Link to document: https://docs.google.com/spreadsheets/d/1nQfGvAhWeXVQkI1WkCUFMRHCuVl13kXM_tf9FXopgas/edit?usp=sharing

I tried to do this in the code below, but I just started learning javascript and have a few questions:

1. It seems that I am not working correctly with an array containing many records from different tables.
2. How to sum data from cell with corresponding data from array?
3. How to add new data to corresponding cells at the end of the table?

/**
 * This script gets summary data from other sheets. The sheets you want to summarize must have columns with headers that match the names of the columns this function summarizes data from.
 * 
 */

const MONTHLY_REPORT = "Monthly report";

/**
 * The function receives data from all sheets except "Monthly report" 
 * 
 * @return {string} Summary data from other sheets.
 */
function copyData() {
  var diary = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = diary.getSheets();
  var outputArr = [];
  for (var s in sheets) {
    // Gets sheet name.
    var sheetNm = sheets[s].getName();
    if (sheetNm === MONTHLY_REPORT) { continue; }
    // Gets sheets data.
    var values = sheets[s].getDataRange().getValues();
    // Gets the first row of the sheet which is the header row.
    var headerRowValues = values[0];
    // Finds the columns with the heading names and gets the index value of each.
    var columnProduct = headerRowValues.indexOf("Product name");
    var columnWeight = headerRowValues.indexOf("Sold in grams");
    var columnCash = headerRowValues.indexOf("Cash payments");
    var columnNoneCash = headerRowValues.indexOf("Cashless payments");
    // Removes header row.
    values.splice(0,1);
    // Gets the 'Product name' column value by retrieving the first data row in the array.
    var product = values[0][columnProduct];
    var weight = values[0][columnWeight];
    var cash = values[0][columnCash];
    var noneCash = values[0][columnNoneCash];
    outputArr.push([product,weight,cash,noneCash]);

  }
  return outputArr;
}

/**
 * The function summarizes or adds data from all sheets to the "Monthly report"
 * 
 * @return None
 */

function insertData(){
  var reportList = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Monthly report");
  //Get daily data array
  var diaryData = copyData();
  var newRecords = [];

  var values = reportList.getDataRange().getValues();
  // Gets the first row of the sheet which is the header row.
  var headerRowValues = values[0];
  try {
    /**
     * Problem in this block.
     * I'm trying to iterate through all the records in the "Monthly report" table 
     * and find the product name that matches the product name in the array.
     * 
     */
    for (var row = 0; row < values.length; row++) {
        for(var column = 0;column<values[row].length;column++){
          diaryData.forEach(function(item){
            //if there is a product with this name in the table, then sum up the corresponding cells for cost and weight.
          if (values[row][column].indexOf("Product name").getValues() === item [0].getValues()){
            //How to add new data in cell?
            } 
            //if it doesn't exist, add a new entry to the table..
            else {
              var value = item [0].getValues();
              newRecords.push([value]); 
            }
          })
          
        }
      }
    var lrow   = reportList.getLastRow();  
    //how to add data to corresponding cells?
  } catch (err) {
    Logger.log('Failed with error %s', err.message);
  }
  }

Answer 1

I'd propose my favorite method to convert the table data into the object data:

{
  product1: {weight: 1, cash: 2, no_cash: 3, ...},
  product2: {weight: 1, cash: 2, no_cash: 3, ...},
  product3: {weight: 1, cash: 2, no_cash: 3, ...},
  ...
}

and merge all the numbers inside this object.

Here is the code:

function main() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // make the array of data objects from all the sheets
  var objects = spreadsheet.getSheets().map(sheet => get_obj_from_sheet(sheet));

  // make the object with merged data from all the objects
  var merged_obj = merge_objects(objects);

  // convert the merged object into a table and put the table on the report sheet
  var sheet       = spreadsheet.getSheetByName('Monthly report');
  var header      = sheet.getDataRange().getValues().shift();
  var col_product = header.indexOf('Product name');
  var col_weight  = header.indexOf('Sold in grams');
  var col_cash    = header.indexOf('Cash payments');
  var col_no_cash = header.indexOf('Cashless payments');

  var data = [];
  for (let product in merged_obj) {
    var row = new Array(header.length);
    row[col_product] = product;
    row[col_weight]  = merged_obj[product][header[col_weight]];
    row[col_cash]    = merged_obj[product][header[col_cash]];
    row[col_no_cash] = merged_obj[product][header[col_no_cash]];
    data.push(row);
  }
  var table = [header, ...data];

  sheet.clearContents().getRange(1,1,table.length,table[0].length).setValues(table);
}

function get_obj_from_sheet(sheet) {
  var [header, ...data] = sheet.getDataRange().getValues();
  var col_product = header.indexOf('Product name');
  var obj = {};
  for(var row of data) {
    var product = row[col_product];
    obj[product] = {};
    for (var i in header) obj[product][header[i]] = row[i];
  }
  return obj;
}

function merge_objects(objects) {
  var cols = Object.keys(objects[0][Object.keys(objects[0])[0]]); // get headers from the first object
  var merged_obj = {};
  for (var obj of objects) {
    for (var product in obj) {
      try {
        for (var col of cols) merged_obj[product][col] += obj[product][col];
      } catch(e) {
        merged_obj[product] = obj[product];
      }
    }
  }
  return merged_obj;
}

Let me know if it works.