Last active
April 4, 2019 04:20
-
-
Save Rick-Kirkham/023d8df769ddaeaf7cd1207f8a06d758 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
name: Basic Operational Call - 1 | |
description: Executes a basic call using the Operational API | |
host: EXCEL | |
api_set: {} | |
script: | |
content: > | |
// Get the Operational workbook | |
// In a true NPM world, this will be: | |
// import Excel from 'excel'; | |
// const { workbook } = new Excel(); | |
const { workbook } = new ExcelOp.Excel(); | |
// Bind button handler to run function | |
$("#run").click(run); | |
async function run() { | |
const posts = await getPosts(); | |
const allUserIDs = workbook.worksheets.getItem("data").getUsedRange(); | |
// Group data into arrays by user ID. | |
const allPostsData = await getPostsDataByUser(allUserIDs, posts); | |
populateSheet(allUserIDs, allPostsData); | |
// Hide all but first 3 posts for a given user. | |
await hideExcessPosts(); | |
} | |
async function getPosts(): Promise<Array<Post>> { | |
const data = await fetch("https://jsonplaceholder.typicode.com/posts"); | |
const jsonArray = await data.json(); // Returns an array of posts | |
return jsonArray; | |
} | |
async function getPostsDataByUser(allUserIDs: ExcelOp.Range, posts: Post[]): | |
Promise<Array<any>> { | |
const { values } = await allUserIDs.retrieve("values"); | |
const allPostsData = []; | |
values.forEach(function (value) { | |
let dataByUserId = postsByUserId(posts, value[0]); | |
if (dataByUserId.length) { | |
dataByUserId.forEach(function (item) { | |
allPostsData.push(item); | |
}) | |
} else { | |
// No posts were found for the user, but we want | |
// to preserve the user's row in the worksheet. | |
allPostsData.push([value[0], null, null, null]); | |
} | |
}) | |
return allPostsData; | |
} | |
function postsByUserId(posts: Post[], id: number): any[] { | |
let data = []; | |
posts.forEach(function (post, index) { | |
if (post.userId === id) { | |
// If data has 0 length, this is the 1st row | |
// for this user ID, so include it in the data. | |
if (!data.length) { | |
data.push([ | |
posts[index].userId, | |
posts[index].id, | |
posts[index].title, | |
posts[index].body | |
]); | |
} else { | |
// The previous row has the same userId, so pass | |
// empty string for the first column. | |
data.push([ | |
"", | |
posts[index].id, | |
posts[index].title, | |
posts[index].body | |
]); | |
} | |
} | |
}) | |
return data; | |
} | |
function populateSheet(allUserIDs: ExcelOp.Range, allPostsData: any[]): void | |
{ | |
const finalRange = allUserIDs.getAbsoluteResizedRange(allPostsData.length, 4); | |
finalRange.update({ | |
values: allPostsData, | |
format: { | |
verticalAlignment: "Top", | |
} | |
}); | |
finalRange.format.autofitColumns(); | |
finalRange.format.autofitRows(); | |
} | |
async function hideExcessPosts(): Promise<void> { | |
const userIDRange = workbook.worksheets.getItem("data") | |
.getUsedRange().getIntersection("A:A"); | |
const userIDValues = await userIDRange.retrieve("values"); | |
let consecutiveBlankRows = 0; | |
userIDValues.values.forEach(function (value, index) { | |
if (value[0]) { | |
// There's an ID in this row, so reset consecutiveBlankRows | |
consecutiveBlankRows = 0; | |
} else { | |
consecutiveBlankRows++; | |
if (consecutiveBlankRows > 2) { | |
let rowToHide = userIDRange.getCell(index, 1).getAbsoluteResizedRange(1, 4); | |
rowToHide.update({ | |
rowHidden: true | |
}); | |
} | |
} | |
}); | |
} | |
interface Post { | |
userId: number; | |
id: number; | |
title: string; | |
body: string; | |
} | |
language: typescript | |
template: | |
content: | | |
<p class="ms-font-m">Usability Study Test:</p> | |
<button id="run" class="ms-Button"> | |
<span class="ms-Button-label">Run Task</span> | |
</button> | |
language: html | |
style: | |
content: '' | |
language: css | |
libraries: > | |
https://unpkg.com/@microsoft/office-js@operational/dist/office.experimental.js | |
https://unpkg.com/@microsoft/office-js@operational/dist/office.experimental.d.ts | |
office-ui-fabric-js@1.4.0/dist/css/fabric.min.css | |
office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css | |
core-js@2.4.1/client/core.min.js | |
@types/core-js | |
@microsoft/office-js-helpers@0.7.4/dist/office.helpers.min.js | |
@microsoft/office-js-helpers@0.7.4/dist/office.helpers.d.ts | |
jquery@3.1.1 | |
@types/jquery@3.3.1 | |
whatwg-fetch | |
@types/whatwg-fetch |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
name: Op Model Study - Task 6 Ricky | |
description: '' | |
host: EXCEL | |
api_set: {} | |
script: | |
content: > | |
// Get the Operational workbook | |
// In a true NPM world, this will be: | |
// import Excel from 'excel'; | |
// const { workbook } = new Excel(); | |
const { workbook } = new ExcelOp.Excel(); | |
// Bind button handler to run function | |
$("#run").click(run); | |
async function run() { | |
const posts = await getPosts(); | |
const allUserIDs = workbook.worksheets.getItem("data").getUsedRange(); | |
// Group data into arrays by user ID. | |
const allPostsData = await getPostsDataByUser(allUserIDs, posts); | |
populateSheet(allUserIDs, allPostsData); | |
// Hide all but first 3 posts for a given user. | |
await hideExcessPosts(); | |
} | |
async function getPosts(): Promise<Array<Post>> { | |
const data = await fetch("https://jsonplaceholder.typicode.com/posts"); | |
const jsonArray = await data.json(); // Returns an array of posts | |
return jsonArray; | |
} | |
async function getPostsDataByUser(allUserIDs: ExcelOp.Range, posts: Post[]): | |
Promise<Array<any>> { | |
const { values } = await allUserIDs.retrieve("values"); | |
const allPostsData = []; | |
values.forEach(function (value) { | |
let dataByUserId = postsByUserId(posts, value[0]); | |
if (dataByUserId.length) { | |
dataByUserId.forEach(function (item) { | |
allPostsData.push(item); | |
}) | |
} else { | |
// No posts were found for the user, but we want | |
// to preserve the user's row in the worksheet. | |
allPostsData.push([value[0], null, null, null]); | |
} | |
}) | |
return allPostsData; | |
} | |
function postsByUserId(posts: Post[], id: number): any[] { | |
let data = []; | |
posts.forEach(function (post, index) { | |
if (post.userId === id) { | |
// If data has 0 length, this is the 1st row | |
// for this user ID, so include it in the data. | |
if (!data.length) { | |
data.push([ | |
posts[index].userId, | |
posts[index].id, | |
posts[index].title, | |
posts[index].body | |
]); | |
} else { | |
// The previous row has the same userId, so pass | |
// empty string for the first column. | |
data.push([ | |
"", | |
posts[index].id, | |
posts[index].title, | |
posts[index].body | |
]); | |
} | |
} | |
}) | |
return data; | |
} | |
function populateSheet(allUserIDs: ExcelOp.Range, allPostsData: any[]): void | |
{ | |
const finalRange = allUserIDs.getAbsoluteResizedRange(allPostsData.length, 4); | |
finalRange.update({ | |
values: allPostsData, | |
format: { | |
verticalAlignment: "Top", | |
} | |
}); | |
finalRange.format.autofitColumns(); | |
finalRange.format.autofitRows(); | |
} | |
async function hideExcessPosts(): Promise<void> { | |
const userIDRange = workbook.worksheets.getItem("data") | |
.getUsedRange().getIntersection("A:A"); | |
const userIDValues = await userIDRange.retrieve("values"); | |
let consecutiveBlankRows = 0; | |
userIDValues.values.forEach(function (value, index) { | |
if (value[0]) { | |
// There's an ID in this row, so reset consecutiveBlankRows | |
consecutiveBlankRows = 0; | |
} else { | |
consecutiveBlankRows++; | |
if (consecutiveBlankRows > 2) { | |
let rowToHide = userIDRange.getCell(index, 1).getAbsoluteResizedRange(1, 4); | |
rowToHide.update({ | |
rowHidden: true | |
}); | |
} | |
} | |
}); | |
} | |
interface Post { | |
userId: number; | |
id: number; | |
title: string; | |
body: string; | |
} | |
language: typescript | |
template: | |
content: | | |
<p class="ms-font-m">Usability Study Test:</p> | |
<button id="run" class="ms-Button"> | |
<span class="ms-Button-label">Run Task</span> | |
</button> | |
language: html | |
style: | |
content: '' | |
language: css | |
libraries: > | |
https://unpkg.com/@microsoft/office-js@operational/dist/office.experimental.js | |
https://unpkg.com/@microsoft/office-js@operational/dist/office.experimental.d.ts | |
office-ui-fabric-js@1.4.0/dist/css/fabric.min.css | |
office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css | |
core-js@2.4.1/client/core.min.js | |
@types/core-js | |
@microsoft/office-js-helpers@0.7.4/dist/office.helpers.min.js | |
@microsoft/office-js-helpers@0.7.4/dist/office.helpers.d.ts | |
jquery@3.1.1 | |
@types/jquery@3.3.1 | |
whatwg-fetch | |
@types/whatwg-fetch |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment