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) }
Susan | Previous | Present |
---|---|---|
Value 1 | 23 | 45 |
Value 2 | 34 | 876 |
Value 3 | 456 | 456 |
Value 3 | 34 | 324 |
— | — | — |
Rachel | ||
Value 1 | 123 | 234 |
Value 2 | 123 | 654 |
Value 3 | 456 | 456 |
Value 4 | 43 | 45 |
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] }
Advertisement
Answer
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.