This article demonstrates how to achieve simple automation using Microsoft Forms and Excel by utilizing Office Scripts.
Scenario:
A team leader wishes to automate how the team meeting schedule is managed. The workflow that team leader comes up with is this -
- Creates an Excel file to store the meeting schedule with list of topics and presenter details.
- Creates a Microsoft form to collect meeting agenda request. Anyone in the team can submit the Form to request to present a topic.
- Once a request is made, the automation will perform business validations to either accept or reject the request. The automation will update the Excel file with the confirmed schedule.
- The workflow will Email the requester confirming or rejecting the request.
- To ensure that no topic is confirmed during holidays and team events, it references a team wide holiday/event schedule maintained in a separate Excel file as part the validation.
Below is the complete flow and require Office Scripts source code to achieve this scenario:
Overall flow
Forms to Excel data flow:
Final part - emailing result
Meeting request holding table:
Final schedule:
Holiday schedule
Microsoft Forms
Script listing
Meeting request
// Save data from forms to table
function main(workbook: ExcelScript.Workbook, record: string) {
let table = workbook.getTable('IncomingRequests');
let values = record.split('|');
console.log(values)
table.addRow(-1, values);
}
Get holidays
function main(workbook: ExcelScript.Workbook): string {
let range = workbook.getTable('BlockedDays').getRangeBetweenHeaderAndTotal();
let holidayDates = range.getTexts();
let returnValue = holidayDates.join('|');
console.log(returnValue)
return returnValue;
}
Validate request
function main(workbook: ExcelScript.Workbook, input: string): MeetingApproval[] {
let reqTable = workbook.getWorksheet('AddRequest').getTables()[0];
let allRequests: string[][] = reqTable.getRangeBetweenHeaderAndTotal().getTexts();
console.log(allRequests);
// Get confirmed schedule.
let finalSchedule = workbook.getWorksheet('Schedule').getTables()[0];
// Unprotect worksheet
// workbook.getWorksheet('Schedule').getProtection().unProtect();
// Declare output
let decisions: MeetingApproval[] = [];
for (let request of allRequests) {
console.log(`Calling process request for ${request[0]}`)
let result = processRequest(request, input, workbook);
console.log(result.decision);
decisions.push(result);
if (result.decision === 'accepted') {
let [name, email, requestedDate, topic, duration, specialNotes, submitter] = request;
finalSchedule.addRow(-1, [name, email, requestedDate, topic, duration, specialNotes, 'False', '', 'Active']);
}
}
// Sort and auto-fit schedule table
sortScheDuleTable(finalSchedule);
finalSchedule.getRange().getFormat().autofitColumns();
// Clean-up requested table row
try {
reqTable.getRangeBetweenHeaderAndTotal().delete(ExcelScript.DeleteShiftDirection.up);
} catch(e) {
// Ignore deletion error as it can happen with two simultanieos submission can clear out the table.
}
console.log(decisions);
return decisions;
}
interface MeetingApproval {
decision: string,
value: string,
email: string,
topic?: string,
date?: string,
startTime?: string,
endTime?: string,
meetingLink?: string
}
interface DataValidation {
request: string[],
dayOfWeek: number,
blockedDays: string,
startTime: string,
endTime: string,
workbook: ExcelScript.Workbook
}
interface MeetingSettings {
dayOfWeek: number,
startTime?: string,
endTime?: string,
meetingLink?: string
}
function nextWeekday(dayOfWeek: number) {
let today = new Date();
var resultDate = new Date();
resultDate.setDate(today.getDate() + (7 + dayOfWeek - today.getDay() - 1) % 7 + 1);
return resultDate.toLocaleDateString();
}
function rejectRequest(value: string, topic: string, date: string, email: string) {
let obj: MeetingApproval = {
decision: 'rejected',
value,
email,
topic,
date
}
return obj;
}
function sortScheDuleTable(table: ExcelScript.Table): void {
// Sort on table: 'finalSchedule' column index: '2'
table.getSort()
.apply([{
key: 2,
ascending: true
}]);
}
function validateRequest({ request = [], dayOfWeek = 1, workbook, blockedDays = '', startTime = '', endTime = '' }: DataValidation): MeetingApproval {
let [name, email, requestedDate, topic, duration, specialNotes, submitter] = request;
if (!validEmail(email)) {
return rejectRequest('Presenter Email is not valid.', topic, requestedDate, email);
}
// If not Day of week, reject
if (new Date(requestedDate) < new Date(nextWeekday(dayOfWeek))) {
return rejectRequest('Past date or too close to meeting date', topic, requestedDate, email);
}
// Get day of the week of requested date
let d = new Date(requestedDate).getDay();
// If not day of week, reject
if (d !== dayOfWeek) {
return rejectRequest('Not the day of week when meeting is held', topic, requestedDate, email);
}
let finalScheduleTable = workbook.getWorksheet('Schedule').getTables()[0];
let currentSchedule = finalScheduleTable.getRangeBetweenHeaderAndTotal().getTexts();
// Get requested duration
let reqTable = workbook.getWorksheet('AddRequest').getTables()[0];
let requestedMins = reqTable.getColumn('Duration (Minutes)').getRangeBetweenHeaderAndTotal().getText();
// Override for testing.
blockedDays = '9/7/2020,Labor Day|10/5/2020,All hands meeting|11/2/2020,Team lunch|11/30/2020,Holiday period|12/21/2020,Holiday period|12/29/2020,Holiday period';
// Get Holiday schedule
let allRows = blockedDays.split('|');
let dates = [];
allRows.forEach(row => dates.push(row.split(',')))
for (let row of dates) {
if (requestedDate === row[0]) {
return rejectRequest('Requested date is reserved for holidays or team event.', topic, requestedDate, email);
}
}
// Check number of minutes already reserved for requested date
let dateMinutes = Number(requestedMins);
for (let row of currentSchedule) {
if (row[2] === requestedDate) {
dateMinutes = Number(row[4]) + dateMinutes;
}
}
// If over-booked, reject
if (dateMinutes > 50) {
return rejectRequest('Schedule is full for this day.', topic, requestedDate, email);
}
return {
decision: "accepted",
value: 'Your requested topic is confirmed. ',
topic,
email,
date: requestedDate,
startTime,
endTime
}
}
function processRequest(request: string[], blockedDays: string, workbook: ExcelScript.Workbook): MeetingApproval {
// setting values
let { dayOfWeek, startTime, endTime, meetingLink } = getMeetingSettings(workbook);
let response = validateRequest({ request, blockedDays, dayOfWeek, startTime, endTime, workbook });
return response;
}
function validEmail(email: string) {
let atSigns = (email.match(/@/g) || []).length;
if (atSigns !== 1) {
return false
}
return /\S+@\S+\.\S+/.test(email)
}
function getMeetingSettings(workbook: ExcelScript.Workbook): MeetingSettings {
// setting values
let dayOfWeek = 1; // Default
let startTime = '11am'; // Default
let endTime = '12pm'; // Default
let meetingLink = '';
// Load setting about meeting schedule
let settingsRange = workbook.getWorksheet('Settings').getRange('A:B').getUsedRange();
let settings = settingsRange.getValues();
for (let row of settings) {
if (row[0] === 'Day(s) of week') {
dayOfWeek = Number(row[1]);
}
if (row[0] === 'Start') {
startTime = row[1];
}
if (row[0] === 'End') {
endTime = row[1];
}
if (row[0] === 'Meeting Link') {
meetingLink = row[1];
}
}
return {
dayOfWeek,
startTime,
endTime,
meetingLink
}
}
