Effortlessly Connect Google Sheets with iObeya QCD

Google Workspace is renowned for its versatility in expanding the functionalities of its services. One such powerful combination lies in seamlessly integrating Google Sheets with iObeya QCD, enabling users to effortlessly inject their data into their iObeya QCD letters and indicators. On the iObeya side, leveraging its robust API Facade, businesses can effortlessly retrieve and transform crucial data, opening doors to visualizing and manipulating information directly within a dedicated board, used in daily performance management routines.

In this article, we delve into a real-life example of this in-house integration, providing a comprehensive step-by-step guide to help you achieve seamless data integration bliss.

Preparing iObeya board

Within this board, one or more QCD letters must be established, accompanied by indicators that will serve as recipients for the injected values. In our straightforward example, technical IDs were employed to reference and establish connections between iObeya elements and Google Sheets. To acquire the ID of a board, simply copy and paste it in the URL from your browser. Similarly, for an individual element, such as a QCD letter, click on it, select the "Display Information" menu, and proceed to click on "Copy Technical ID."

It could have been possible to use the new Data Context feature, which is elaborated upon in this article, for the sake of simplicity, we will directly target elements by leveraging their IDs.

Structuring data in Google Sheets

To ensure smooth integration and facilitate effortless data ingestion by the bulk update indicators values service of the iObeya Facade API, we recommend adopting a simple and intuitive flat data structure. This structure can be easily generated by any system or process. By incorporating just six columns, all mandatory information can be efficiently provided:

iObeya Board: This column houses the unique identifier (ID) of the iObeya board to which the data pertains.
iObeya Letter: Here, you specify the ID of the iObeya letter associated with the data.
Indicator Name: The name of the specific indicator is recorded in this column.
Ring Identifier: To identify the ring in which the target indicator resides within the QCD letter, this column accommodates the corresponding identifier ("outer," "middle," or "inner"). Note that a QCD letter can encompass up to three rings.
Indicator Value: The numerical or qualitative value of the indicator is captured in this column.
Letter Box: This column designates the box within the letter where the indicator value should be injected. The box can represent a day, a week, a month… based on the configuration of the letter and its rings.

Note
It is important to note that recent developments allow for updating a QCD indicator without explicitly specifying the box. Instead, it is now possible to directly utilize a date, providing a more intuitive approach based on how the data is structured in the source system.

Implementing the logic of the integration

The magic will appear in this section.

Fortunately, Google Sheets offers a remarkably straightforward method for in-house automation through the use of scripts, (named macros or scripts in Excel). Initially, the script has to be associated with the current Sheet. However, to enhance scalability and enable broader deployment within an organization, it is advisable to transform this script into an add-on, which can be centrally managed by administrators rather than individual end users. But this is not the purpose of this article.

To begin, open the Google Sheets menu, navigate to "Extensions," and select "Apps Script." A new Apps Script window will appear, where you can easily copy and paste the following code.

Important note
This code serves as an example and is tailored to a specific sheet structure. Proceed with caution and fully comprehend the implications of implementing automation in Google Sheets before utilizing it. It's essential to acknowledge that iObeya does not assume responsibility for maintaining this code or its execution. If you require further information or support, our experts at integrations-support@iobeya.com are available to assist you. They can provide more specific guidance and create specific solutions to meet your unique needs.

//Process the data of a given sheet
//in accordance with the iObeya Facade API service: Bulk update indicators values
function getDataRow() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  let data = sheet.getDataRange();
  let values = data.getValues();

  //"model" definition as excepted in the payload (json)
  let rows = [];
  let row = {};
  let letter = {};
  let indicator = {};
  let wedge = {};

  //Forget the first row
  values.slice(1).forEach((value) => {

    //boardId exists? -> generate the corresponding row
    let indexOfValue = rows.map(function (e) { return e.boardId; }).indexOf(value[0]);
    if (indexOfValue == -1) {
      row = {};
      row.boardId = value[0];
      row.letters = [];
      indexOfValue = rows.push(row) - 1;
    }
    row = rows[indexOfValue];

    //letterId exists? -> add it in the current board
    indexOfValue = row.letters.map(function (e) { return e.id; }).indexOf(value[1]);
    if (indexOfValue == -1) {
      letter = {};
      letter.id = value[1];
      letter.indicators = [];
      indexOfValue = row.letters.push(letter) - 1;
    }
    letter = row.letters[indexOfValue];

    //IndicatorName exists? -> add it in the current letter
    indexOfValue = letter.indicators.map(function (e) { return e.id; }).indexOf(value[2]);
    if (indexOfValue == -1) {
      indicator = {};
      indicator.name = value[2];
      indicator.wedges = [];
      indexOfValue = letter.indicators.push(indicator) - 1;
    }
    indicator = letter.indicators[indexOfValue];
   
    //Add the wedge information
    wedge = {};
    wedge.ring = value[3];
    wedge.value = value[4];
    wedge.wedge = value[5];
    indicator.wedges.push(wedge);
   
  });
  return rows;
}

//Call iObeya Facade API
function updateQCDValues() {
  //Get the token in the properties
  let userProperties = PropertiesService.getScriptProperties();
  let token = userProperties.getProperty('IOBEYA_TOKEN');
  let iobeya_api_url = userProperties.getProperty('IOBEYA_API_URL');

  let requestOptions = {
    method : "post",
    headers : {Authorization : 'Bearer ' + token},
    contentType : "application/json",
    muteHttpExceptions: true,
  };

  let dataRow = getDataRow();
  dataRow.forEach((row) => {
    requestOptions.payload = JSON.stringify(row);
    let res = UrlFetchApp.fetch(iobeya_api_url + '/v1/qcd/indicators-values', requestOptions);
    let json = res.getContentText();
    Logger.log(res);
  });
}

Cherry on the cake, here is a piece of code that create a new menu so user just has to click on it to trigger the integration:

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Update QCD Values",
    functionName : "updateQCDValues"
  }];
  sheet.addMenu("iObeya", entries);
};

Conclusion

In this article, we explored the potential of integrating Google Sheets with iObeya QCD. By following the provided steps, you can effortlessly inject data into iObeya QCD letters and indicators. This integration streamlines data management and enhances collaboration, allowing you to transform raw data into actionable insights within dedicated boards make informed decisions with ease.

Take control of your data management processes by harnessing the power of connecting Google Sheets with iObeya QCD. Empower your organization and unlock the benefits of seamless data integration today.

Looking for more ?

Do you want to learn more about this connector capabilities? Browse the iObeya connector documentation
You are using another RPA/iPaaS platform than Power Automate? Browse the Facade API documentation to discover the API services used with Power Automate
Interested to have an overview of the integration capabilities of the iObeya platform? Access the integrations page

If you have any questions or would like more information about using Power BI and iObeya for your business, please don't hesitate to contact us at integrations-support@iobeya.com. Our 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

Google Sheets Data to QCD Indicators