Integration of Google Apps Script with the Kommo CRM API: How We Solved the Problem and Automated Processes

At one point, we faced a challenge that initially seemed quite difficult for us. It was an issue with integrating OAuth 2.0, an authorization standard that allows users to securely grant access to their data to other services.

This was a new experience for our team, as we had never dealt with such a task before, and we couldn't find direct instructions or guides. We encountered constant errors, and simple access denial without explanation became a real challenge.

In this article, we will discuss how Google Apps Script helped transform our processes and solve the integration problem.

#1 Make didn't help

OAuth (short for Open Authorization) is an open authorization standard that allows users to grant access to their private data (photos, videos, contact lists) stored on one site to another site without needing to enter a username and password. OAuth 2.0 focuses on simplifying client-side development by providing specific authorization flows for web applications, desktop applications, and mobile phones.

We had a problem: we couldn't figure out how to properly implement OAuth integration. It even got to the point where two developers had identical solutions, but one worked and the other didn't because of a single extra letter in the request. Regarding access, it simply displayed: "Access denied" without any explanation whenever we attempted it. We needed to correctly create the Kommo integration and properly connect it in Make.

Attempting to solve this problem through the Make service did not yield the desired result due to its limitations. Therefore, we decided to turn to Google Apps Script, which ultimately became our solution. Vlad, one of our team members, found a way to handle the authorization, and this became a turning point in our work.

How We Reached the Solution

During our work, we encountered the need to automate certain processes, particularly accessing data through authorization and simplifying repetitive tasks that consumed a lot of time and effort. The solution to this problem was using Google Apps Script, which allowed us to create a library with useful functions for automating various tasks.

The first thing we realized was that the tools provided by the Make service could be adapted to our needs through additional automation. For example, we could generate an API key (APK), use it to create requests, and gain access to the necessary information.

The main task we solved with this library was to simplify the authorization process and optimize routine tasks. This not only addressed data access issues but also made working with the data more convenient and faster. With the library, we could easily access lead data from the Kommo system, execute various requests for retrieving or adding information, and analyze this data.

As a result, the library we developed proved to be an effective tool for automating a wide range of tasks: from user authorization and data access to executing complex queries and analyzing the obtained information. This significantly increased our productivity and simplified many processes that previously seemed complex and time-consuming.

Efficient Development: Using Existing Library Methods for Adapting to Specific Tasks

If you plan to develop new functionality related to processing data from Kommo, such as parsing specific information, there's no need to write a completely new function from scratch. You can utilize the existing methods in our library, which already contain the basic logic for working with data. This means that fundamental operations, like retrieving a list of leads, are already implemented in the library.

By choosing the appropriate method from the library, you can add additional logic or conditions specific to your task without altering the method itself. This greatly simplifies the development process, as you don't need to write code for standard operations. You simply use the existing tools and focus on the unique logic of your task.

#2 How the Code Looks

function getKOMMOAuthToken() {
  var projectID = "someId";
  var datasetID = "someDataSetId";
  var clientId = "someClientId";
  var clientSecret = "someClientSecret";
  var redirectUris = "someRedirectUris";
  var authorizationCode = getRefreshToken_(projectID);
  var url = "someUrl";
  // Define the token request parameters
  var payload = {
    "client_id": clientId,
    "client_secret": clientSecret,
    "grant_type": 'refresh_token',
    "refresh_token": authorizationCode,
    "redirect_uri": redirectUris
  };
  // Make the token request
  var data = {
    method: "POST",
    headers:{
      "Content-Type": "application/json"
    },
    payload: JSON.stringify(payload)
  };
  var response = UrlFetchApp.fetch(url, data);
  var tokenData;
  var refreshToken;
  var accessToken;
  if (response.getResponseCode() === 200) {
    tokenData = JSON.parse(response.getContentText());
    Logger.log(JSON.parse(response.getContentText()));
    accessToken = tokenData.access_token;
    refreshToken = tokenData.refresh_token;
  } else {
    Logger.log("Error: " + JSON.parse(response.getContentText()));
  
  }
  setRefreshToken_(projectID, refreshToken);
  setAccessToken_(projectID, accessToken);
}
function setRefreshToken_(projectID ,refreshToken) {
  //var ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active spreadsheet
  // var sheet = refreshTokenSS_.getSheetByName('refresh_token'); // Get the "refresh_token" sheet
  // sheet.getRange('A1').setValue(refresh_token); // Set the value in cell A1
  var refreshTokenTableID = "iampm-stats.IAMPM_KOMMO_API.refresh_token";
  var sqlQuery_PutRefreshToken = `INSERT INTO ${refreshTokenTableID} (refresh_token, timestamp) VALUES ("${refreshToken}", CURRENT_TIMESTAMP())`;
  var refreshTokenRequest = {
    query: sqlQuery_PutRefreshToken,
    useLegacySql: false
  }
  var queryResults = BigQuery.Jobs.query(refreshTokenRequest, projectID);
  Logger.log(`QUERY to set refresh token\n\nQUERY RESULT: ${queryResults.jobComplete}`);
}
function getRefreshToken_(projectID) {
  //var ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active spreadsheet
  // var sheet = refreshTokenSS_.getSheetByName('refresh_token'); // Get the "refresh_token" sheet
  // var refresh_token = sheet.getRange('A1').getValue(); // Get the value from cell A1
  var refreshTokenTableID = "iampm-stats.IAMPM_KOMMO_API.refresh_token";
  var refreshTokenTableID = "iampm-stats.IAMPM_KOMMO_API.refresh_token";
  var sqlQuery_GetRefreshToken = `SELECT * FROM ${refreshTokenTableID} ORDER BY timestamp DESC LIMIT 1`;
  var refreshTokenRequest = {
    query: sqlQuery_GetRefreshToken,
    useLegacySql: false
  }
  var queryResults = BigQuery.Jobs.query(refreshTokenRequest, projectID);
  Logger.log(`QUERY to get refresh token\n\nQUERY RESULT: ${queryResults.jobComplete}`);
  var refreshToken = queryResults.getRows()[0].f[0].getV();
  return refreshToken;
}
function getAccessToken() {
  //var ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active spreadsheet
  // var sheet = refreshTokenSS_.getSheetByName('access_token'); // Get the "access_token" sheet
  // var access_token = sheet.getRange('A1').getValue(); // Get the value from cell A1
  // return access_token;
  var projectID = "iampm-stats";
  
  var accessTokenTableID = "iampm-stats.IAMPM_KOMMO_API.access_token";
  var sqlQuery_GetAccessToken = `SELECT * FROM ${accessTokenTableID} ORDER BY timestamp DESC LIMIT 1`;
  var accessTokenRequest = {
    query: sqlQuery_GetAccessToken,
    useLegacySql: false
  }
  var queryResults = BigQuery.Jobs.query(accessTokenRequest, projectID);
  Logger.log(`QUERY to get access token\n\nQUERY RESULT: ${queryResults.jobComplete}`);
  var accessToken = queryResults.getRows()[0].f[0].getV();
  return accessToken;
}
function setAccessToken_(projectID, accessToken) {
  //var ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active spreadsheet
  // var sheet = refreshTokenSS_.getSheetByName('access_token'); // Get the "access_token" sheet
  // sheet.getRange('A1').setValue(access_token); // Set the value in cell A1
  var accessTokenTableID = "iampm-stats.IAMPM_KOMMO_API.access_token";
  var sqlQuery_PutAccessToken = `INSERT INTO ${accessTokenTableID} (access_token, timestamp) VALUES ("${accessToken}", CURRENT_TIMESTAMP())`;
  var accessTokenRequest = {
    query: sqlQuery_PutAccessToken,
    useLegacySql: false
  }
  var queryResults = BigQuery.Jobs.query(accessTokenRequest, projectID);
  Logger.log(`QUERY to set access token table\n\nQUERY RESULT: ${queryResults.jobComplete}`);
}
function test() {
  Logger.log(getAccessToken());
}

Additionally, by using the methods in the library, you have the option to select exactly the data you need, applying filters, sorting, or other selection criteria without rewriting the existing code. This allows you to efficiently adapt publicly available solutions to the specific needs of your project.

Why Google Apps Script? A Comparison with Make

The choice in favor of Apps Script was driven by many factors, among which the ease of integration with Google products, the ability to use JavaScript for task automation, and the lack of a need for hosting play significant roles. This tool also allowed us to effectively log processes and integrate more easily with various APIs.

During development, we encountered limitations, such as the maximum number of records that could be retrieved from a single request, and we found ways to work around them using iteration and recursion. We also learned that proper code organization and detailed comments greatly simplify understanding and further work with it. Here's a comparison table:

How to Start Working with Apps Script

1. Google Apps Script quickstart
2. Google Apps Script: A Beginner’s Guide
3. Google Apps Script — Where to Begin Learning?
4. SCRIPTS GOOGLE APPS SCRIPT IN DOCUMENTS, SHEETS, AND PRESENTATIONS
5. The Beginner’s Guide to Google Sheets and App Script-1

How Script Logging Works on App Script in Google Cloud

Using Google Apps Script significantly simplifies working with Google's internal APIs. Google offers a wide range of APIs for various tasks, and Apps Script makes it easy to integrate with them, regardless of the programming language, whether Python or another. To do this, you just need to add the necessary API to your project through the developer console, and you can start using it in your scripts immediately with specialized Apps Script methods.

Here are a few useful links that will help you understand this:

1. The Cloud Logging
2. Cloud Logging documentation
3. Logging query language

If you want to solve the problem of obtaining leads using Google Apps Script and integration with Kommo, the most effective approach is to combine studying the documentation with practical application. First, familiarize yourself with the official Google Apps Script documentation and the Kommo API documentation to understand the basic principles of working with these tools. Even if you have basic knowledge of JavaScript, this will be sufficient to start.

Don’t stop at theory. Review ready-made code examples and Google and Kommo labs to better understand how to apply the acquired knowledge in practice. Here are some useful resources to help you get started:

1. Apps Script samples overview – here you will find various script samples that can be adapted to your tasks.
2. Google Developers Codelabs – interactive practical labs that will guide you step by step through the process of creating various applications and scripts.
3. EXPAND CAPABILITIES WITH API – familiarize yourself with examples of using the API for integration with the Kommo system.

Remember that successful application of these tools requires not just reading the documentation but also practical application of the acquired knowledge. Experiment with the code, adapt ready-made examples to your tasks, and don’t be afraid to try new approaches.

#3 Challenges We Faced During Integration

During the development of our library on Google Apps Script, we encountered certain technical limitations and challenges that required creative solutions and a deeper understanding of the tools we were using.

API Limitations and Workarounds

One of the key issues was the limitation on the number of records that could be retrieved from Kommo— a maximum of 250 per request. We found a solution by using iteration: a method that allowed us to process data page by page, keeping track of the current page number and checking if it was the last one.

function getLeadsByCreateDate(pipelineId, dateFrom, dateTo) {
  let accessToken = getAccessToken();
  let page = 1;
  let collectedData = [];
  do{
    let responseData = makeGETRequestToKOMMOLeadsCreateDate_(accessToken, pipelineId, dateFrom, dateTo, page);
    if(responseData == null) break;
    page++;
    collectedData = [...collectedData, ...responseData._embedded.leads];
  } while(true);
  Logger.log(collectedData.length + " Leads")
  return collectedData;
}

Here’s how we addressed this issue!

Another important limitation was the 6-minute execution time limit for scripts in Google Apps Script. Unlike the issue with the number of records, we didn’t attempt to bypass this limitation since it was a fixed part of the platform.

Working with External Libraries and Syntax

Unlike some programming languages where you need to connect external libraries for tasks like cryptography or data processing, Google Apps Script already has many necessary tools built in. This simplified our work but required additional time to learn the relevant methods and documentation.

The Utilities Service is one of the libraries you'll need to work with.

#4 Project Structure and File Naming

Each file in the project has a name corresponding to the logic or functionality it performs, for example, getLeadByIdFromKommo. This helps organize the code more logically and understandably.

Naming Rules and Tips

Well-thought-out file and method names contribute to a better understanding of the processes within the project and make it easier to work with the code. Using clear and understandable naming allows other developers to quickly navigate the project's structure.

Naming rules and tips for files:

1. Notations in programming: naming rules
2. File naming in JavaScript
3. BEM
4. The Art of Naming: Best Practices for File Naming
5. Understanding The JavaScript Filename Naming Convention: A Comprehensive Guide

Clear and understandable naming of files and methods is key to effective work with code. For example, when you open a file with a name that corresponds to a specific function, you immediately understand that it contains methods for working with leads in the Kommo system by ID. Such organization makes it easy to find the needed functionality and understand the code, especially when comments are used for additional explanations.

#5. 5 Tips for Working with Large Codebases

1. Modularity of Code: Organize the code into modules or packages responsible for specific system functions. This will not only simplify the understanding of the project architecture for new developers but also make it easier to make changes and updates.
2. Version Control: Using systems like Git allows you to effectively manage code versions, track and recover changes, and simplify team collaboration. Create clear commit messages for easier change tracking.
3. Documentation: Thorough documentation and comments in the code are necessary for quick orientation within the project. It’s important to update the documentation in line with code changes to avoid confusion.
4. Refactoring: Constantly reviewing and optimizing the code improves its quality and efficiency. It’s essential to focus not only on new functionality but also on improving existing code.
5. Testing: Developing automated tests ensures code reliability and timely detection of errors. Use different types of testing, including unit, integration, and system testing, for a comprehensive project check.

#6 The Process of Testing and Debugging Code

One of the most time-consuming and challenging stages in developing our library on Google Apps Script was testing. We often encountered situations where a single misplaced character in a request, such as a question mark instead of the required symbol, could cause the entire program to crash. Such minor errors posed significant difficulties, as the system didn’t always provide a clear explanation for the failure. This forced us to spend a lot of time on detailed code analysis and debugging to identify and fix the error.

During testing, we used several methods. One of them was working with the integration platform Make (formerly known as Integromat), which allowed us to visualize processes and more easily identify points of failure. The Make extension, Integromat DevTools, in particular, became an indispensable tool that helped us visually track all processes and errors.

Additionally, the console within Google Apps Script played a significant role in the debugging process. It allowed us to directly view errors and output data for analysis within the project. This was especially useful for gaining a detailed understanding of where and why issues were occurring. Moreover, the global project console provided the ability to analyze script behavior in more detail and more effectively identify errors.

Although testing was challenging and time-consuming, it became a crucial stage that allowed us to ensure the high quality and reliability of our library. Using a combined approach with debugging tools and active code analysis helped us effectively resolve identified issues.

Using the Library in an EdTech Company

One important aspect of our work was the ability to use the created library to simplify the work of other teams. For example, the method for adding notes to leads significantly eased routine tasks and reduced the number of errors.

Currently, our library is actively used within the team for various tasks related to processing data from Kommo. It has proven to be useful not only for automating the processes of lead collection and analysis but also for creating convenient tools for analytics and sales management.

Thanks to this experience, we not only solved a specific problem but also enriched our knowledge and skills in programming and process automation. Learning Google Apps Script opened up new opportunities for us to develop and optimize work processes.

Conclusion

Developing the library on Google Apps Script was an interesting challenge that allowed us to dive deeper into the capabilities of this platform. Despite some technical limitations and difficulties, we were able to adapt our approach and find effective solutions for automating data processing tasks.