Retrospective tool using Google Sheets

ReadMe.md

Retrospective tool using Google Sheet

Retro tool

Features:

• Concise: Currently just 3 columns: "What went well", "what went wrong", "Action Items"
• Extensibility: Can be extended to any number of features, but would require to change the appscript.
• Extra Feature: Upvote/Downvote. Generally retro tools doesn't have upvote/downvote feature, which is required to know the mood/issues of the team combined. And also, prioritisation of action items become easier rather than focusing on all problems at hand.
• Privacy: Upvote/Downvote are anonymous. And if you want to make the feedbacks as well anonymous, the sheet can be backed by a google form and the Aapscript can be used there.
• Security: Using 3rd party softwares/portals mean sharing your data outside your organisation. If you're a Google cloud customer, then this tool saves you from sharing your org data outside.

HowTo:

• Go to Retro tool and make a copy
• It would also copy the appscript.
• To edit and add more features: Sheet -> Extensions -> Appscript -> edit Code.gs -> click on Save button

Issues:

• At first it might seem slow as the script loads.
• Multiple votes by same person (although script can be edited to ensure this doesn't happen)

Code.gs

// Sheet link: tinyurl.com/sid-retro-tool
// Below code is to be pasted in AppScript Editor

UPVOTE_COLUMN = [2,7,12]
DOWNVOTE_COLUMN = [3,8,13]

function onSelectionChange(evt) {
  const activeSheet = SpreadsheetApp.getActiveSheet();
  const selectionRange = evt.range;
  const userSelectedARange = selectionRange.getNumRows() > 1 || selectionRange.getNumColumns() > 1;

  // The user didn't select a single cell, stop processing
  if (userSelectedARange) {
    return;
  }

  const selectedRow = selectionRange.getRow();
  const selectedColumn = selectionRange.getColumn();
  
  const headerNotSelected = selectedRow > 1;
  const cellNotEmpty = !selectionRange.isBlank();
  const processVote = headerNotSelected && cellNotEmpty;

  // They clicked a header or they clicked into an empty vote cell
  if (!processVote) {
    return;
  }

  const isUpvote = UPVOTE_COLUMN.includes(selectedColumn);
  const isDownvote = DOWNVOTE_COLUMN.includes(selectedColumn);

  if (isUpvote) {
    const scoreCell = activeSheet.getRange(selectedRow, selectedColumn+2);
    const currentScore = scoreCell.getValue();
    scoreCell.setValue(currentScore + 1);
  }

  if (isDownvote) {
    const scoreCell = activeSheet.getRange(selectedRow, selectedColumn+1);
    const currentScore = scoreCell.getValue();
    scoreCell.setValue(currentScore - 1);
  }
}