I spent some time on this but could not find the answer I was looking for. I am writing a cost calculator, where not everybody contributed to every cost. I use a check box to count whether a person is a contributor to a given cost, and at the end there is a formula that takes an indefinite number of arguments (using arguments in JS), and tallies up all checked boxes and adds to the sum for a given person. You will see what I mean below, with screenshots, code snippet, and my gSheet. All I am trying to figure out is, if an argument (checkbox) is checked, to add to my sum the value of a different cell from a different row, but same column, as my checkbox.

I am able to use the INDIRECT syntax if I am writing the script directly in cell, but am trying to use GAS and call the if function, so I can add the if (checkbox) then statement. Any help is appreciated.

Bonus points if you can help me figure out syntax so I don't have to add every cell's value as a comma-separated argument, and instead add a range as an array of arguments.

Please look below and see if you can help me figure out what syntax I am looking for.

Screenshot of the sheet

Link to sheet here

Code:

function addIt() {
  for (let i = 0; i < arguments.length; i  ) {
    var sum = 0;
    //this is where I want to add a 'perPerson' that is a value offset from the the cell position of a given argument
    //var perPerson = [?????]
    if (arguments[i] == true) {
      sum  = perPerson;
    }
  }
  return sum;
}

CodePudding user response：

In your situation, how about the following sample script?

Sample script 1:

In this sample, please put =addIt(B3:G3) to a cell "H3". By this, the value is put to the cells "H3".

function addIt(values) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var v = sheet.getRange("B10:G10").getValues()[0];
  return values[0].reduce((s, c, i) => s  = c == true ? v[i] : 0, 0);
}

Sample script 2:

In this sample, please put =addIt(B3:G8) to a cell "H3". By this, the values are put to the cells "H3:H8".

function addIt(values) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var v = sheet.getRange("B10:G10").getValues()[0];
  return values.map(r => r.reduce((s, c, j) => s  = c == true ? v[j] : 0, 0));
}

References:

• reduce()
• map()

CodePudding user response：

Grand Total Without using any cells functions

function grandTotals() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const sr = 3;
  const ihA = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues().flat();
  const chA = sh.getRange(2, 1, 1, sh.getLastColumn()).getValues().flat();
  const vs = sh.getRange(sr, 1, sh.getLastRow() - sr   1, sh.getLastColumn()).getDisplayValues();
  let imidx = {};
  ihA.forEach((h, i) => { imidx[h] = i; });
  let csidx = {};
  chA.forEach((h, i) => { csidx[h] = i; })
  let gt = { pA: [], iA: [] };
  //collecting cell data
  vs.forEach((r, i) => {
    r.forEach((c, j) => {
      if (j > 0) {
        if (c == "TRUE") {
          if (!gt.hasOwnProperty(r[0])) {
            gt[r[0]] = [];
            gt[r[0]].push({ item: ihA[j], cost: chA[j] })
            gt.pA.push(r[0]);
          } else {
            gt[r[0]].push({ item: ihA[j], cost: chA[j] })
          }
          if (!gt.hasOwnProperty(ihA[j])) {
            gt[ihA[j]] = { cost: chA[j], count: 1 };
            gt.iA.push(ihA[j])
          } else {
            gt[ihA[j]].count  = 1;
          }
        }
      }
    });
  });
  //calculating grand totals
  let g =  gt.pA.map(p => {
    return [p,parseInt(gt[p].reduce((a, obj) => {
      a  = gt[obj.item].cost / gt[obj.item].count;
      return a;
    }, 0))]
  });
  sh.getRange(sr,8,g.length,g[0].length).setValues(g);
  const end = "is near";
  console.log(g);
}

[ [ 'Person A', 408 ],
  [ 'Person B', 808 ],
  [ 'Person C', 150 ],
  [ 'Person E', 25 ],
  [ 'Person F', 708 ] ]

Starting Data:

Grand Totals: