How to Automatically push Excel Charts in iObeya Images thanks to PowerAutomate

As a visual management platform, iObeya provides an intuitive way to manage data, collaborate and make decisions. However, updating images on boards can be time-consuming and repetitive. That's why using the iObeya Button Tool in combination with Power Automate can be a game-changer. In this article, we'll show you how to use Power Automate to extract chart images from an Excel file and update the images on your iObeya board.

Step 1: Prepare Your Excel File

In this example, we will use an Excel file that will map the chart names and the iObeya board image IDs. To get the image IDs, simply go to the element menu, select Properties, and “Copy technical ID”.

Then, in a specific worksheet, we populate a table with the names of the charts in one column and the IDs of iObeya images in a second column.

This Excel file will serve as a source of information for the Power Automate Flow.

Step 2: Create a Power Automate Flow with an HTTP Request Trigger

We will now create a Power Automate flow that will be triggered by an HTTP request.

 

To do this, follow these steps:

  • Go to the Power Automate website.
  • Click on the "Create a flow" button.
  • Select "Instant - from blank" and then choose "When a HTTP request is received".
  • Fill in the details of the HTTP request.
  • Save the flow.
  • After saving the flow, you can copy the URL that will trigger it.

Step 3: Add a Button to Your iObeya Board

We will now add a button in the iObeya board. To do that, simply pick up the Button tool from the dock, place it on the board, and set the URL to the HTTP Request trigger created in Step 2.

 

Then, set the Button label to "Update Images" and select the event "Execute action". Finally, save the button.

Note
The Button tool is a feature available from the version 4.16 of iObeya

Step 4: Run Excel Script to Extract Chart Images

In Power Automate, we need to add an action to run an Excel script that will extract chart images from the Excel file.

The script uses the chart names and image IDs defined in the previous table to identify the correct image to extract.

 

Then, it will return it so PowerAutomate can get it. Here is a complete example a such script.

 

It just has to put added in the Excel file following this Microsoft guideline for instance.

let
// The interface for table and chart images.
class iObeyaChart {
imageBase64: string
iObeyaBoardImageId: string
}

function main(workbook: ExcelScript.Workbook): iObeyaCharts {
// Recalculate the workbook to ensure all tables and charts are updated.
workbook.getApplication().calculate(ExcelScript.CalculationType.full);

// Configuration
let chartSettingsSheet = workbook.getWorksheet("ChartSettings");
let chartSettings = chartSettingsSheet.getTable("ChartSettings");
let rangeChartSettings = chartSettings.getRangeBetweenHeaderAndTotal();
let chartImages = new Array();
// Big size for better quality image
let ratio = 4;

// Get the data from the "InvoiceAmounts" table.
let sheets = workbook.getWorksheets();
sheets.forEach((sheet) => {
const charts = sheet.getCharts();

// Loop on each chart
charts.forEach((chart) => {
let ic = new iObeyaChart();
// Retrieve iObeya images boards ID
let boardImageRange = rangeChartSettings.find(chart.getName(), {
completeMatch: true,
matchCase: false
});
if (boardImageRange != null) {
let found = chartSettingsSheet.getRange(boardImageRange.getAddress().replace('A', 'B'));
ic.iObeyaBoardImageId = found.getValues()[0][0] as string;
ic.imageBase64 = chart.getImage(chart.getWidth() * ratio, chart.getHeight() * ratio);
chartImages.push(ic);
}
});
});
console.log(chartImages);
return { chartImages };
}

// The interface for table and chart images.
interface iObeyaCharts {
chartImages: Array
}

interface iObeyaChartResult {
imageBase64: string
iObeyaBoardImageId: string
}


Step 5: Use the iObeya Connector to Update the Board Image

To complete the integration between Power Automate and iObeya, we need to add two actions to the Power Automate flow: "Compose" and "Update asset of a board image". These actions will work together to ensure that the image extracted from the Excel file is properly formatted and then uploaded to the iObeya board.

Add a Compose Action

  1. In the Power Automate flow, click on the "+ New step" button and select the "Compose" action.
  2. Name the action "Transform Excel Image to Binary" or something similar.
  3. In the "Inputs" field, paste the output from the previous step which is the image in base64 format.
  4. In the "Function" field, enter the following expression: "base64ToBinary(<inputs>)".
  5. Replace "<inputs>" with the output from the previous step.
  6. Save the Compose action.

Add the "Update asset of a board image" Action

  1. Click on the "+ New step" button and select the "Update asset of a board image" action from the iObeya connector.
  2. In the "Board Image ID”, choose the iObeyaBoardId from the excel’s script.
  3. In the "File to upload" field, select the output from the "Transform Excel Image to Binary" Compose action.
  4. In the "File to upload (file name)", set the name that you want. 
  5. Save the "Update asset of a board image" action.

Step 6: Trigger the Flow

To trigger the Flow, a simple double-click on the iObeya Button Tool added in Step 3 will do the job.

 

The Flow will run, extract the chart images from the Excel file, and update the images on the iObeya board.

Conclusion

By using Power Automate and Excel in combination with the iObeya Button Tool, you can automate the process of updating images on your iObeya board. This will save you time and effort, allowing you to focus on making data-driven decisions

Looking for more ?

If you have any questions or would like more information about using iObeya APIs for your business, please don't hesitate to contact us at integrations-support@iobeya.comOur team of experts is available to provide additional guidance and support, and can also offer personalized demonstrations of these powerful tools to help you get the most out of them. We look forward to hearing from you and helping you achieve your business goals with iObeya.

Type of integration

Excel charts to iObeya images via a PowerAutomate Flow

Tags

ChartExcelImagesPowerAutomate

Tools