Creating Teams Meetings from Excel Spreadsheet

[VWilliamson]

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

My table looks like this: 

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?

[sumurthy · Accepted solution]

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

Script

1. Open Office Script from Excel for web. Paste the script below - 

2. Create a Flow

.... answer continued in next post (this editor is buggy; won't let me save scripts)

[sumurthy]

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

[sumurthy]

@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