Implementation Details

Modules

info

Unfortunately, the script is bound to the spreadsheet, and I can't share it directly without making the sheet open to the world. I'll share the code here, as a next-best effort.

In Google AppsScript, everything exists in the global scope. You can get the benefits of modules if you develop locally, and synchronize your changes with clasp, but that seemed a bit heavy-weight for this project. That said, I wanted to keep my code as organized as I could so, barring some utility functions and constants, I broke it up into the following chunks:

Code.gs

Code.gs is the entry point for the script, and performs high-level orchestration.

I would probably be wise to extract the late-added logic that writes the URL of the created (or updated) cover-letter to the spreadsheet into it's own module, but I've already invested a lot of time in this, and it's essentially a one-liner.

Code.gs

function generateCoverLetters() {
  const sheet = SpreadsheetApp
    // this is a bound script -- activeSpreadsheet is always the same sheet, and always non-null
    .getActiveSpreadsheet()
    .getSheetByName(JOB_LISTINGS_SHEET_NAME);

  const effectiveRange = getEffectiveRange(sheet);
  const jobListingRows = effectiveRange.getValues();
  
  for ([i, listingRow] of jobListingRows.entries()){
    if(listingRow.every(value => value === '')) continue;
    const listing = mapDataRowToJobListingRecord(listingRow);

    const coverLetterFile = createCoverLetterPathAndFile(listing);
    updateCoverLetter(coverLetterFile, listing);
    
    sheet.getRange(effectiveRange.getRow() + i, 7).setFormula(`=HYPERLINK("${coverLetterFile.getUrl()}", "Cover Letter")`);
  };
}

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Job Listing Tools')
    .addItem('Generate Cover Letters', 'generateCoverLetters')
    .addToUi();
}

rangeHandling.gs

rangeHandling.gs performs a series of operations on Google Sheets ranges. Not on the values in the ranges -- transformations of the ranges themselves.

These are:

• expand the range to include all columns in the sheet (to ensure we capture all the required data)
• strip the header row from the range, if it is included
• supply a default range if the range has no rows (after stripping headers) or if all cells in the range are empty.

Each of these depend on having access to the sheet, therefore the functions are defined as the return value of factories, and the sheet is supplied by way of an init function.

These initialized functions are then wrapped in decorators and composed into a sequence of operations that performs a meaningful task, in this case: returning a range in the spreadsheet value that:

• includes the selected portion of the spreadsheet, except the header row
• includes all columns for selected rows OR a range in the spreadsheet that:
• includes all rows and columns with meaningful data (default if an empty range is selected);

tip

by convention, a compose function generally uses 'reduceRight'. I generally prefer to use reduce as it puts the definition of operations in the same order they will be executed, which seems much more intuitive to me.

rangeHandling.gs

const isRangeEmpty = (range) => !range || range.getNumRows() > 1 && range.getValues()[0].every(cell => cell === "");
const rangeIncludesHeaderRow = (range) => range && range.getRow() === 1;
const getDefaultRange = (sheet) => sheet && sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());


const getActiveRangeFactory = (sheet) => () => sheet.getActiveRange()

expandRangeFactory = (sheet) => (range) => {
  Logger.log('Expanding range to include all spreadsheet columns')
  return newRange = sheet.getRange(range.getRow(), 1, range.getLastRow(), sheet.getLastColumn());
}

const stripHeaderRowFactory = (sheet) => (range) => {
  if(rangeIncludesHeaderRow(range) && range.getNumRows() === 1) {
    return null;
  }

  const [firstRow, numRows] = rangeIncludesHeaderRow(range) 
    ? [2, range.getNumRows() - 1]
    : [range.getRow(), range.getNumRows()];

  return sheet.getRange(firstRow, range.getColumn(), numRows, range.getNumColumns());
}

const applyDefaultIfEmptyFactory = (sheet) => (range) => {
  if(isRangeEmpty(range)) {
    Logger.log("Range is empty. Using default range (all data rows)");
    return getDefaultRange(sheet);
  }
  return range;
}

const initRangeFuncs = (sheet) => ({
  getActiveRange: logRangeTransform(getActiveRangeFactory(sheet), 'getActiveRange'),
  expandRange: logRangeTransform(skipIfNull(expandRangeFactory(sheet)), 'expandRange'),
  stripHeaderRow: logRangeTransform(skipIfNull(stripHeaderRowFactory(sheet)), 'stripHeaderRow'),
  applyDefaultIfEmpty: logRangeTransform(applyDefaultIfEmptyFactory(sheet), 'applyDefaultIfEmpty'), 
});

// if the active range is empty, select all data in the sheet
const getEffectiveRange = (sheet) => {
  const {getActiveRange, expandRange, stripHeaderRow, applyDefaultIfEmpty} = initRangeFuncs(sheet);

  const applyTransforms = compose(
    getActiveRange,
    expandRange,
    stripHeaderRow,
    applyDefaultIfEmpty
  );

  const effectiveRange = applyTransforms(sheet);
  return isRangeEmpty(effectiveRange) ? getDefaultRange(sheet) : effectiveRange;
}

jobListingRecord.gs

This very simple file just includes the transformation from a row of data in the spreadsheet to a JavaScript object that can be passed around and manipulated more easily.

jobListingRecord.gs

const _mapDataRowToJobListingRecord = ([companyName, roleTitle, uniqueId, hiringManager, listedAt]) => ({
    companyName,
    roleTitle,
    uniqueId,
    hiringManager: hiringManager || DEFAULT_HIRING_MANAGER,
    listedAt,
    coverLetterDate: toCoverLetterDate(new Date())
});

const mapDataRowToJobListingRecord = (row) => {
  Logger.log(`mapping row: ${row} to object`);
  const obj = _mapDataRowToJobListingRecord(row);
  Logger.log(`mapped result: ${JSON.stringify(obj)}`);
  return obj;
}

folderAndFileOps.gs

Basic file and folder options. These eventually get grouped into findOrCreate operations in fileAndFolderHandling.gs, but this file contains just the interactions with DriveApp

They're pretty straight-forward.

folderAndFileOps.gs

const getFolderById = (folderId) => () => {
  try {
    Logger.log(`Getting folder with ID: ${folderId}`)
    const folder = DriveApp.getFolderById(folderId);
    if(folder) Logger.log(`found folder ${folder.getName()} at ${folder.getUrl()}`);
    return folder;
  } catch (e) {
    Logger.log(`unable to find folder with ID ${folderId}: ${e instanceof Error ? e.message : e}`)
    return null;
  }
}

const findFolderByName = (folderName, parentFolder = DEFAULT_PARENT) => () => {
  const folders = parentFolder.getFoldersByName(folderName);
  folder = folders.hasNext() ? folders.next() : null;
  if(folder) {
    Logger.log(`found folder "${folderName}" with ID: ${folder.getId()}`);
  } else {
    Logger.log(`found no folders called '${folderName} under ${parentFolder.getUrl()}`);
  }
  return folder;
}

const findFileByName = (fileName, parentFolder = DEFAULT_PARENT) => () => {
  const files = parentFolder.getFilesByName(fileName);
  const file = files.hasNext() ? files.next() : null;
  if(file) {
    Logger.log(`found file "${fileName}" with ID: ${file.getId()}`);
  } else {
    Logger.log(`found no files called '${fileName} under ${parentFolder.getUrl()}`);
  }
  return file;
}

const createFolder = (folderName, parentFolder = DEFAULT_PARENT) => () => {
  Logger.log(`creating new Folder "${folderName}" at ${parentFolder.getName()}`)
  try {
    const folder = parentFolder.createFolder(folderName);
    Logger.log(`successfully created folder "${folderName}" with ID ${folder.getId()} at ${folder.getUrl()}`)
    return folder;
  } catch (e) {
    Logger.log(`Failed to create file ${folderName} at ${parentFolder.getUrl()}: ${e instanceof Error ? e.message : e }`);
    return null;
  }
}

const cloneTemplate = (fileName, parentFolder = DEFAULT_PARENT) => () => {
  Logger.log(`creating new File "${fileName}" at ${parentFolder.getName()}`)

  try {
    const file = DriveApp.getFileById(CL_TEMPLATE_DOC_ID).makeCopy(fileName, parentFolder);
    Logger.log(`successfully created file "${fileName}" with ID ${file.getId()} at URL ${file.getUrl()}`)
    return file;
  } catch (e) {
    Logger.log(`Failed to create file ${fileName} at ${parentFolder.getUrl()}: ${e instanceof Error ? e.message : e }`);
    return null;
  }
}

folderAndFileHandling.gs

The point of this file is to find each folder on the path to the cover-letter file, creating it if it does not exist, then do the same for the cover-letter file itself, cloning it from the template file if it doesn't already exist, and returning the existing file if it does

Functions in this file compose the basic folder and file operations from folderAndFileOps.gs into higher-level operations. This file uses the same pattern of initializing functions using factories, then wrapping them in decorators and composing them, making for a highly maintainable and readable file.

folderAndFileHandling.gs

const generateCompanyFolderName = (jobListingRecord) => `${jobListingRecord.companyName}`;
const generateListingFolderName = (jobListingRecord) => `${jobListingRecord.roleTitle} | ${jobListingRecord.uniqueId}`
const generateCoverLetterName = (jobListingRecord) => `Cover Letter -- ${jobListingRecord.companyName} | ${jobListingRecord.uniqueId}`

const DEFAULT_PARENT = DriveApp.getRootFolder();

const findOrCreateEmploymentFolderFactory = compose(
  logFileTransform(getFolderById(EMPLOYMENT_FOLDER_ID), 'getEmploymentFolderById'),
  skipIfNonNull(logFileTransform(findFolderByName(EMPLOYMENT_FOLDER_NAME), 'findEmploymentFolderByName')),
  skipIfNonNull(logFileTransform(createFolder(EMPLOYMENT_FOLDER_NAME), 'createEmploymentFolder'))
);

const findOrCreateCompanyFolderFactory = (jobListingRecord) => (employmentFolder) => {
  const folderName = generateCompanyFolderName(jobListingRecord);
  const {companyName} = jobListingRecord;

  return compose(
    logFileTransform(findFolderByName(companyName, employmentFolder), 'findCompanyFolderByName'),
    skipIfNonNull(logFileTransform(createFolder(companyName, employmentFolder), 'createCompanyFolder'))
  )
}

const findOrCreateListingFolderFactory = (jobListingRecord) => (companyFolder) => {
  const folderName = generateListingFolderName(jobListingRecord);

  return compose(
    logFileTransform(findFolderByName(folderName, companyFolder), 'findListingFolderByName'),
    skipIfNonNull(logFileTransform(createFolder(folderName, companyFolder), 'createListingFolder')),
  )
}

const findOrCreateCoverLetterFileFactory = (jobListingRecord) => (listingFolder) => {
  const coverLetterFilename = generateCoverLetterName(jobListingRecord);

  return compose(
    logFileTransform(findFileByName(coverLetterFilename, listingFolder), 'findCoverLetterFileByName'),
    skipIfNonNull(logFileTransform(cloneTemplate(coverLetterFilename, listingFolder), 'createCoverLetterFile')),
  )
}

const initFolderTransforms = (jobListingRecord) => ({
  findOrCreateEmploymentFolder: findOrCreateEmploymentFolderFactory,
  findOrCreateCompanyFolder: findOrCreateCompanyFolderFactory(jobListingRecord),
  findOrCreateListingFolder: findOrCreateListingFolderFactory(jobListingRecord),
  findOrCreateCoverLetter: findOrCreateCoverLetterFileFactory(jobListingRecord),
});

function createCoverLetterPathAndFile(jobListingRecord) {

  const {
    findOrCreateEmploymentFolder,
    findOrCreateCompanyFolder,
    findOrCreateListingFolder,
    findOrCreateCoverLetter
  } = initFolderTransforms(jobListingRecord);

  const employmentFolder = findOrCreateEmploymentFolder();
  const companyFolder = findOrCreateCompanyFolder(employmentFolder)();
  const listingFolder = findOrCreateListingFolder(companyFolder)();
  const coverLetter = findOrCreateCoverLetter(listingFolder)();

  return coverLetter;
}

updateCoverLetter.gs

Like Code.gs, this file could probably use some further decomposition.

This file assumes we have already retrieved (or created) the cover-letter file at its appointed place in the Drive.

It opens the template file, and the cover letter file, and copies the content from one to the other, preserving formatting. This has the advantage (over replacing the file) of preserving version history. Then, in the cloned document, it replaces the placeholder tokens with the actual values from the spreadsheet.

Note: there are a couple of peculiar lines here:

const firstParagraph = body.getChild(0)
if(!firstParagraph.asParagraph().getText().trim()) firstParagraph.removeFromParent();

These are there because the body.clear() function still leaves a single empty space or newline or something. The end result is that everything you append from the source document works just fine, except that there's an empty bar just at the top of the page. If such a line exists in the document, these two lines of script remove it.

updateCoverLetter.gs

function* getChildren(section) {
  for (index of range(section.getNumChildren())) {
    yield section.getChild(index).copy();
  }
}

const getAppendFunc = (element) => ({
  [DocumentApp.ElementType.PARAGRAPH]: (body) => body.appendParagraph(element),
  [DocumentApp.ElementType.LIST_ITEM]: (body) => body.appendListItem(element),
  [DocumentApp.ElementType.TABLE]: (body) => body.appendTable(element),
}[element.getType()])

function updateCoverLetter(coverLetterFile, jobListingRecord) {
  let templateDoc, coverLetterDoc;

  try {
    templateDoc = DocumentApp.openById(CL_TEMPLATE_DOC_ID);
    coverLetterDoc = DocumentApp.openById(coverLetterFile.getId());

    const body = coverLetterDoc.getBody();
    body.setMarginTop(0);
    body.clear();

    for(child of getChildren(templateDoc.getBody())) {
      const appendElementTo = getAppendFunc(child);
      appendElementTo(body);
    }

    const firstParagraph = body.getChild(0)
    if(!firstParagraph.asParagraph().getText().trim()) firstParagraph.removeFromParent();

    body.replaceText(COMPANY_NAME_TOKEN, jobListingRecord.companyName);
    body.replaceText(ROLE_TITLE_TOKEN, jobListingRecord.roleTitle);
    body.replaceText(HIRING_MANAGER_TOKEN, jobListingRecord.hiringManager);
    body.replaceText(LISTED_AT_TOKEN, jobListingRecord.listedAt);
    body.replaceText(CL_DATE_TOKEN, jobListingRecord.coverLetterDate);

    Logger.log(`Updated cover letter: ${coverLetterFile.getName()}`);

  } catch (e) {
    Logger.log(`Failed to update cover letter: ${e instanceof Error ? e.message : e}`);
  } finally {
    templateDoc?.saveAndClose();
    coverLetterDoc?.saveAndClose();
  }
}

ui.gs

File creates a custom menu in the Google Sheets app so the script can be run without having to open the script editor in another tab.

Note that the onOpen function is supposed to run automatically when the document opens, but to make this work, I had to set up an explicit trigger in the AppsScript editor.

ui.gs

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Job Listing Tools')
    .addItem('Generate Cover Letters', 'generateCoverLetters')
    .addToUi();
}