I have a google sheet with ~ 2K items, each linked to several financial transactions. I have read several of the sumif questions on SO, but was hoping for assistance on improving my current script (reducing or streamlining the extra lines of code I added to make it work), which I based on @Mohammad Usman's answer to this SO question. I like his answer because it performs 2K sumif calculations in < three seconds, even with the extra steps I added to make it work for me in google sheets.

Below is a sample of what my google sheet looks like (in reality, I'll have ~2K rows under "Desired Output":

                                 INPUT

                                DESIRED OUTPUT

Here is the heart of my current code with comments:

const a = wsSalesData.getRange(2, 1, 1762).getValues();
const b0 = wsSalesData.getRange(2, 4, 1762).getValues();

const b = b0.flat(1);//if I don't add this line, then the script reads the amounts in column 4 as text and won't sum based on "const a." In the SO Post, "[4, 2, 7, 6, 8, 9]" doesn't require flattening.

const sumObject = b.reduce((acc,e,i,arr) => {
  acc[a[i]] = (acc[a[i]] || 0)   e;
  return acc;
}, {});

const sum = Object.entries(sumObject).map(el => [el[0] el[1]]);

let sourceArray = sum.map(row => [row[0].slice(0,6), row[0].slice(6)]);//needed to create two columns of data; otherwise, the product and the sum amount are in the same column.SO Post doesn't require this step.

Any idea how to:

1. Modify const sumObject to eliminate "const b" and "let source Array," which were not included in the source post
2. Modify code to allow for an additional criteria. For example, add a Zone column, and then sum by zone and item.

Thanks in advance for any improvements.

CodePudding user response：

Modification points:

• In your showing sample Spreadsheet image, it seems that the columns of "Fruit" and "Amouont Sold" are columns "A" and "B". But, when I saw your script, it seems that the columns of "Fruit" and "Amouont Sold" are columns "A" and "D". And, unfortunately, I cannot understand the destination range and sheet you expect. So, in this answer, from your sample Spreadsheet image, it supposes that the columns of "Fruit" and "Amouont Sold" of "INPUT" are columns "A" and "B", respectively. Please be careful about this.

• In your script, at const sum = Object.entries(sumObject).map(el => [el[0] el[1]]);, I thought that the key and the value are merged. By this, at let sourceArray = sum.map(row => [row[0].slice(0,6), row[0].slice(6)]);, no values are shown. I thought that this might be the reason for your current issue.

When these points are reflected in your script, how about the following modification?

Modified script:

function myFunction() {
  const srcSheetName = "Sheet1"; // Please set your source sheet name.
  const dstSheetName = "Sheet2"; // Please set your destination sheet name.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName(srcSheetName);
  const dstSheet = ss.getSheetByName(dstSheetName);
  const values = srcSheet.getRange(2, 1, srcSheet.getLastRow() - 1, 2).getValues();
  const res = [...values.reduce((m, [a, b]) => m.set(a, m.has(a) ? m.get(a)   b : b), new Map())];
  dstSheet.getRange(1, 1, res.length, res[0].length).setValues(res);
}

• When this script is run, the source values are retrieved from the columns "A" and "B" of the source sheet. And, the values are converted and the converted values are put into the destination sheet.

Reference:

• reduce()