TrainingBot

Some of the committees have adopted the TrainingBot system to keep on top of training requests.

• Training is requested via a google form.
• Form responses are collected in a google sheet.
• A Slack bot (TrainingBot) posts to the committee channel whenever someone requests training.
• The TrainingBot posts a list of all the pending training requests to the committee channel once a week.
• Trainers view the responses form, and when they have reached out to someone they add something to a specific row which removes them from the reminders list.

• Create a google form in your committee's google drive folder - see the training request forms for examples
  • Make sure you collect their name, and their slack or talk handle
• Set the google form up to save responses to a google sheet that also belongs to your committee's group.
• From the Form, click “App Scripts” to load up the app scripts editor
• Name the created project “<Subject> Training Form Scripts”. e.g. “CoreXY Training Form Scripts”
• Click on the sidebar to add a library, and enter this library ID: 1NX_-nJIfqTDdmuDtLvdI4-t-sXl7V5Lt0-CBHCO54jyGwFBt9_mZ9DEa
  • If you don't have access to the Library, ask in #vhs-it-ops on slack or DM @cory.
• Customize and add the following code to the main code.gs file in the App Script project:

const TRAINING_TYPE = "CoreXY";
const COMMITTEE_CHANNEL = "3d-printing-committee";
const COMMITTEE_NAME = "3D Printing Committee";
const FORM_NAME_FIELD = "Slack username";

function onSubmit(e) {
  let form = FormApp.getActiveForm();
  let formResponse = VHSWebhookCode.parseForm(form);

  const username = formResponse[FORM_NAME_FIELD];
  const message = `${formResponse['Name']} (@${username}) has requested ${TRAINING_TYPE} training. They should be contacted by ${VHSWebhookCode.getDateNDaysFromNow(30)}.`;

  VHSWebhookCode.postToSlack(COMMITTEE_CHANNEL, message);
  
  const dm_message = `The ${COMMITTEE_NAME} has been notified of your training request, and someone should get back to you soon! Remember, we're all volunteers here at VHS, so please be patient!`;

  VHSWebhookCode.sendSlackDMToUser(username, dm_message);
};

The most important details to get right are COMMITTEE_CHANNEL, which needs to exactly match the committee channel name, and FORM_NAME_FIELD, which needs to exactly match the name of the username field in the Google Form.

Now add a trigger, so the onSubmit script is run when the form is submitted: go to the triggers tab from the sidebar, and add a trigger that runs onSubmit when the “On form submit” event happens.

The final step is to add the weekly reminders. To do this, go to the form's responses Sheet, and under the “extensions” menu select “App Script”.

Name the project something like “CoreXY Training Sheet Scripts”

Add a modified version of the following code to the App Script:

const TRAINING_TYPE = "filament";
const SHEET_NAME = "Form Responses 1";
const CHANNEL = "3d-printing-committee";
const SHEET_NAME_INDEX = 1;
const SHEET_TRAINED_STATUS_INDEX = 6;

function checkForUnTrained() {
  let untrained = VHSWebhookCode.scanSheetForNamesAndTrainers(SHEET_NAME, SHEET_NAME_INDEX, SHEET_TRAINED_STATUS_INDEX);

  let names = untrained.map((item)=>item.name);

  console.log(names);
  
  const message = `There are ${ names.length } members waiting for ${TRAINING_TYPE} training. If you have some time this week, consider setting up a training session! Training list: ${names.join(', ')}`;

  VHSWebhookCode.postToSlack(CHANNEL, message);
}

Important variables:

• SHEET_NAME is the name of the sheet - usually “Form Responses 1”.
• SHEET_NAME_INDEX is the index of the name field, 0 is the first column, 1 is the second, etc.
• SHEET_TRAINED_STATUS_INDEX is the index of the field used to track if the member has been contacted.

Then go to the Triggers section, and add a “Time Based” trigger that runs checkForUnTrained weekly.

Don't forget to run both the scripts manually - sometimes it'll toss up a permissions dialog that you have to OK before it'll run successfully.

That's it!