How to Output Data to Excel Online From the Cloud-based Workflow Questetra (API Integration Setting Procedure)

Hi there!

It’s Kusaka here again, back with another blog about inter-system collaboration.

After consulting with a customer about whether data could be output to Excel Online, I was able to implement an investigation and I had just done, after all. The following summarizes how to set up that link.

1: Background

In the past, Google Sheets has been implemented when we wanted to output the data from Questetra to a spreadsheet. This is one of the most common needs.

※Related add-on: 「Google Spreadsheet: Add Row」

2: Settings for linking Excel Online and Questetra

Here is a previous article about integration with Office 365, including Excel Online.
※Related article: How to Cooperate between Office 365 and Cloud Based Workflow
As described in the above article, if you used an Authorization Code with the Azure AD v2.0 endpoint Questetra can only support it by using OAuth2. That was also the case this time.

2.1: OAuth Configuration on Excel Online (Office 365) Side

Please obtain the “Client ID” and “Client Secret” by referring to the following article:
　Application Registration Procedure in Office 365 for Linking Office 365 and Questetra Cloud Workflow with API

2.2: OAuth Configuration on Questetra side

This is the same as in the section “2.2: OAuth setting on Questetra side” in the following article. Please refer to this article as the scope may be the same.
　How to Output Files from Cloud-based Workflow Questetra to OneDrive

3: Settings to Call Excel Online API from Questetra

When preparing the settings there are various difficulties. It is assumed the target Excel workbook has been created manually in advance.
(In the case of Google Sheets, the ID of the sheet necessary for API access can be taken from the access URL of that sheet, whereas in the case of Excel Online, the access URL contains a different ID and cannot be taken easily.)
This time, we will use the “table” of the Excel workbook. If you are interested in that term, please search the web. The reason why a table is necessary is that there are APIs for updating Excel cell values ​​by specifying the cell position on the sheet and APIs for adding rows by specifying a table, moreover, it was easier to do the latter this time. Therefore, please create the table manually.

The preparation is summarized as follows:

• To update the value of a cell in an Excel Online sheet, a table-id (ID of the table in the Excel workbook) is required
• Worksheet-id (ID of the sheet in the Excel workbook) is required to obtain the table-id
• Workbook-id (Excel workbook ID) is required to obtain the worksheet-id
• In order to get the workbook-id, it is necessary to get the list of DriveItems with the OneDrive API
• To get a list of DriveItems, access from MyDrive on OneDrive is required.
  (Because the DriveItem list is acquired for each folder, if the target Excel workbook is not directly under My Drive, it is necessary to follow the folder. You must acquire the DriveItem list each time …)

First, the information required for access is prepared in the first half, and the data update is explained in the second half.

3.1: Preparation for Excel Online Sheet Access

In order to be able to prepare as easily as possible, we have created a place to take a table-id in the Questetra workflow App. A workbook-id and table-id are required information for 3.2.
You can click the archive below to download the workflow App:
　Workflow application archive (Get Excel Online id)）
※If you unzip the file, a qar file will be included. The archive file can be imported as a template. For details, please refer to the page Using a Business Template for the First Time. In addition, OAuth settings for each Throwing Message Intermediate Event (HTTP) are required to make it work.
※In this workflow App, mandatory checks and error handling are not very strict. However, an error occurs if a DriveItem is not selected or if the selected file is not an Excel workbook. Please be aware of this.

A brief explanation of what is being done is as follows:
(1) Acquire the drive-id from the response by sending an HTTP request using GET as the HTTP Method to the following URL.

https://graph.microsoft.com/v1.0/me/drive

(2) Based on the acquired drive-id, get the list of DriveItems by sending an HTTP request using GET as the HTTP Method to the following URL.

https://graph.microsoft.com/v1.0/drives/{drive-id}/root/children

(3) Select the target from the list of acquired DriveItems (when the target file is Excel, driveitem-id becomes workbook-id)
　If the selected target is a file, get a list of worksheet-ids by sending an HTTP request using GET as the HTTP Method to the following URL.

https://graph.microsoft.com/v1.0/me/drive/items/{workbook-id}/workbook/worksheets

　If the selected target is a folder, get the DriveItems and return to the beginning of (3) by sending an HTTP request using GET as the HTTP Method to the following URL.

https://graph.microsoft.com/v1.0/drive/items/{driveitem-id}/root/children

(4) Select the target from the list of acquired worksheet-ids

(5) Get the cell value by sending an HTTP request using GET as the HTTP Method to the following URL. (This process is not necessary, but I added it to confirm that worksheet-id works as intended.)
(I think that there might be an API implementation error because I need “%7B” / “%7D” URL-encoded as “{” / “}”)

https://graph.microsoft.com/v1.0/me/drive/items/{wokbook-id}/workbook/worksheets('%7B{worksheet-id}%7D')/range(address='A1')

(6) Acquire the list of tables by sending an HTTP request using GET as the HTTP Method to the following URL.

https://graph.microsoft.com/v1.0/me/drive/items/{workbook-id}/workbook/worksheets('%7B{worksheet-id}%7D')/tables

(7) Get the table-id of the target table from the list of acquired tables

3.2: Excel Online Sheet Value Update

Finally preparation has been completed.
For the sake of simplicity, we will add a table row with only two items.

When JSON data like the following format is POSTed, a value is entered in the cell.

https://graph.microsoft.com/v1.0/me/drive/items/{workbook-id}/workbook/tables('%7B{table-id}%7D')/rows
{ "values": [ [ "{data1}", "{data2}" ] ], "index": null }

The following is represented as a Questetra workflow App.
You can download the archived workflow App below.
　 Workflow application archive (add Excel Online table row)

It worked as follows.

4: Summary

It was quite hard work. Even though I’ve seen various APIs, I think Office 365 is the strongest class.

For Excel, we believe there is a need to start all business processes from a list of sheets, as demonstrated with Google Sheets.
With Google Sheets it was possible to embed a Google Apps Script (we also created an add-on so that it can be used immediately).
※Reference article: How to Start Workflows in Bulk with Google Sheets

Unfortunately, Excel Online doesn’t have such a mechanism yet, so it seems that I will have to try to do it with the API or the Office addin mechanism. If it can be implemented I want to summarize that in an article.
By the way, it can be realized with VBA from the client application Excel.

The road is quite steep, but since there are quite a lot of consultations about Office 365 recently, I would like to increase the number of examples.

If you have any questions, please feel free to contact us.

※Reference documents
Working with files in Microsoft Graph
Working with Excel in Microsoft Graph