Create files from sheet

Question

I'd like to have Apps Script create a new spreadsheet based on names in a sheet. For instance, if my list of names (in column 7) were:

I'd like the script to iterate through this list, replace the string with just the name (minus the email domain), search the drive for an existing file that matches the name. Also, if there is an existing file, do nothing, else create a sheet with the name.

My code below seems to be iterating through the list and replacing the string but there is an issue with the last two steps. It would see that Bender does not exist, and it would create a Bender file, but then it creates additional Bender files as it iterates through the list, and does not create the other missing files.

Any assistance would be appreciated.

function findExistingSheets() {
      var sheet = SpreadsheetApp.openById('SpreadSheetKey').getSheetByName('ISheetName');
      var data = sheet.getDataRange().getValues();
      var files = DriveApp.getFolderById("FileKeyID").getFiles();
        for (var i = 1; i < data.length; i++) {
          var searchFor = data[i][7].replace("@interpret.org.nz","");
            while (files.hasNext()) {
              var file = files.next();
              if(searchFor == file.getName()){
                //Don't do anything
              }
              else {
                //Create speadsheet file with the matchfile name.
                var newFile = SpreadsheetApp.create(searchFor);
              }
            }
        }
      Logger.log(searchFor);
    }

Cooper Cooper · Accepted Answer · 2017-04-06T01:48:45

Try this. I haven't tested it, but I think it will get you close.

function findExistingSheets() {
      var sheet = SpreadsheetApp.openById('SpreadSheetKey').getSheetByName('ISheetName');
      var data = sheet.getDataRange().getValues();

      var titles = [];
      for (var i = 1; i < data.length; i++) 
      {
        titles.push(data[i][7].replace("@interpret.org.nz",""));
      }    
      var files = DriveApp.getFolderById("FileKeyID").getFiles();  
      while (files.hasNext()) 
      {
          var file = files.next();
          var idx = idxOf(titles,file.getName());
          if(idx>-1)
          {
            var newFile = SpreadsheetApp.create(titles[idx]);
          }
      }
    }

function idxOf(array, x)
{
  for(var i = 0;i < array.length;i++)
  {
    if(array[i]==x)
    {
      return i;
     }
  }
  return -1;
}

Create files from sheet

2 Answers