I currently have a Google Sheet Tab called Main that allows the person to enter data in a few cells. Then in another tab called IXRE-BOF it creates the config file that the person needs based upon the values entered on the Main tab. I currently have 2 macro buttons on the MAIN tab the person clicks on. The first is button calls the CreateFolder Macro seen below. It looks up the Main Google Drive folder ID stored in K25 on the MAIN tab and searches that drive for a folder name stored in cell B9 of the MAIN tab and if not found creates it. This Macro works fine and creates folder name correctly.

I second button calls the ExportBof macro and copies everything on the IXRE-BOF tab in Column A. This works correctly and exports the file called bof.cfg but stores it in the parent Google Drive folder currently. I have been searching online for the past week and can't find a way to make it create the bof.cfg file in the subfolder created by name from the cell value B9 on the MAIN tab. So currently I just manually move the bof.cfg file into the subfolder but wish to have this done automatically. This script will create multiple files called bof.cfg which I don't want to happen. If it finds a bof.cfg I wish to have it overwrite the existing file with the same name if possible.

Lastly I would love to have all of this in 1 macro versus 2. So ultimately the button would be called Create Configs and when pressed it would look in the parent folder for the subfolder name. If it isn't found it will create a new folder from the value in B9 of the MAIN tab and then create the bof.cfg file in that folder. If the folder is found it will just add the bof.cfg file into that folder.

My code isn't the cleanest, as I've been just creating it based upon various examples that partially match what I'm trying to accomplish. So if there is a cleaner way to write this I greatly appreciate the guidance. I used to have this all in Excel but migrating to Google Sheets.

'''

function CreateFolder() {
var ss = SpreadsheetApp.getActive();
var gdrive = ss.getSheetByName('MAIN').getRange('K25').getValue();
var parent=DriveApp.getFolderById(gdrive);
SpreadsheetApp.getActive().getSheetByName('MAIN').getRange('B9').getValues()
    .forEach(function (r) {
        if(r[0]) checkIfFolderExistElseCreate(parent, r[0]);
    })
}

function checkIfFolderExistElseCreate(parent, folderName) {
var folder;
try {
    folder = parent.getFoldersByName(folderName).next();
} catch (e) {
    folder = parent.createFolder(folderName);
}
};
function ExportBof() {
  var ss = SpreadsheetApp.getActive();
  var sheet=ss.getSheetByName('IXRE-BOF');
  var firstRow = 1; // Skip first two rows => start at 3rd
  var range = sheet.getRange(firstRow, 1, sheet.getLastRow() - firstRow   1, sheet.getLastColumn());
  var rows = range.getValues();
  var columns = rows[0].map((_, colIndex) => rows.map(row => row[colIndex]));
  var gdrive = ss.getSheetByName('MAIN').getRange('K25').getValue();
  var folder=DriveApp.getFolderById(gdrive);
  columns.forEach(function(column, index) {
    folder.createFile("bof.cfg", column.join("\n")); // New line
  });
}
};

'''

CodePudding user response：

You can accomplish both things (merge into one script and create in the correct folder) with the following steps:

1. Return folder from checkIfFolderExistElseCreate

2. Integrate the last line of CreateFolder into ExportBof as shown below, using the return value as your target folder. Use getValue instead of getValues since the range B9 is only one cell. No need to iterate.

3. You no longer need CreateFolder, so just connect the single script ExportBof to your spreadsheet button.

Updated script (** indicates changes):

function checkIfFolderExistElseCreate(parent, folderName) {
  var folder;
  try {
    folder = parent.getFoldersByName(folderName).next();
  } catch (e) {
    folder = parent.createFolder(folderName);
  }
  return folder // **Add this
};

function ExportBof() {
  var ss = SpreadsheetApp.getActive();
  var sheet=ss.getSheetByName('IXRE-BOF');
  var firstRow = 1; // Skip first two rows => start at 3rd
  var range = sheet.getRange(firstRow, 1, sheet.getLastRow() - firstRow   1, sheet.getLastColumn());
  var rows = range.getValues();
  var columns = rows[0].map((_, colIndex) => rows.map(row => row[colIndex]));
  var gdrive = ss.getSheetByName('MAIN').getRange('K25').getValue();
  var parent=DriveApp.getFolderById(gdrive); // **Change this to parent
  var folderName = ss.getSheetByName('MAIN')
    .getRange('B9').getValue() // **Add this line
  var folder = checkIfFolderExistElseCreate(parent, folderName) // **Add this line
  columns.forEach(function(column, index) {
    folder.createFile("bof.cfg", column.join("\n")); // New line
  });
}
};