Bdtjtk

I have created a custom function in GAS that is equivalent to a SUMIF formula and will run through an array that contains a range of data within a google sheet. The function will sum the 'capacity' column when the 'status' column matches entries from a defined range in the sheet.

It worked fine until I rearranged the array to be separated by columns rather than by row. The columns are arranged: Project name, Connection type, Ownership, Country, Capacity, Status, Technology, Fuel, Commissioning date, Commissioning end date. This is how the array looks now, it is sorted by the columns.

[[Azambi Hydro, Budana Hydro, Busanga Hydro, Dilolo Thermal, N'zilo I Hydro], [Embedded, Embedded, Embedded, Embedded, Embedded], [IPP, IPP, IPP, IPP, PPP], [DR Congo, DR Congo, DR Congo, DR Congo, DR Congo], [10.0, 11.0, 240.0, 0.0, 108.0], [Construction, Operating, Planned, Operating, Rehabilitation], [Conventional hydro, Conventional hydro, Conventional hydro, Undecided or unknown, Conventional hydro], [Hydro, Hydro, Hydro, Cogeneration, Hydro], [Sat Dec 01 08:00:00 GMT+00:00 2018, Mon Jan 01 08:00:00 GMT+00:00 1940, Fri Dec 31 08:00:00 GMT+00:00 2021, , Thu Jan 01 08:00:00 GMT+00:00 1953], [, , , , ]]

For some reason the function isn't working with the array arranged this way and I can't seem to figure out why.

Full code:

var sheet = SpreadsheetApp.getActiveSpreadsheet()

var statusColumn = sheet.getSheetByName('Supply Projections').getRange(6,7,9,1).getValues()



var status = statusColumn.map(function (x) { return x[0] });

var status0 = status[0];

var status1 = status[1];

var status2 = status[2];

var status3 = status[3];

var status4 = status[4];

var status5 = status[5];

var status6 = status[6];

var status7 = status[7];

var status8 = status[8];

var statusArray = [status0,status1,status2,status3,status4,status5,status6,status7,status8];





function transpose(data) {



return (data[0] || ).map (function (col , colIndex) {

return data.map (function (row) {

  return row[colIndex];

  });

});

 }





var sheetData = transpose(sheet.getSheetByName('Project 

Selector').getRange('$D$3:M7').getValues())



//SUMIF function

function sumIf(sumColIdx, criteriaColIdx, criteria, data) {

  return data

    .filter(function (row) {

      return row[criteriaColIdx] === criteria

    })

    .reduce(function (acc, row) {

      return acc + row[sumColIdx]

    }, 0)

}



  var capacityStatusSums = statusArray.map(function (criteria) {

    var statusColIdx = 5

    var capacityColIdx = 4

    return sumIf(capacityColIdx, statusColIdx, criteria, sheetData)

  })

I think it might be the way I am referencing the position within the array, see statusColIdx = 5 and capacityColIdx = 4. Previously, they referenced the 5th and 4th column within my data range and I assumed it would be the same with the array arranged by columns but I am not sure.