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