I want to create a new worksheet each time I have a new user details in column 1 of my USERS sheet. Here is the code I have so far:
// Get the data from the sheet called CreateSheets
var sheetNames = SpreadsheetApp.getActive().getSheetByName("USERS").getDataRange().getValues();
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("USERS");
var data = ss.getDataRange().getValues();
var lr = ss.getLastRow();
var dataRange = ss.getRange(1, 1, lr, 1);
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
// For each row in the sheet, insert a new sheet and rename it.
sheetNames.forEach(function(row) {
var sheetName = data;
var sheet = SpreadsheetApp.getActive().insertSheet();
sheet.setName(sheetName);
});
}}
The code works but it is combining the data in the cells in column 1 into the name of the new spreadsheet. Thanks
Updated to account for existing sheet names and potential duplicate names in column 1.
I think this should do what you want. You should probably build in some sort of check to ensure the sheet doesn't already exist.
function makeSheetHappen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var wsUser = ss.getSheetByName("USERS");//the sheet with users
//this gets all values in column 1 to end of spreadsheet (it might include blanks)
//flat function avoids having to pull 2-dim array (h/t COOPER!)
var sheetNames = wsUser.getRange(1, 1, wsUser.getLastRow(), 1).getValues().flat();
//mapping function to get an array of the spreadsheet's sheet names.
var theExistingNames = ss.getSheets().map(function (aSheet) {
return aSheet.getName();
});
//loops through the sheetNames and ensures not blank and not currently existing.
sheetNames.forEach(function (aName) {
if (aName != '' && !theExistingNames.includes(aName)) {
var newSheet = ss.insertSheet();
newSheet.setName(aName);
theExistingNames.push(aName); //add name to array to avoid duplicate names in column
}
});
}
function myfunc() {
const ss = SpreadsheetApp.getActive();
const ush = ss.getSheetByName("USERS");
const names = ush.getRange(1, 1, ush.getLastRow(), 1).getValues().flat();
const enames = ss.getSheets().map(s => s.getName());
names.forEach(n => {
if (~e.names.indexOf(n)) {
ss.insertSheet().setName(n);
enames.push(n); //add name to array to avoid duplicate names in column
}
});
}