Table of Contents

Automatic Backlog Documentation Generation with Google Sheets and Apps Script
#

In an Agile project, it is essential to document User Stories (US) so that the entire team has a clear vision of requirements. Through Google Sheets and Apps Script, it is possible to automate this documentation generation.

Prepare the Backlog in Google Sheets
#

Imagine a Backlog file with a simple structure:

Main Script code.gs
#

The following script automates the retrieval of User Stories ready for documentation and creates a Google Doc for each US:

function main() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const backlogName = tabs?.get('backlog') || 'Backlog';
  const backlogSS = sheet.getSheetByName(backlogName);

  if (!backlogSS) {
    SpreadsheetApp.getUi().alert(`Sheet "${backlogName}" not found.`);
    return;
  }

  const lastBacklogLine = backlogSS.getLastRow();
  if (lastBacklogLine < 2) {
    SpreadsheetApp.getUi().alert('No data found in backlog.');
    return;
  }

  const rangeUS = backlogSS.getRange(2, 1, lastBacklogLine - 1, 7);
  const USValues = rangeUS.getValues();

  const usReady = USValues.filter(row => Boolean(row[0]));

  if (usReady.length === 0) {
    SpreadsheetApp.getUi().alert('No User Stories ready to be documented.');
    return;
  }

  const ui = SpreadsheetApp.getUi();
  const dialogResponse = ui.alert(
    `${usReady.length} User Stories are ready to be documented. Do you want to continue?`,
    ui.ButtonSet.OK_CANCEL
  );

  if (dialogResponse === ui.Button.OK) {
    const doc = getDoc(usReady[0]);
    usReady.forEach(us => generate(us));
    doc.saveAndClose();
    ui.alert('Documentation generated successfully ✓');
  }
}

Project Parameters (Para.gs)
#

To simplify maintenance, centralize sheet names:

const tabs = new Map([
  ['backlog', 'Backlog'],
  ['params', 'Params']
])

Document Generation (GenerateDoc.gs)
#

The script creates a document per User Story and applies minimal styling:

const generate = (usRow) => {
  console.log(`About to generate doc for ${usRow[1]}`)
  // Here, you could iterate through each column to enrich the document
}

const getDoc = (usReady) => {
  const title = `[US-${usReady[1]} - ${usReady[2]}]`;
  const doc = DocumentApp.create(title);
  const body = doc.getBody();

  body.setMarginTop(72)
      .setMarginBottom(72)
      .setMarginLeft(72)
      .setMarginRight(72);

  const mainTitle = body.appendParagraph(title);
  mainTitle.setHeading(DocumentApp.ParagraphHeading.TITLE);
  mainTitle.setAlignment(DocumentApp.HorizontalAlignment.CENTER);
  mainTitle.setAttributes({ [DocumentApp.Attribute.FONT_SIZE]: 20 });

  body.appendParagraph('');

  const description = body.appendParagraph(usReady[3] || 'Description not provided');
  description.setAttributes({
    [DocumentApp.Attribute.FONT_SIZE]: 11,
    [DocumentApp.Attribute.FONT_FAMILY]: 'Arial',
  });

  body.appendParagraph('');

  const businessContext = body.appendParagraph('Business context');
  businessContext.setHeading(DocumentApp.ParagraphHeading.HEADING2);
  businessContext.setAttributes({ [DocumentApp.Attribute.FONT_SIZE]: 16 });

  return doc;
}