I want to combine these two formulas but can't seem to the ' and " right?
I want to output a table that has the data from the source tables whose sheet name is checked in sheet FL-elever:
You could do this with an Apps Script Custom Function.
First, open a bound script by selecting Tools > Script editor, and copy the following function to the script (check inline comments):
function POPULATE_TABLE() {
var output = new Array(6).fill("").map(function() {
return new Array(5).fill(""); // Initialize the 2D array you will output
});
var ss = SpreadsheetApp.getActive();
// Get the rows in which checkbox is marked:
var FLelever = ss.getSheetByName("FL-elever");
var checkedNames = FLelever.getDataRange().getValues().slice(1).filter(function(row) {
return row[5] === true;
});
checkedNames.forEach(function(checkedName) { // Iterate through each row with marked checkbox
var sheetName = checkedName[0] + "-" + checkedName[1]; // Get sheet name (column A + "-" + column B)
var sheet = ss.getSheetByName(sheetName); // Get corresponding sheet
if (sheet) { // Check that sheet exists
// Get the source table from the corresponding sheet:
var firstRow = 2;
var firstCol = 2;
var numRows = sheet.getLastRow() - firstRow + 1;
var numCols = sheet.getLastColumn() - firstRow + 1;
var values = sheet.getRange(firstRow, firstCol, numRows, numCols).getValues();
// Iterate through the source table and add the existing values to output:
for (var i = 0; i < values.length; i++) {
for (var j = 0; j < values[i].length; j++) {
if (output[i][j] !== "" && values[i][j] !== "") {
output[i][j] = output[i][j].concat(", ");
}
output[i][j] = output[i][j].concat(values[i][j]); // Concatenate values
}
}
}
});
return output;
}
Once it is defined, you can use this function the same you would any sheets built-in function:
