A bot can be used to integrate with a linked Excel spreadsheet available on your OneDrive.
Note: The example used here is based on a Sales Demo spreadsheet that should sit in the root directory of your OneDrive. In future we will support documents in different folders. The Sales Demo spreadsheet used here has a few cells that are named for each month of the year, as well as a total, and an overview chart.
As noted above, the Excel document that needs to be integrated with has to sit in the root directory of the user's OneDrive. Every user requires a copy of this document. Additionally, all the fields that values are to be returned for, has to be named in the Excel document. We do not support cell numbers as reference.
A chart can also be returned to a bot through a thumbnail which will link to the actual chart. At the moment, only charts in the first worksheet of the document can be returned by the bot. The Graph API doesn't return names for charts, so we can only return the first chart found. This functionality will be expanded as the API improves.
This ability only has one setting, which is the name of the document to be interrogated. It is called OneDrive Path and can be changed in the bot designer to point to another document.
Using the Excel ability
When linked to a spreadsheet, the bot can provide the values of requested cells. As mentioned in the note above, the sheet used here has cells named according to each month of the year. Below is an example of how to return a particular cell's value:
- Sales for May (any month of the year can be used, as well as the Total)
Show chart screenshot
When a spreadsheet contains a chart, the bot can be used to return a screenshot of the chart in its current state. This can be done as follows:
- Show chart (this sheet only contains one chart; when more than one chart is available, the name of said chart should be referenced)
List Named Values
When linked to a spreadsheet, the bot can provide you with a list of values that have been marked as being able to be retrieved with the check value ability. Below is an example of how to get the list:
- List named items