Analyze Companies Growth with Explorium’s Google Sheet Formula

/**
 * Find company departments' growth indicators by company's URL using the Explorium API
 *
 * @param {string} url Company website URL (can be in any format)
 * @return if found - Quarter, Found Profiles, Engineering, QoQ Engineering, Design, QoQ Design, Marketing, QoQ Marketing, Sales, QoQ Sales, Customer Service, QoQ Customer Service, HR, QoQ HR, Legal,	QoQ Legal, Finance, QoQ Finance
 * @customfunction
 */
function ENRICH_GROWTH(url) {
    // Define here the fields you want to take from Explorium
    // You can view all available Contacts for professional Individuals fields at https://developers.explorium.ai/reference/individual-contact-data-contacts-for-professional-individuals
    const EXPLORIUM_FIELDS_g = [
        "Quarter", "Number of profiles", "Percentage of engineering roles", "QoQ engineering roles", "Percentage of design roles", "QoQ design roles", "Percentage of marketing roles", "QoQ marketing roles", "Percentage of sales roles", "QoQ sales roles", "Percentage of customer service roles", "QoQ customer service roles", "Percentage of human resources roles", "QoQ human resources roles", "Percentage of legal roles", "QoQ legal roles", "Percentage of finance roles", "QoQ finance roles"
    ];

    // Send HTTP request to Firmographics Explorium API - https://developers.explorium.ai/reference/individual-contact-data-contacts-for-professional-individuals
    const response = UrlFetchApp.fetch("https://app.explorium.ai/api/bundle/v1/enrich/workforce-trends-by-company-department", {
        method: "post",
        payload: JSON.stringify([{
            "url": url
        }]),
        headers: {
            "Content-Type": "application/json",
            "x-explorium-ref": "gsheets_formula_enrich_growth",
            "API_KEY": readExploriumAPIKey()
        },
        muteHttpExceptions: true
    });

    const responseCode = response.getResponseCode();
    const responseJson = JSON.parse(response);

    if (responseCode !== 200)
        return responseJson.message;

    const signals = responseJson[0];
    
    const relevantFields = EXPLORIUM_FIELDS_g.map(field => {
      if (!signals["Quarter"])
        return null;

      if (field == "Quarter")
          return signals["Quarter"].substring(0, 10);

      if (field.startsWith("Percentage of ")) {
          if (!signals[field]) {
              if (signals["Number of profiles"])
                  return 0;
                  
              return null;
          }

          const percentage = Math.round(signals[field]);
          return Math.round((percentage/100) * signals["Number of profiles"]);
      }

      if (field.startsWith("QoQ "))
          return `${Math.round(signals[field])}%`;
          
      return signals[field];
    });

    return [relevantFields];
}

// Reads Explorium API Key from Settings sheet, B1 cell
function readExploriumAPIKey() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var worksheet = spreadsheet.getSheetByName("Settings");
  var cell = worksheet.getRange("B1");
  return cell.getValue();
}