Google script to allow voting on a google sheet document.

00_readme.md

Vote on Google Sheet

There are some expectations:

• Score column needs to be configured, column A is 1, column B is 2, etc.
• Upvote and downvote columns need to be configured in the same way
• Within your sheet, insert an image into the cells for upvote/downvote. I like to use google image search within the sheets interface to add "up arrow" and "down arrow". You want to use images because you can drag down the corners to autofill the images into new rows as new data is added to the sheet

01_voting.gs

// Each columns number corresponds with its position in the alphabet. Column A is 1, B is 2, etc...
SCORE_COLUMN = 2;

// Column K
UPVOTE_COLUMN = 11
// Column L
DOWNVOTE_COLUMN = 12

function sortByScore() {
  const descending = false;
  const activeSheet = SpreadsheetApp.getActiveSheet();
  // sort descending, pass true if you want ascending sort
  activeSheet.sort(SCORE_COLUMN, descending);
}

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 scoreCell = activeSheet.getRange(selectedRow, SCORE_COLUMN);
  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 = selectedColumn === UPVOTE_COLUMN;
  const isDownvote = selectedColumn === DOWNVOTE_COLUMN;

  if (isUpvote) {
    const currentScore = scoreCell.getValue();
    scoreCell.setValue(currentScore + 1);
  }

  if (isDownvote) {
    const currentScore = scoreCell.getValue();
    scoreCell.setValue(currentScore - 1);
  }

  sortByScore();
}

Hi,
This code worked wonderfully. I'll appreciate if the code can add these features:

1. Restrict 1 vote per user. So, a single user upon voting for a row, cannot vote again for that row. I'm guessing the following line needs to add another condition for that:
   Line31: const processVote = headerNotSelected && cellNotEmpty && SOMETHING;

2. Add a pop-up message upon any vote (Up or Down) so that the user knows he has successfully voted.

TIA