I'm trying to take census data I receive from a spreadsheet and convert it to a 2D array to do my cleaning to it. Unfortunately, not all sheets I receive are formatted the same, and thus I am trying to make the script be able to work with multiple formats. I have it working when there is one line per person, but when I get one line per dependent and multiple per employee my script does not return the desired results.
Working Example inputs:
[
["LstNm", "1st Nm", "Email", "EE-SSN", "Mem-SSN","Relate"]
["Smith", "John", "[email protected]", 11, 11, "E"]
["Wang", "Jacob", "[email protected]", 11, 22, "C"]
["Da Silva", "Jingle", "[email protected]", 13, 13, "E"]
["Martin", "Heimer", "[email protected]", 12, 12, "E"]
["Muller", "Schmidt", "[email protected]", 12, 22, "S"]
]
Problem Inputs. This is where I want to return two rows, one for the employee, and one for the dependent. But in my code's present state, it is not working:
[
["LstNm", "1stNm", "Email", "EE-SSN", "Mem-SSN", "Relate", "DepLstNm", "Dep1stNm", "Dep-SSN", "Dep-Relate"]
["Smith", "John", "[email protected]", 11, 11, "E", "Wang", "Jacob", 21, "C"]
["Da Silva", "Jingle", "[email protected]", 13, 13, "E", , , , ]
["Martin", "Heimer", "[email protected]", 12, 12, "E", "Muller", "Schmidt", 22, "S"]
]
The expected end result in both cases would be One row per person with the EE-SSN linking the employee to the dependent:
[
["Email", "1stNm", "LstNm", "EE-SSN", "Mem-SSN", "Relate"]
["[email protected]", "John", "Smith", 11, 11, "E"]
["[email protected]", "Jacob", "Wang", 11, 22, "C"]
["[email protected]", "Jingle", "Da Silva", 13, 13, "E"]
["[email protected]", "Heimer", "Martin", 12, 12, "E"]
["[email protected]", "Schmidt", "Muller", 12, 22, "S"]
]
Finally, here is the code I'm using:
function getData(origData, index) {
var depCheck = index[6] index[7] index[8] index[9]
if (depCheck < 0) {
var workData = origData.map(row => ([
row[index[2]], // EMAIL
row[index[1]], // FIRST NAME
row[index[0]], // LAST NAME
row[index[3]], // SUBSCIBER SSN
row[index[4]], // MEMBER SSN
row[index[5]], // RELATIONSHIP
]));
} else {
var arr = [[]];
var workData = origData.flatMap(row => {
arr.push([
row[index[2]], // EMAIL
row[index[1]], // FIRST NAME
row[index[0]], // LAST NAME
row[index[3]], // SUBSCIBER SSN
row[index[4]], // MEMBER SSN
row[index[5]], // RELATIONSHIP
]);
if (typeof row[index[6]] !== "undefined" && typeof row[index[8]] !== "undefined" ) {
arr.push([
row[index[2]], // EMAIL
row[index[7]], // FIRST NAME
row[index[6]], // LAST NAME
row[index[3]], // SUBSCIBER SSN
row[index[8]], // MEMBER SSN
row[index[9]], // RELATIONSHIP
]);
}
return arr;
});
}
return workData;
}
function main () {
const originalSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet3');
const origData = originalSheet.getRange("A6:F11").getValues();
console.log(origData);
var index = [0,1,2,3,4,5,-1,-1,-1,-1];
var data = getData(origData, index);
console.log("Single Rows:\n" data);
const origData2 = originalSheet.getRange("A13:J16").getValues();
console.log(origData2);
var index2 = [0,1,2,3,4,5,6,7,8,9];
var data2 = getData(origData2, index2);
console.log("Double Rows:\n" data2);
var numRows = data.length;
var numCols = data[0].length;
var numRows2 = data2.length;
var numCols2 = data2[0].length;
originalSheet.getRange(18,1,numRows,numCols).setValues(data);
originalSheet.getRange(18,7,numRows2,numCols2).setValues(data2);
}
EDIT Reviewing the code further, I realized that by returning a 2D array in the second map which returns arr[tempEE, tempDep] is actually becoming a 3D array. As such, I switched the map to flatMap (see answer below), which gives the desired 2D array.
CodePudding user response：
Description
Here is a revised example of how to use array functions to break the employee/dependant row into 2 seperate rows . This example assumes there is only one dependant per employee.
Script
function testCensus() {
try {
let spread = SpreadsheetApp.getActiveSpreadsheet();
let sheet = spread.getSheetByName("Census");
let values = sheet.getDataRange().getValues();
values.forEach( row => Logger.log(row) );
let header = values.shift(); // remove the header row
let results = [[].concat(header[2],header[1],header[0],header.slice(3,6))];
function sort(row) {
let temp = [].concat(row[2],row[1],row[0],row.slice(3,6));
results.push(temp);
if( row[6] !== '' ) {
temp = [].concat(row[2],row[7],row[6],row[3],row.slice(-2));
results.push(temp);
}
}
values.forEach( row => sort(row) );
results.forEach( row => Logger.log(row) );
}
catch(err) {
console.log("Error in testCensus - " err)
}
}
Console.log
10:04:57 AM Notice Execution started
10:04:58 AM Info [LstNm, 1stNm, Email, EE-SSN, Mem-SSN, Relate, DepLstNm, Dep1stNm, Dep-SSN, Dep-Relate]
10:04:58 AM Info [Smith, John, [email protected], 11.0, 11.0, E, Wang, Jacob, 21.0, C]
10:04:58 AM Info [Da Silva, Jingle, [email protected], 13.0, 13.0, E, , , , ]
10:04:58 AM Info [Martin, Heimer, [email protected], 12.0, 12.0, E, Muller, Schmidt, 22.0, S]
10:04:58 AM Info [Email, 1stNm, LstNm, EE-SSN, Mem-SSN, Relate]
10:04:58 AM Info [[email protected], John, Smith, 11.0, 11.0, E]
10:04:58 AM Info [[email protected], Jacob, Wang, 11.0, 21.0, C]
10:04:58 AM Info [[email protected], Jingle, Da Silva, 13.0, 13.0, E]
10:04:58 AM Info [[email protected], Heimer, Martin, 12.0, 12.0, E]
10:04:58 AM Info [[email protected], Schmidt, Muller, 12.0, 22.0, S]
10:04:58 AM Notice Execution completed
Reference
- https://www.w3schools.com/jsref/jsref_foreach.asp
- https://www.w3schools.com/jsref/jsref_concat_array.asp
- https://www.w3schools.com/jsref/jsref_slice_array.asp
CodePudding user response：
Actually, changing the second map to flatMap does fix the problem, here is the final code I used:
function getData(origData, index) {
var depCheck = index[6] index[7] index[8] index[9]
if (depCheck < 0) {
var workData = origData.map(row => ([ // The data is simply returned as a 1D arr here
row[index[2]], // EMAIL
row[index[1]], // FIRST NAME
row[index[0]], // LAST NAME
row[index[3]], // SUBSCIBER SSN
row[index[4]], // MEMBER SSN
row[index[5]], // RELATIONSHIP
]));
} else {
var arr = [];
/** Use flatMap here as the returned arr[tempEE, tempDep] is a 2D array being added to an
* array workData[arr], flatMap takes the result and flattens it by one level*/
var workData = origData.flatMap(row => {
var tempEE = [
row[index[2]], // EMAIL
row[index[1]], // FIRST NAME
row[index[0]], // LAST NAME
row[index[3]], // SUBSCIBER SSN
row[index[4]], // MEMBER SSN
row[index[5]], // RELATIONSHIP
];
if (typeof row[index[6]] !== "undefined" && typeof row[index[8]] !== "undefined" ) {
console.log("Dependent - " row[index[6]])
var tempDep = [
row[index[2]], // EMAIL
row[index[7]], // FIRST NAME
row[index[6]], // LAST NAME
row[index[3]], // SUBSCIBER SSN
row[index[8]], // MEMBER SSN
row[index[9]], // RELATIONSHIP
];
}
var arr = [tempEE, tempDep];
return arr;
});
}
return workData;
}
function main () {
const originalSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet3');
const origData = originalSheet.getRange("A6:F11").getValues();
console.log(origData);
var index = [0,1,2,3,4,5,-1,-1,-1,-1];
var data = getData(origData, index);
console.log("Single Rows:\n" data);
const origData2 = originalSheet.getRange("A13:J16").getValues();
console.log(origData2);
var index2 = [0,1,2,3,4,5,6,7,8,9];
var data2 = getData(origData2, index2);
console.log("Double Rows:\n" data2);
var numRows = data.length;
var numCols = data[0].length;
var numRows2 = data2.length;
var numCols2 = data2[0].length;
originalSheet.getRange(18,1,numRows,numCols).setValues(data);
originalSheet.getRange(18,7,numRows2,numCols2).setValues(data2);
}
Reference: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/flatMap