I have a master list with dozens of names in row 2 spread across a bunch of columns (A2:Z2). Under each name is a list of values and data.
row 2 | John | Sally | James |
---|---|---|---|
row 3 | Value | Value | Value |
row 4 | Value | Value | Value |
row 5 | Value | Value | |
row 6 | Value | Value |
Each name should be created into a sheet.
Here is the script used to create a sheet for each name in row 2:
function generateSheetByName() {
const ss = SpreadsheetApp.getActive();
var mainSheet = ss.getSheetByName('Master List');
const sheetNames = mainSheet.getRange(2, 1, mainSheet.getLastRow(), 1).getValues().flat();
sheetNames.forEach(n => ss.insertSheet(n));
}
I want this script to not only create a sheet for each name but also carry over all values under each name all the way down to the last row of the respective column.
e.g. John is in A2 and A3:A are the values that should be carried over to the sheet created. Sally is B2 and B3:B are the values that should carry over.
In John's sheet - "John" is the header in A1 and the column values sit in A2:A
For every sheet that is made I also want to add other values manually. Like for example, if "John" sheet is created, and 20 values are added in A2:A22, I want the script to add checkbox in B2:B22. Or to always add a formula in B1 like "=counta(a2:a)" or something.
How can I do this with an efficient loop? Note this will likely create 50 sheets and carry over 10-50 values per sheet
Example images:
Master List:
Each name will have a sheet created that will look like this
John's sheet