Create Google Sheet dropdown with names of Google Drive files

In the previous post we looked at a rudimentary mail merge for Gmail using Apps Script. The script used a global object that housed the default subject line and body for our emails. We incorporated placeholders into the text that the script replaced with recipients' data. There are, however, a couple of issues with this approach:

• Email text is built into the code, which means that business users will likely not be able to change on their own without risking breaking the script.
• There is only one email template. If we want to use mail merge with different types of messages (e.g, follow-up, special offer, membership invitation) then we will have to change the code every time we want a different template.

To address these shortcomings, we are going to store the email templates in a Google Docs. We will populate a dropdown with the names of the files, so that we can choose the email template with a single mouse click.

To get started, we need to add a new sheet to our spreadsheet. I'll call it "Admin".

This sheet should contain an empty cell in B3, and a button that will trigger the mail merge. We also need to create a new folder on Google Drive that will store our email templates. Inside the folder we can place one or more empty Google Docs and give each a unique name.

Now we need to write some code to inspect the folder, iterate through its documents, get their names, and create a validation rule for the B3 cell that will populate the names.

const g = {
  templatesFolderId: 'abc1213...',
  adminSheetName: 'Admin',
  dataSheetName: 'Registrations',
  dropdownRange: 'B3',
  messageRange: 'B4',
  ss: SpreadsheetApp.getActive(),
 };

I've made some changes in the global object that we saw in the previous post. "templatesFolderId" stores the ID of the Google Drive folder we created. You get the folder ID from the end of the folder URL in your web browser.

I'm storing the names of the two sheets in the global object, so that they're easy to find in case I want to change them in the future.

I also have the ranges for both the dropdown cell and another cell that will be used to communicate messages (instead of using alerts like we did in the previous post).

Now we will add code to retrieve the file names and populate the dropdown:

function updateTemplateDropdown() {
  // Message start of script
  notifyUser_('Updating dropdown...');
  // Get a reference to our dropdown cell using
  // the name of our admin sheet and the cell range
  const cell = g.ss.getSheetByName(g.adminSheetName)
    .getRange(g.dropdownRange);
  // Create an array to store the file names  
  const fileNames = [];
  // Create a query to look for a file that are stored 
  // in our templates folder
  const query = `"${g.templatesFolderId}" in parents`
  // Get a reference to our files
  const files = DriveApp.searchFiles(query);
  // Loop through our file references as long as the list
  // has not been exhausted
  while (files.hasNext()) {
    // Get the actual file
    const file = files.next();
    // Populate our array with the file name
    fileNames.push(file.getName());
  }
  // It's possible that the folder might be empty. In that case,
  // our array will be empty:
  if (fileNames.length == 0) {
    // Remove any validation that our script created in prior runs
    cell.clearDataValidations();
    // Empty the cell
    cell.setValue('');
    // Inform the user that no templates were found
    notifyUser_('No email templates found');
    // End the function execution
    return;
  }
  // Use SpreadsheetApp to define a new data validation rule.
  // Sort the file names and use them as values in a list
  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(fileNames.sort())
    .setAllowInvalid(false)
    .setHelpText('Select a template name from the dropdown menu')
    .build();
  // Once the rule is built, attach it to our cell.
  cell.setDataValidation(rule);
  // Notify the user
  notifyUser_('Dropdown updated');
}

We build a query to look for files in our folder. We then use DriveApp.searchFiles to look for files that match our query. The result of this operation gives us references to all the files found in the folder (if there are any).

We then use SpreadsheetApp to create a new data validation builder. We chain a bunch of methods together to pass in the list of file names (sorted) that will populate the dropdown. We build the rule and set it on our dropdown cell.

Next, we modify our notifyUser_ function to display messages in our admin sheet:

function notifyUser_(msg) {
  console.log(msg);
  g.ss.getSheetByName(g.adminSheetName)
    .getRange(g.messageRange).setValue(msg);
  SpreadsheetApp.flush();
}

Finally, we update the "onOpen" function to display a menu item to update the dropdown. We no longer need the custom menu to offer an option to run the mail merge since we are going to use a button to do that:

function onOpen(e) {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('🟢 Mail Merge')
    .addItem('Update template dropdown', 'updateTemplateDropdown')
    .addToUi();
}

To connect the button to the sendEmails function, right-click on the button, click on the three dots icon, then select "Assign Script" and type in the name of the function:

At this point, when you select "Update template dropdown" from the custom menu, the dropdown will be populated with the names of the files in your folder. The script currently ignores anything you place in those files though – I'll address that in the next post.