javascript function variables google-apps-script google-sheets

Google Sheets and App Script; Adding multiple numbers with a function

I have made a new dummy sheet. I have included the expected result and the current result with my current code. I have tried with my best ability to try and code this, but it ultimately isn't working well for me. Basically, I need the blank Trans with quantities to be added to the one Trans. Same with price and discount. This is an extension of my previous question, so if you need some clarification, please check that as well. Let me know if you need some more info or help. If you are able to help, thank you so much. Any help/advice is greatly appreciated! Thank you so much for your effort and help! Let me know if anything else is needed.

The link is sheet.

Note: Please ignore the variable/parameter "Category", since it is not related to this topic/question.

AppsScript Code:

var RemoveDupV1 = "";
var RemoveDubVV1 = "";
var Time2List = [];
var Trans2List = [];
var RemoveDupV2 = "";
var RemoveDupV3 = "";
var RemoveDupV4 = 0;
var RemoveDupV5 = 0;
var RemoveDupV6 = 0;

function RemoveDup1(Trans1, Trans2, Time1, Time2, Category, Quantity1, Quantity2, Disc1, Disc2, Price1, Price2) {
  if(Trans1.localeCompare(Trans2) == 0){
    if(Time1.localeCompare(Time2) == 0){
      RemoveDup2V1(Trans2, Time2);
      PropertiesService.getScriptProperties().setProperty('myArray', Trans2);
      return(Trans1);
    }else{
      return(Trans1)
    }
  }else{
    return(Trans1)
  }
}

function RDR1(Trans1, Trans2, Time1, Time2, Category, Quantity1, Quantity2, Disc1, Disc2, Price1, Price2){
  RemoveDupV1 = RemoveDup1(Trans1, Trans2, Time1, Time2, Category, Quantity1, Quantity2, Disc1, Disc2, Price1, Price2);
  return(RemoveDupV1)
}

function RemoveDup2V1(Trans2,Time2) {
  Trans2List.push(Trans2);
  Time2List.push(Time2);
}


// Time
function RemoveDup2(Trans1, Trans2, Time1, Time2, Category, Quantity1, Quantity2, Disc1, Disc2, Price1, Price2) {
  if(Trans1.localeCompare(Trans2) == 0){
    if(Time1.localeCompare(Time2) == 0){
      return(Time1);
    }else{
      return(Time1)
    }
  }else{
    return(Time1)
  }
}

function RDR2(Trans1, Trans2, Time1, Time2, Category, Quantity1, Quantity2, Disc1, Disc2, Price1, Price2){
  RemoveDupV2 = RemoveDup2(Trans1, Trans2, Time1, Time2, Category, Quantity1, Quantity2, Disc1, Disc2, Price1, Price2);
  return(RemoveDupV2)
}



// Category
function RemoveDup3(Trans1, Trans2, Time1, Time2, Category, Quantity1, Quantity2, Disc1, Disc2, Price1, Price2) {
  if(Trans1.localeCompare(Trans2) == 0){
    if(Time1.localeCompare(Time2) == 0){
      return(Category);
    }else{
      return(Category)
    }
  }else{
    return(Category)
  }
}

function RDR3(Trans1, Trans2, Time1, Time2, Category, Quantity1, Quantity2, Disc1, Disc2, Price1, Price2){
  RemoveDupV3 = RemoveDup3(Trans1, Trans2, Time1, Time2, Category, Quantity1, Quantity2, Disc1, Disc2, Price1, Price2);
  return(RemoveDupV3)
}



// Quantity
function RemoveDup4(Trans1, Trans2, Time1, Time2, Category, Quantity1, Quantity2, Disc1, Disc2, Price1, Price2) {
  if(Trans1.localeCompare(Trans2) == 0){
    if(Time1.localeCompare(Time2) == 0){
      return(Quantity1 + Quantity2);
    }else{
      return(Quantity1)
    }
  }else{
    return(Quantity1)
  }
}

function RDR4(Trans1, Trans2, Time1, Time2, Category, Quantity1, Quantity2, Disc1, Disc2, Price1, Price2){
  RemoveDupV4 = RemoveDup4(Trans1, Trans2, Time1, Time2, Category, Quantity1, Quantity2, Disc1, Disc2, Price1, Price2);
  return(RemoveDupV4)
}



// Disc
function RemoveDup5(Trans1, Trans2, Time1, Time2, Category, Quantity1, Quantity2, Disc1, Disc2, Price1, Price2) {
  if(Trans1.localeCompare(Trans2) == 0){
    if(Time1.localeCompare(Time2) == 0){
      return(Disc1 + Disc2);
    }else{
      return(Disc1)
    }
  }else{
    return(Disc1)
  }
}

function RDR5(Trans1, Trans2, Time1, Time2, Category, Quantity1, Quantity2, Disc1, Disc2, Price1, Price2){
  RemoveDupV5 = RemoveDup5(Trans1, Trans2, Time1, Time2, Category, Quantity1, Quantity2, Disc1, Disc2, Price1, Price2);
  return(RemoveDupV5)
}



// Price
function RemoveDup6(Trans1, Trans2, Time1, Time2, Category, Quantity1, Quantity2, Disc1, Disc2, Price1, Price2) {
  if(Trans1.localeCompare(Trans2) == 0){
    if(Time1.localeCompare(Time2) == 0){
      return(Price1 + Price2);
    }else{
      return(Price1)
    }
  }else{
    return(Price1)
  }
}

function RDR6(Trans1, Trans2, Time1, Time2, Category, Quantity1, Quantity2, Disc1, Disc2, Price1, Price2){
  RemoveDupV6 = RemoveDup6(Trans1, Trans2, Time1, Time2, Category, Quantity1, Quantity2, Disc1, Disc2, Price1, Price2);
  return(RemoveDupV6)
}


function clean1() {
  var data = SpreadsheetApp.getActive().getActiveSheet().getRange('A2:B11').getDisplayValues();

  var res = data.map((x, i, arr) => {
    try {
      return (x[0] == arr[i + 1][0] && x[1] == arr[i + 1][1]) ? ['', x[1]] : arr[i + 1];
    } catch {
    }
  }).filter(x => x);

  res.unshift(data[0])

  SpreadsheetApp.getActive().getActiveSheet().getRange(2, 7, res.length, res[0].length).setValues(res);
}

function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu('Clear Dupes')
    .addItem('Clear all Dupes', 'clean1')
    .addSeparator()
    .addToUi();
}

Solution

From your following reply,

If you see the dummy sheet, you can see the expected result at the bottom, basically the transactions that are blank have one main transaction above them. The code need to output the correct quantity, discount amount, and price by adding it all up from the blank transactions.

When your provided Spreadsheet is used, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet and save the script.

When you use this script in your provided Spreadsheet, please put a custom function of =SAMPLE(A1:E11) into a cell. By this, the script is run and result values are returned.

const SAMPLE = values =>
  [...values.reduce((m, r) => {
    const [a, , ...c] = r;
    if (m.has(a)) {
      const [aa, bb, ...cc] = m.get(a);
      m.set(a, [aa, bb, ...cc.map((e, i) => e + c[i])]);
    } else {
      m.set(a, r);
    }
    return m
  }, new Map()).values()];

Testing:

When this script is run with your provided Spreadsheet, the following result is obtained. In this sample, I put a custom function of =SAMPLE(A1:E11) into a cell "G14". The result can be seen in the red color range.

Note:

If the number of rows is large, the custom function might not be able to be used. In that case, please run the script with the script editor. At that time, please use the following script.

function myFunction() {
  const sheetName = "Sheet1"; // Please set your sheet name.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getDataRange();
  const values = [...range.getValues().reduce((m, r) => {
    const [a, , ...c] = r;
    if (m.has(a)) {
      const [aa, bb, ...cc] = m.get(a);
      m.set(a, [aa, bb, ...cc.map((e, i) => e + c[i])]);
    } else {
      m.set(a, r);
    }
    return m
  }, new Map()).values()];
  range.clearContent().offset(0, 0, values.length, values[0].length).setValues(values);
}

References:

reduce()
Map

Added 1:

From your following replies,

but it is a bit incorrect. Both functions are returning quantities added that are the same name. I have added a new sheet, please check Sheet2 using the same link. In that sheet I have added what need to be done. For example, G4 is a name under it is a blank, the quantities, price, and discount of that blank need to be added to the G4. Same thing with G7. In G27, there are two blanks below it. The quantities, discounts, and prices of those two blanks need to be added to G27. All of these need to be repeated. I hope this clarifies everything.

A:E is just to help me, just ignore it. G:K is my sample. M:Q is my output.

As one more sample script, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet, and save the script. And, put a custom function of =SAMPLE(Sheet2!G1:K) into a cell.

const SAMPLE = v =>
  v.reduce((o, r, i) => {
    const [a, , ...c] = r;
    if (a.toString() == "") {
      const f1 = o.values[o.temp].splice(0, 2);
      o.values[o.temp] = [...f1, ...o.values[o.temp].map((e, j) => e + c[j])];
    } else {
      o.temp = i;
    }
    o.values.push(r);
    return o;
  }, { values: [], temp: -1 }).values;

If you want to directly run the script with the script editor, please use the following script.

function myFunction() {
  const sheetName = "Sheet2"; // Please set your sheet name.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange("G1:K" + sheet.getLastRow());
  const { values } = range.getValues().reduce((o, r, i) => {
    const [a, , ...c] = r;
    if (a.toString() == "") {
      const f1 = o.values[o.temp].splice(0, 2);
      o.values[o.temp] = [...f1, ...o.values[o.temp].map((e, j) => e + c[j])];
    } else {
      o.temp = i;
    }
    o.values.push(r);
    return o;
  }, { values: [], temp: -1 });
  range.offset(0, 6).setValues(values);
}

Note:

This sample script is for your 2nd expected result. Please be careful about this.

Added 2:

From your following replies,

PERFECT! But I need the value where it is blank/"", I need that row to be deleted. Then It is perfect. I forgot to mention that. Other than that, It is working well.

As one more sample script, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet, and save the script. And, put a custom function of =SAMPLE(Sheet2!G1:K) into a cell.

const SAMPLE = v =>
  v.reduce((o, r, i) => {
    const [a, , ...c] = r;
    if (a.toString() == "") {
      const f1 = o.values[o.temp].splice(0, 2);
      o.values[o.temp] = [...f1, ...o.values[o.temp].map((e, j) => e + c[j])];
    } else {
      o.temp = i;
    }
    o.values.push(r);
    return o;
  }, { values: [], temp: -1 }).values.filter(([a]) => a);

If you want to directly run the script with the script editor, please use the following script.

function myFunction() {
  const sheetName = "Sheet2"; // Please set your sheet name.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange("G1:K" + sheet.getLastRow());
  const values = range.getValues().reduce((o, r, i) => {
    const [a, , ...c] = r;
    if (a.toString() == "") {
      const f1 = o.values[o.temp].splice(0, 2);
      o.values[o.temp] = [...f1, ...o.values[o.temp].map((e, j) => e + c[j])];
    } else {
      o.temp = i;
    }
    o.values.push(r);
    return o;
  }, { values: [], temp: -1 }).values.filter(([a]) => a);
  range.offset(0, 6, values.length, values[0].length).setValues(values);
}