Skip to main content
Power Automate
    • Robotic process automation
    • Power Automate Desktop
    • Process advisor
    • AI Builder
    • Connectors
    • Templates
  • Pricing
    • Blog
    • Documentation
    • Roadmap
    • Self-paced learning
    • Webinar
    • Overview
    • Issues
    • Give feedback
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • User groups
    • Register
    • ·
    • Sign In
    • ·
    • Help
    Go To
    • Microsoft Power Automate Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power Automate
    • General Power Automate Discussion
    • Connecting To Data
    • Building Flows
    • Using Flows
    • Power Automate Desktop
    • AI Builder
    • Power Automate Mobile App
    • Translation Quality Feedback
    • Share, Chat, Discuss
    • Community Chat & Announcements
    • Power Automate Cookbook
    • Off Topic Discussion
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Watch & Share Videos
    • Webinars and Video Gallery
    • MBAS Gallery 2019
    • MBAS Gallery 2020
    • Give Feedback & Share Ideas
    • Power Automate Ideas
    • Report Issues
    • I Found A Bug
    • Find & Join Local User Groups
    • Power Automate User Groups
    • Read & Share Blogs
    • Power Automate Community Blog
    • Community Blog Topic Suggestions
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power Automate Community
    • Share, Chat, Discuss
    • Power Automate Cookbook
    • Excel and Microsoft Forms integration using Office...

    Excel and Microsoft Forms integration using Office Scripts

    10-24-2020 17:56 PM

    Microsoft sumurthy
    Microsoft
    552 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    sumurthy
    Microsoft sumurthy
    Microsoft
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    Excel and Microsoft Forms integration using Office Scripts

    ‎10-24-2020 05:56 PM

    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

    End to end flowEnd to end flow

    Forms to Excel data flow:

    Zooming in on Forms to Excel data flowZooming in on Forms to Excel data flow

    Final part - emailing result

    Final part - email the resultFinal part - email the result

    Meeting request holding table:

    Meeting request holding tableMeeting request holding table

    Final schedule:

    Final scheduleFinal schedule

     

    Holiday schedule

    Team events and holidaysTeam events and holidays

    Microsoft Forms

    Form to get presentation requestForm to get presentation request

     

    Script listing

    Required scriptsRequired scripts

     

    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
        }
    }

     

    images.zip
    Labels:
    • Labels:
    • Automated flows
    • Business process flows
    • Solutions
    Message 1 of 1
    552 Views
    1 Kudo
    Reply
    • All posts
    • Previous Topic
    • Next Topic
    Preview Exit Preview

    never-displayed

    You must be signed in to add attachments

    never-displayed

    Additional options
    You do not have permission to remove this product association.
     

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Automate
    • Power Virtual Agents
    • Sign up free
    • Sign in

    Browse

    • Templates
    • Connectors
    • Partners

    Downloads

    • Mobile
    • Gateway

    Learn

    • Documentation
    • Learn
    • Support
    • Community
    • Give feedback
    • Blog
    • Pricing
    • © 2021 Microsoft
    • Twitter
    • RSS Feed
    • YouTube
    • Contact us
    • Trademarks
    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Terms & conditions