Automating Google Sheets to Update Cover Letter Content Based on Input

Other Questions Google Sheets
1

I’m looking for a method to streamline the process of creating cover letters for various job applications. When applying for multiple positions, I often make errors in my letters, such as mentioning the wrong job title or company. To improve this, I’ve implemented a system based on Google Form submissions to auto-generate cover letters, following a tutorial I found. It worked well, but I would like to enhance it further. Here’s the code I’ve developed:

function generateCoverLetter(e) {
  const currentDate = e.values[1];
  const recipientName = e.values[2];
  const jobTitle = e.values[3];
  const organization = e.values[4];
  const itemName = e.values[5];
  const sentimentNote = e.values[6];
  const additionalNotes = e.values[7];
  const letterContent = e.values[8];

  const templateFile = DriveApp.getFileById("1a-qF9vzbXWyRPdKYTIn0RmH4yK8PP4ZvkQ6kITSTXZg");
  const targetFolder = DriveApp.getFolderById("158SpuE8urUej7JCaJfym8roi_uAdyu--");
  const copyFile = templateFile.makeCopy(`${currentDate}_${organization}_${jobTitle}`);

  const document = DocumentApp.openById(copyFile.getId());
  const documentBody = document.getBody();

  documentBody.replaceText('{{currentDate}}', currentDate);
  documentBody.replaceText('{{recipientName}}', recipientName);
  documentBody.replaceText('{{jobTitle}}', jobTitle);
  documentBody.replaceText('{{organization}}', organization);
  documentBody.replaceText('{{itemName}}', itemName);
  documentBody.replaceText('{{sentimentNote}}', sentimentNote);
  documentBody.replaceText('{{additionalNotes}}', additionalNotes);

  e.values[8] = ("Editor") ? documentBody.replaceText('{{letterContent}}', "editor cover letter body") : '';

  document.saveAndClose();
}

My goal is to respond with “Editor” on the last question of the form, and have the script automatically select the corresponding letter content for that position. I have prepared specific paragraphs for different roles. I attempted to adjust this line, but I’m uncertain about the correct way to structure it:

e.values[8] = ("Editor") ? documentBody.replaceText('{{letterContent}}', "editor cover letter body")

What function should I utilize here?

2

Hey! To make things adaptive, use regex matching on the job title and adjust the content accordingly. For instance, if you use ‘Editor’ in different contexts, regex can intelligently pick the related content, making your code future-proof and more dynamic. Regex magic works wonders once you get the hang of it!

3

From my experience, to handle different templates for various job titles, a switch statement might be more suitable than your current conditional logic. Given that you have specific paragraphs for each role, you can define the logic using a switch statement for neatness and clarity, like so:

e.values[8] = documentBody.replaceText('{{letterContent}}', getCustomContent(jobTitle));

function getCustomContent(jobTitle) {
  switch(jobTitle) {
    case 'Editor':
      return 'editor cover letter body';
    case 'Designer':
      return 'designer cover letter body';
    // add more cases for different job titles
    default:
      return 'default cover letter body';
  }
}

This will allow for easier future extensions as you can simply add new cases for new job titles. With this, based on the job title provided in your form, the cover letter body will be dynamically selected.