How To Add Sheets Based on Cell Values?

user_15943944 0
Viewed 46

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

google-apps-script google-sheets javascript
2 Answers

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
    }
  });
}
user_117323200 
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
    }
  });
}
user_72150910
Related