Analytics

KPI output from Google spreadsheet

Widget for displaying KPI indicators of managers from Google Sheets directly to the Kommo desktop

Features

Displaying KPI indicators from Google Sheets
Individual settings for each manager
Dragging card on screen
Setting up table cells for metrics

Installation and setup guide

Detailed guide for working with widget "KPI output from Google spreadsheet"

CONTENTS

  1. Widget settings
  2. Autoloading fields

Widget settings

First you need to create a table

* Next, go to the widget settings.
* Insert a link to the Google Spreadsheet

* Next, you need to select a manager

* A block with the selected managers will appear

* When you click on it, two fields will appear for entering the Indicator name and the Indicator Cell.
* In the Indicator Name field, you must enter a name, for example "Number of successful leads".

* Next, we go to the table and find the cell with the indicators of this manager and copy the cell number.
* Insert a value in the Indicator Cell field

Next, you can add all the indicators by clicking on the Add Field button.

Autoloading fields

After preparing the data table:
* Go to "Extensions" -> "Apps Script"

Paste the following code into the editor:

function createTrigger() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() === 'onEdit') {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
  ScriptApp.newTrigger('onEdit').forSpreadsheet(spreadsheet).onEdit().create();
}

function onEdit(e) {
  try {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var editedSheet = e.source.getActiveSheet();
    var sheetId = editedSheet.getSheetId();
    var baseUrl = spreadsheet.getUrl();
    var fullUrl = baseUrl + (baseUrl.includes('?') ? '&' : '?') + 'gid=' + sheetId + '#gid=' + sheetId;
    updateCache(fullUrl, "subdomain");
  } catch (error) {}
}

function updateCache(sheetUrl, subdomain) {
  try {
    UrlFetchApp.fetch("https://sp1-nova.ru/api/gt-cache-update/", {
      method: "post",
      contentType: "application/x-www-form-urlencoded",
      payload: {url: sheetUrl, subdomain: subdomain},
      muteHttpExceptions: true
    });
  } catch (error) {}
}

Instead of subdomain, you need to write your subdomain without .kommo.com

* Then click on Save
* Select createTrigger and click run, give all permissions
* Click on Run

Now, when the table is changed, it will be automatically uploaded to Kommo.