How to add Ranked Choice Voting to Google Forms and Google Sheets
Millions upon millions of people use Google Forms and Sheets every day. They are staggeringly popular and even more useful. But, they can't do everything you need. And, if you're on this page, chances are you're looking for ways to get them to do ranked choice voting. Well...you're in luck!
RankedVote has a Chrome Extension that brings ranked choice voting to Google Forms and Sheets — two of the most popular tools on the internet!
Potential Uses
Using RankedVote for Google Sheets allows you to get the best of both worlds:
- Ranked Choice Voting in Google Forms: Google Forms are incredibly flexible, but they lack any ranked choice calculation capability. The RankedVote Extension allow you to do those calculations easily with Google Forms output.
- Complex Surveys: Sometimes, you don't want to ask only ranked choice questions. You may need to collect Yes/No answers, comments, and more. If you have a mix of different question types, you can lean on Google Forms for managing that aspect and the RankedVote Extension for calculating any ranked choice results.
- Verified Voting: Google Forms allows you to limit voters to those signed in with specific Google Accounts or from specific Organizations. You can now collect your votes with fine-grained controls, while still generating ranked choice results.
- Scenario Switching: Adding voters, removing candidates, and changing the number of winners are as simple as adding a row, deleting a column, and changing a drop down. You can quickly adjust your sheet, update the data, and refresh in RankedVote.
- RankedVote Flexibility: Using the RankedVote Data Download feature, you can get voter data captured by RankedVote into a CSV that can be opened in Google Sheets. Change votes, multiply votes, remove candidates, and more with the ease of a spreadsheet.
Follow the walkthrough below to see exactly how to enhance your Google Forms and Sheets👇
Step-by-Step Walkthrough
Add "RankedVote for Google Sheets" to Chrome
First off, you might be wondering...what in the world is a "Chrome Extension?"
If you're new to the world of Chrome Extensions, that's totally ok. You can think of them as little apps that help the Chrome browser do something it couldn't do before. You may have heard of some of the more popular extensions like Honey (for finding deals) and Grammarly (for writing better).
The RankedVote for Google Sheets extension allows Chrome to read the data that's on the spreadsheet you're looking at and translate it into data that can be displayed in RankedVote.
To install, go to the Chrome Web Store listing for "RankedVote for Google Sheets" and click "Add to Chrome." That's all you need to do with it for now. We'll start using the extension in a few steps.
Set Up Your Google Form
In order to collect ranked choice data, you need voters to use a ranked choice ballot. For any ranked choice question that's part of your Google Form, here's the recommended way to set it up:
- Question Type: Multiple Choice Grid
- Rows: The names of the choices
- Columns: The rankings of your choices. Should be in the format "1", "2", "3" or "1st", "2nd", "3rd"
- Optional: "Require a response in each row" if you want to ensure that all choices are ranked
To get you started faster, here's an example Google Form configured as a ranked choice ballot.
Link Your Google Form to Google Sheets
This step is critical. In order for the extension to work, it needs the data to be in Google Sheets.
From the "Responses" tab of your Google Form, click the green Google Sheets icon. Then, choose where you want the response data to go ("Create a new spreadsheet" is usually best).
You're all set up! As responses to your form come in, the spreadsheet will update almost instantaneously.
Put Your Google Sheet Data in the Right Format
IMPORTANT NOTE: The RankedVote for Google Sheets extension is optimized for the default format that Google Forms uses. But, it does not require you to use Google Forms. As long as you can get your data into the format below, you can use RankedVote to calculate the results.
Basic Format
The Extension requires the Google Sheet to be in a specific format to accurately interpret the data. In simple terms, the Choices are the columns, the Voters are the rows, and the Rankings are the values in a row under a specific column. More precisely...
- Choices: The name or description of each choice needs to be in the first row of a column starting in cell B1.
- Voters: A unique identifier for each voter needs to be in the first column of a row starting in cell A2.
- Rankings: These are numeric values under a choice's column in a voter's row.
- Title: The name of the "Sheet" (not the overall spreadsheet, but the individual sheet found near the bottom)
That may sound a bit complicated, but the picture below usually makes things clear. You can also make a copy of this correctly formatted Google Sheet to get a better feel for how it works.
Important Things to Know About Formatting the Data
Choices
- The extension will grab the name between the [brackets], if there are brackets in the cell. This is the default way that Google Forms outputs the data. Otherwise, it will treat everything in Row 1 of a column as the choice's name.
- If Row 1 of a column is blank (aka there's no name), any rankings in that column will be ignored
Voters
- Each row should have a unique identifier in Column A.
- If more than one row has the same identifier in Column A, the first row is counted and the subsequent rows are ignored.
- If Column A of a row is blank, any rankings in that column will be ignored
- The info in A1 is ignored (Google Forms will put "Timestamp" there by default).
Note: By default, Google puts a timestamp that's accurate to the nearest second. This can lead to accidental duplications if you have many voters all voting at about the same time. If this happens to you, change the values of any duplicated cells to something that is unique across all of Column A.
Rankings
- The extension only understands cells that start with numeric data. So, "1" and "1st" will work, but "First" will not.
- If a cell is blank, no ranking is captured
Removing Non-Ranked Choice Data
Google Forms will include any of the other questions in your Form in the same sheet as your ranked choice questions. To get the calculation to work, you need to isolate the ranked choice question responses. Fortunately, it's pretty easy to do. Here's how:
- Duplicate the sheet. Click on the triangle icon next to the sheet's name ("Form Responses 1" if you're coming from Google Sheets) and select "Duplicate."
- Rename the new sheet (it has "Copy of" in the name). Click the triangle on that sheet and select "Rename."
- In the newly renamed sheet, remove all columns not related to the single ranked choice question you're focused on
- Prepare the data with the Extension
Handling Multiple Ranked Choice Questions
If you happen to have multiple ranked choice questions on a single form, you'll need to put each on its own sheet. The process is the same as in "Removing Non-Ranked Choice Data," but you repeat the steps for each ranked-choice question you want to isolate.
Use the Extension to Save Data to Your Computer
Now we get to the good stuff! While on your spreadsheet, open the RankedVote for Google Sheets extension:
How to open the extension:
- Click the "puzzle piece" icon to show your extensions and then select "RankedVote for Google Sheets"
- If it's pinned (recommended), click the icon with the purple square and checkmark
First Timers: Granting Access
The first time you use the RankedVote for Google Sheets extension, you'll need to grant it access to your Google Sheets. This allows the extension to read the data in the sheet and then save it to your computer in a ranked choice format. It's worth pointing out that the extension cannot change, edit, delete or otherwise modify the information in your sheets.
Once you've granted access, the next time you open the extension it can get your data ready for calculation and visualization.
Getting to a Spreadsheet
The extension can read the information from the spreadsheet you're currently looking at in the browser. If you're not on a spreadsheet, a message will appear directing you to go to one.
When you're on a spreadsheet, the extension can read the information in each sheet (the tabs along the bottom) contained within that spreadsheet. It shows that data in the Prepare, Update, and Saved Data sections of the extension.
Prepare
This is where the action starts. Prepare lists all sheets in the current spreadsheet that have not already been "prepared." Click the sheet you want to prepare for ranked choice voting calculations. When you do, the extension will analyze that sheet, save it as ranked choice data to your computer, and then refresh the Prepare, Update, and Saved Data lists.
Update
Use the Update tab to refresh the information in a previously saved sheet from the spreadsheet you're on. If more responses come in, you change the name of a choice, or anything else about the sheet changes after your first time preparing it, you'll need to update the data.
Click the blue "update/refresh" icon to pull in the latest data. You can also click the purple "square with arrow" icon to view the results in RankedVote. And, if you don't care for this data any more, click the red "delete" icon.
If any errors were encountered while saving your data (e.g. a cell said "Third" instead of "3rd"), a red warning will display under the sheet. To fix, make the corrections in your sheet, open the extension again, and then click the blue update icon.
Saved Data
The Saved Data list is just like the Update list, but you can see all the sheets you've saved that aren't on the sheet you're looking at. If you need to get back to a sheet to make updates, you can click the spreadsheet's name after the words "From:"
Calculate the Results in RankedVote
Once you have at least one sheet prepared, a link displays in the extension to "View Results in RankedVote." What are you waiting for? Click it!
The RankedVote web application will open and display a popup asking you to select:
- The saved sheet you want to use
- The number of winners
Click "Calculate Results" and you are done! You can also click "Change Settings" at the bottom-right corner of the page if you want to adjust the number of winners or load up a different sheet.
Bonus: Tips & Tricks
Get Ranked Choice Data From (Just About) Anywhere
Google Sheets is the de facto standard for cloud-based spreadsheets. Thousands of services integrate with it. That means you're not limited to collecting ranked choice data in Google Forms or RankedVote. If the data can get into Google Sheets, you can perform ranked choice calculations on it in the RankedVote Extension.
SurveyMonkey
After configuring your SurveyMonkey for ranked choice voting, you can connect the results directly to Google Sheets. Install the SurveyMonkey Google Sheets Add-On to make it happen.
Zapier (for anything else)
If a service doesn't directly integrate with Google Sheets, it's pretty likely that Zapier will bridge that gap. Check out Zapier's 5,000+ integrations with Google Sheets to pull in data from TypeForm, Airtable, and more.
Play With Your RankedVote Voter Data
By downloading voter data captured in the "RankedVote Extension" format and loading that into Google Sheets, you now have an extremely flexible way of working with your data. Here are some examples of what you could do:
Remove Candidates
If you want to see what happens if a candidate were to drop out, simply delete the column in the spreadsheet, update the data in the Chrome Extension, and view the results in RankedVote.
Weighted Voting
If you have certain voters whose votes count more than others, you can copy the row of that voter and paste it as many times as necessary to reflect the "weight" of that voter. For example, if the leader of a committee has a vote that counts 5x more than regular members, you'd copy the leader's vote and paste it four more times.
Note: Make sure to add something to the voter identifier in the first column so that each of these weighted votes is counted. For example, "leader-1," "leader-2", etc.
