1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118
| function fullOuterJoin() {
// Open the spreadsheet and get the sheets
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = ss.getSheetByName("Dde d achats DIR QUALITE");
var sheet2 = ss.getSheetByName("DA ctrl gestion");
var outputSheet = ss.getSheetByName("DA");
if (!outputSheet) {
outputSheet = ss.insertSheet("Output");
} else {
outputSheet.clear();
}
// Get the data from the sheets
var data1 = sheet1.getDataRange().getValues();
var data2 = sheet2.getDataRange().getValues();
// Replace #N/A with empty strings in data1
data1 = data1.map(row => row.map(cell => cell === '#N/A' ? '' : cell));
// Assuming the first row contains headers
var headers1 = data1[0];
var headers2 = data2[0];
// Combine headers
var allHeaders = [...new Set([...headers1, ...headers2])];
outputSheet.appendRow(allHeaders);
// Helper function to create an empty row with all headers
function createEmptyRow() {
return Array(allHeaders.length).fill("");
}
// Helper function to fill the row with data based on headers
function fillRow(row, headers, data) {
headers.forEach((header, index) => {
var colIndex = allHeaders.indexOf(header);
row[colIndex] = data[index];
});
return row;
}
// Helper function to trim spaces
function trimSpaces(value) {
return value.toString().trim();
}
// Column indices for join keys (0-based)
var joinKeyIndex1 = 15; // Column P in Sheet1
var joinKeyIndex2 = 9; // Column J in Sheet2
// Create maps to store rows by join keys
var map1 = new Map();
var map2 = new Map();
// Process first sheet
for (var i = 1; i < data1.length; i++) {
var key = trimSpaces(data1[i][joinKeyIndex1]);
if (!map1.has(key)) {
map1.set(key, []);
}
map1.get(key).push(data1[i]);
}
// Process second sheet
for (var j = 1; j < data2.length; j++) {
var key = trimSpaces(data2[j][joinKeyIndex2]);
if (!map2.has(key)) {
map2.set(key, []);
}
map2.get(key).push(data2[j]);
}
// Create a set of all keys
var allKeys = new Set([...map1.keys(), ...map2.keys()]);
// Combine rows from both maps
allKeys.forEach(key => {
var rows1 = map1.get(key) || [createEmptyRow()];
var rows2 = map2.get(key) || [createEmptyRow()];
// Add empty lines for keys that do not have a match
if (rows1.length === 0) {
rows1 = [createEmptyRow()];
}
if (rows2.length === 0) {
rows2 = [createEmptyRow()];
}
// Add lines for the keys that are present in Sheet 1 but not in Sheet 2
if (rows2.length === 0) {
rows1.forEach(row1 => {
var combinedRow = createEmptyRow();
fillRow(combinedRow, headers1, row1);
outputSheet.appendRow(combinedRow);
});
}
// Add lines for the keys that are present in Sheet 2 but not in Sheet 1
if (rows1.length === 0) {
rows2.forEach(row2 => {
var combinedRow = createEmptyRow();
fillRow(combinedRow, headers2, row2);
outputSheet.appendRow(combinedRow);
});
}
// Add lines for the keys that are present in both sheets
rows1.forEach(row1 => {
rows2.forEach(row2 => {
var combinedRow = createEmptyRow();
fillRow(combinedRow, headers1, row1);
fillRow(combinedRow, headers2, row2);
outputSheet.appendRow(combinedRow);
});
});
});
} |
Partager