Google offers WorkSpace (their online office suite) freely for charities. This can be used with your own domain name to allow you to send and recieve emails to [email protected] as well as giving you a repository for all of your scouting files.
Recently, I was organising an event which required volunteers for specific tasks, so I sent a Google Form out. After linking it to a spreadsheet, for the responses to be collated, I refined it with a little bit of code to remove the tasks I had assigned.
My spreadsheet had one tab for the form responses and another called “Volunteers Needed”. In column A of this tab were the tasks/roles I needed help with on the event.
In my spreadsheet I went to Extensions, Apps Script and replaced all of the text in the window with the code below, before making some small changes and saving.
// Define the form and sheet IDs
const formId = 'YOUR_FORM_ID';
const sheetId = 'YOUR_SPREADSHEET_ID';
// Update dropdown in form with volunteer needs
function updateVolunteerDropdown() {
// Access the Google Form
const form = FormApp.openById(formId);
// Access the Google Sheet and the "Volunteer Needs" sheet
const sheet = SpreadsheetApp.openById(sheetId);
const needsSheet = sheet.getSheetByName('Volunteer Needed');
// Get the list of volunteer roles from the "Volunteer Needs" sheet
const roles = needsSheet.getRange('A2:A').getValues().flat().filter(role => role); // Adjust range as needed
// Find the question by title (adjust "Your Question Title" to the exact question title in the form)
const items = form.getItems();
const dropdownItem = items.find(item => item.getTitle() === 'Your Question Title');
// Check if the question was found and is of the correct type
if (dropdownItem) {
// Check if it's a multiple-choice or dropdown (list) question
if (dropdownItem.getType() === FormApp.ItemType.MULTIPLE_CHOICE) {
dropdownItem.asMultipleChoiceItem().setChoiceValues(roles);
} else if (dropdownItem.getType() === FormApp.ItemType.LIST) {
dropdownItem.asListItem().setChoiceValues(roles);
} else {
console.error("The found question is not a multiple-choice or list type.");
}
} else {
console.error("Dropdown question not found. Please check the title.");
}
}
// Schedule the function to run periodically to ensure the dropdown is up to date
function setupTrigger() {
ScriptApp.newTrigger('updateVolunteerDropdown')
.timeBased()
.everyHours(1) // Adjust frequency as needed
.create();
}
The FORM_ID is the long string of letters and numbers after /d/ and before /edit. Copy this ID. The spreadsheet is much the same. The only other thing to set is the title of the question – e.g. “How can you help?”
As replies came in and I allocated jobs, I removed them from the Volunteering Needed and re-loaded the form. This meant I didn’t end up with too moany volunteers in the wrong role.
Leave a Reply to JazzyJeff42 Cancel reply