In the following table, what would the most efficient way to get the data into two separate objects that look like this, removing all the empty spaces.

This is what I have so far but it just takes up so many lines and I am not even complete. I would still need to split into two arrays.

The reason I would like an object is because the real data is financial so I would like the object to include methods in which this.value can be added to this.value2, for example. But perhaps an object is not the best route to achieve this.

Any help would be greatly appreciated.

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const ws = ss.getSheetByName('Sheet1')
  const data = ws.getDataRange().getDisplayValues()
  Logger.log(data)

  const array = data.map(function(row) {
    return [row[0],row[1],row[2]]
  }).filter(function(row) {
    return row[0] != '' && row[1] != "" && row[2] != ""
  })

  Logger.log(array)
}

Output:

let Susan = {
"value 1" = [23,45],
"value 2" = [34,876],
"value 3" = [456,456],
"value 4" = [43,45]
}
let Rachel = {
"value 1" = [123,234],
"value 2" = [123,654],
"value 3" = [456,456],
"value 4" = [43,45]
}

CodePudding user response：

Issue and solution:

The sample output you provided doesn't have a valid syntax for a JS object. Therefore, I'd like to propose an alternative structure for your objects, which doesn't rely on the variable name to store information.

In that case, I'd suggest building an array of objects, in which each object has two properties, name (e.g. Susan) and values, whose value is in itself an object. Each property in values would have, as the key, the value name, and as value, the array of values.

I think the suggested structure would make further retrieval and update more comfortable.

Code sample:

function spreadsheetValuesToObject() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName('Sheet1');
  const data = ws.getDataRange().getDisplayValues();
  const objects = [];
  let currentName;
  data.forEach(row => {
    const [a, b, c] = row;
    if (a) { // Check populated row
      if ((!b || b === "Previous") && (!c || c === "Present")) { // Name row
        objects.push({ name: a, values: {} });
        currentName = a;
      } else { // Value row
        const obj = objects.find(obj => obj.name === currentName);
        if (obj) { // Check name exists
          if (obj.values[a]) { // Check value exists
            obj.values[a].push(b,c);
          } else {
            obj.values[a] = [b,c];
          }
        }
      }
    }
  });
  return objects;
}

Output:

[
  {
    "name":"Susan",
    "values": {
      "Value 1":["23","45"],
      "Value 2":["34","876"],
      "Value 3":["456","456","34","324"]
    }
  },
  {
    "name":"Rachel",
    "values": {
      "Value 1":["123","234"],
      "Value 2":["123","654"],
      "Value 3":["456","456"],
      "Value 4":["43","45"]
    }
  }
]

Note:

In the sample above, it is assumed that name rows either have the headers Previous and Present on columns B and C, or that these cells are empty.