cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Creating Teams Meetings from Excel Spreadsheet

I am looking to create individual team meetings with data from an excel spreadsheet. 

 

My table looks like this: 

VWilliamson_0-1604068970310.png

 

So, I've built a flow to read the rows, however it will not read the date and time correctly as it is reading it as text. I've spent HOURS trying to convert but, having read many forums and watching youtube, I'm not getting any closer.

 

At what stage do I convert the date and time, how do I convert it and how do I put the result into the meeting timeslots?

 

VWilliamson_1-1604069312678.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

For this scenario you can use Office Scripts, which allows a TypeScript language based script to be run on Excel file stored in OneDrive or SharePoint.
For your scenario, you'll need the following: 
1. An Office Script that formats and extracts the array of data you need for Teams meeting action. 
2. Send the output to Teams meeting (it'll do apply-for-each)
 

Input

t2.png

 
Script
1. Open Office Script from Excel for web. Paste the script below - 
scr.png
2. Create a Flow
fl.png
 
scr.png
 
 
.... answer continued in next post (this editor is buggy; won't let me save scripts)

View solution in original post

3 REPLIES 3
Microsoft
Microsoft

For this scenario you can use Office Scripts, which allows a TypeScript language based script to be run on Excel file stored in OneDrive or SharePoint.
For your scenario, you'll need the following: 
1. An Office Script that formats and extracts the array of data you need for Teams meeting action. 
2. Send the output to Teams meeting (it'll do apply-for-each)
 

Input

t2.png

 
Script
1. Open Office Script from Excel for web. Paste the script below - 
scr.png
2. Create a Flow
fl.png
 
scr.png
 
 
.... answer continued in next post (this editor is buggy; won't let me save scripts)

View solution in original post

For the above response, use this script 

function main(workbook: ExcelScript.Workbook): MeetingDetail[] {
  const sheet = workbook.getWorksheet('Sheet8');
  const table = sheet.getTables()[0];
  table.getColumnByName('Start time').getRangeBetweenHeaderAndTotal().setNumberFormatLocal("[$-en-US]m/d/yyyy h:mm AM/PM;@");
  table.getColumnByName('Finish time').getRangeBetweenHeaderAndTotal().setNumberFormatLocal("[$-en-US]m/d/yyyy h:mm AM/PM;@");
  const dataRows: (string | number | boolean)[][] = table.getRange().getTexts();
  // or
  // let dataRows = sheet.getUsedRange().getValues();
  const selectedRows = dataRows.filter((row, i) => {
    // Select header row and any data row with the status column equal to approach value
    return (row[1] === 'FALSE' || i === 0)
  })
  const output: MeetingDetail[] = returnObjectFromValues(selectedRows as string[][]);
  return output;
}

/**
 * This helper funciton converts table values into an object array.
 */
function returnObjectFromValues(values: string[][]): MeetingDetail[] {
  let objArray = [];
  let objKeys: string[] = [];
  for (let i = 0; i < values.length; i++) {
    if (i === 0) {
      objKeys = values[i]
      continue;
    }
    let obj = {}
    for (let j = 0; j < values[i].length; j++) {
      obj[objKeys[j]] = values[i][j]
    }
    objArray.push(obj);
  }
  console.log(JSON.stringify(objArray));
  return objArray as MeetingDetail[];
}

interface BasicObj {
  [key: string]: string | number | boolean
}

interface MeetingDetail extends BasicObj {
  'ID': string
  'Invite to interview': string
  'Candidate': string
  'Candidate email': string
  'Interviewer1': string
  'Interviewer1 email': string
  'Interviewer2': string
  'Interviewer2 email': string
  'Interviewer3': string
  'Interviewer3 email': string
  'Start time': string
  'Finish time': string
}

 

Output

[{
	"ID": "10",
	"Invite to interview": "FALSE",
	"Candidate": "Adele ",
	"Candidate email": "Adele@mail",
	"Interviewer1": "Vance",
	"Interviewer1 email": "Vance@mail",
	"Interviewer2": "Debra ",
	"Interviewer2 email": "Debra@mail",
	"Interviewer": "Berger",
	"Interviewer3 email": "Berger@mail",
	"Start time": "10/30/2020 2:00 PM",
	"Finish time": "10/30/2020 2:30 PM"
}, {
	"ID": "30",
	"Invite to interview": "FALSE",
	"Candidate": "Allan ",
	"Candidate email": "Allan@mail",
	"Interviewer1": "Deyoung",
	"Interviewer1 email": "Deyoung@mail",
	"Interviewer2": "Diego ",
	"Interviewer2 email": "Diego@mail",
	"Interviewer": "Siciliani",
	"Interviewer3 email": "Siciliani@mail",
	"Start time": "10/30/2020 3:30 PM",
	"Finish time": "10/30/2020 4:00 PM"
}]
Microsoft
Microsoft

@VWilliamson 

I made a video of the solution here if you like to see - https://github.com/sumurthy/officescripts-projects/tree/main/Excel%20and%20Teams%20Invite

 

I want to alert you about an issue I noticed around dates. 

There could be a mis-match between time-zone in Excel and 

Helpful resources

Announcements
PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Top Solution Authors
Users online (3,867)