动态API调用优化问询：Fetch函数下产品属性空值处理

DevOps 小满

2026-8-18

优化Alkemics API产品数据获取：处理属性缺失与动态调用

问题背景

我通过Fetch函数调用Alkemics平台API，以GTIN为检索键获取特定品类产品的多属性数据。但部分产品存在属性缺失情况：要么字段存在但值为null，要么平台未填写对应字段。目前脚本里的空值处理逻辑无法完全生效，希望优化实现：

动态API调用，仅获取存在的属性
空值或缺失字段不阻断API调用流程

优化方案与代码实现

1. 新增安全属性访问工具函数

编写通用函数处理嵌套属性的安全访问，替代零散的try/catch和null判断，避免因字段缺失导致的流程中断：

// 安全访问嵌套属性，避免字段缺失或null导致报错
function getSafeValue(obj, path, defaultValue = ' ') {
  return path.split('.').reduce((current, key) => {
    // 处理数组索引，比如namePublicLong[0]
    const arrayMatch = key.match(/(\w+)\[(\d+)\]/);
    if (arrayMatch) {
      const arrKey = arrayMatch[1];
      const index = parseInt(arrayMatch[2], 10);
      return current && current[arrKey] && current[arrKey][index] !== undefined 
        ? current[arrKey][index] 
        : defaultValue;
    }
    return current && current[key] !== undefined ? current[key] : defaultValue;
  }, obj);
}

2. 重构字段映射逻辑

将列名与API字段的映射关系抽离成配置对象，便于维护，同时自动适配属性存在性：

// 列名与API字段的映射配置
const fieldMappings = [
  { column: "GTIN", path: "gtin" },
  { column: "Libelle long", path: "namePublicLong[0].data", defaultValue: "" },
  { column: "Fournisseur", path: "isSharedFrom[0].sourceOrganization.nameLegal" },
  { column: "Catégorie du produit", path: "kind.label" },
  { column: "Code Tiers", path: "supplierId" },
  { column: "Cycle de vie", path: "lifeCycle" },
  { column: "Maturité commande Galec", path: "business_processes.label:Maturité commande Galec", type: "maturity" },
  { column: "Maturité direct magasin", path: "business_processes.label:Maturité direct magasin", type: "maturity" },
  { column: "Maturité Commande SCABEL", path: "business_processes.label:Maturité Commande SCABEL", type: "maturity" },
  { column: "Maturité complémentaire Galec", path: "business_processes.label:Maturité complémentaire Galec", type: "maturity" },
  { column: "Maturité complémentaire Direct Magasin", path: "business_processes.label:Maturité complémentaire Direct Magasin", type: "maturity" },
  { column: "Maturité complémentaire Scabel", path: "business_processes.label:Maturité complémentaire Scabel", type: "maturity" },
  { column: "Maturité Animation Digitale", path: "business_processes.label:Maturité Animation Digitale", type: "maturity" },
  { column: "Maturité Digitale", path: "business_processes.label:Maturité Digitale", type: "maturity" },
  { column: "Maturité Entité Leclerc", path: "business_processes.label:Maturité Entité Leclerc", type: "maturity" },
  { column: "Type d'unité", path: "typePackaging.label" },
  { column: "Marque Commerciale", path: "brand.description" },
  { column: "Appellation viticole ou région d'origine", path: "alcoholicBeverageSubregionText" },
  { column: "Complément d'appellation(N)", path: "alcoholicBeverageAdditionalAppellationDetailsText" },
  { column: "Packaging vin", path: "leclercWinePackagingTypeCode.label" },
  { column: "Contenu net", path: "netWeight[0].data" },
  { column: "Description des saveurs du vin", path: "wineTastingDescriptionList[0].wineTastingCode.label" },
  { column: "Type de champagne(N)", path: "wineCategoryCode.label" },
  { column: "Viticulture(N)", path: "vintnerTextList" },
  { column: "Couleur du Vin", path: "colorGroup.label" },
  { column: "Millésime", path: "vintage" },
  { column: "Terroirs et sol(N)", path: "vineyardRegion.label" },
  { column: "Région Viticole", path: "leclercFrenchVineyardRegionCode.label" },
  { column: "Durée du potentiel de maturation", path: "wineAgeingPotentialDurationCode.label" },
  { column: "Echelle de fruit", path: "wineFruitExpressionNoteNumber" },
  { column: "Echelle de personnalité", path: "wineAromasExpressionNoteNumber" },
  { column: "Dégustation robe", path: "wineTastingRobeDescriptionText[0].data" },
  { column: "Dégustation nez", path: "wineTastingNoseDescriptionText[0].data" },
  { column: "Type de vendange(N)", path: "grapeHarvestingDescriptionText.data" },
  { column: "Dégustation bouche", path: "wineTastingMouthDescriptionText[0].data" },
  { column: "Type d'appellation du vin", path: "wineCategoryCode.label" },
  { column: "Allergènes avérés", path: "allergens[0].data" },
  { column: "Type d'allergène", path: "allergenTypeList" },
  { column: "Degré d'alcool", path: "ratioAlcohol" },
  { column: "Teneur en sucre", path: "sugarContent.label" },
  { column: "Description du cépage", path: "varietalDesignationDescriptionText[0].data" },
  { column: "Accords mets et vins", path: "pairingsTypes[0].pairingsType.label" },
  { column: "Occasions particulières", path: "occasions[0].occasion.label" },
  { column: "Températures de dégustation (en °c)", path: "tempConsumption[0].data" },
  { column: "Potentiel de garde", path: "wineAgeingPotentialDurationCode.label" },
  { column: "Code de nomenclature douanière", path: "importEuropeanClassification" },
  { column: "Code produit accise france", path: "cpaFranceCode.label" },
  { column: "Libellé fiscal", path: "taxLabelList[0].taxLabelCode.label" },
  { column: "Pays d'origine", path: "countryOfOriginList[0].countryOfOriginCode.label" },
  { column: "Type de paiement des droits d'alcools", path: "alcoholicBeveragePaymentTypeCode.label" },
  { column: "Image Face du produit", path: "assets.pictures[0].exportables[0].uniformResourceIdentifier", defaultValue: "" },
  { column: "Image Arrière du produit", path: "assets.pictures[2].exportables[0].uniformResourceIdentifier", defaultValue: "" }
];

3. 优化主函数逻辑

整合工具函数与映射配置，实现动态属性获取与容错处理：

function GetAllProduct() {
  var token = ConnectAlkemics();

  var headers = {
    "accept": "*/*",
    "accept-language": "fr",
    'Authorization': 'Bearer ' + token,
    'cache-control': 'no-cache',
    'content-type': 'application/json;charset=UTF-8',
    'expires': '-1',
    'pragma': 'no-cache',
    'sec-fetch-dest': 'empty',
    'sec-fetch-mode': 'cors',
    'sec-fetch-site': 'same-site',
    'x-alk-priority': '9'
  };

  var options = {
    'method': "GET",
    "headers": headers
  };

  var source = SpreadsheetApp.getActive().getSheetByName("GTIN");
  var ss = SpreadsheetApp.getActive().getSheetByName("RESULTATS");

  ss.clear();

  // 获取属性列表
  var lastRow = source.getLastRow();
  var listeAttribut = "";
  var tabChamp = [];
  var index = 0;
  for (var i = 2; i <= lastRow; i++) {
    if (source.getRange(i, 3).getValue() != "") {
      tabChamp[index] = source.getRange(i, 2).getValue();
      index += 1;
      listeAttribut = listeAttribut ? `${listeAttribut},${source.getRange(i, 3).getValue()}` : source.getRange(i, 3).getValue();
    }
  }

  // 写入表头
  const columnNames = fieldMappings.map(mapping => mapping.column);
  for (let i = 0; i < columnNames.length; i++) {
    ss.getRange(1, i + 1).setValue(columnNames[i]);
  }

  var total = 0;
  var lignegtin = 1;
  var moregtin = true;

  while (moregtin) {
    var fini = false;
    var nbgtin = 0;
    var lesgtin = "";
    var produits = [];

    // 批量获取50个GTIN
    while ((source.getRange(lignegtin, 1).getValue() != "") && (nbgtin < 50)) {
      lesgtin = lesgtin ? `${lesgtin},${source.getRange(lignegtin, 1).getValue().toString().replace(/\s+/g, " ").trim()}` : source.getRange(lignegtin, 1).getValue().toString().replace(/\s+/g, " ").trim();
      lignegtin++;
      nbgtin++;
    }

    if (!lesgtin) {
      moregtin = false;
      fini = true;
    } else {
      var limit = 200;
      var next_page = '';

      var params = `&filter_product_languages=fr&filter_gtins_in=${lesgtin}&filter_source_include=${listeAttribut}`;
      
      while (!fini) {
        var lesparams = `${params}&limit=${limit}&next_page=${next_page}&ts=${Math.floor((Math.random() * 10000000) + 1)}`;
        var url = `https://apis.alkemics.com/public/v1/products?${lesparams}`;
        
        try {
          var content = UrlFetchApp.fetch(url, options);
          if (content.getResponseCode() == 200) {
            var retour = JSON.parse(content.getContentText());
            next_page = retour.next_page;
            var data = retour.data;
            
            // 处理每个产品数据
            data.forEach(produit => {
              // 预处理成熟度数据
              const maturiteMap = {};
              if (produit.business_processes && Array.isArray(produit.business_processes)) {
                produit.business_processes.forEach(item => {
                  maturiteMap[item.label] = item.requested ? (item.status ? "OK" : "KO") : "";
                });
              }

              // 构建产品行数据
              const ligne = fieldMappings.map(mapping => {
                if (mapping.type === "maturity") {
                  // 处理成熟度类型字段
                  const label = mapping.path.split(':')[1];
                  return maturiteMap[label] || ' ';
                }
                // 安全获取属性值
                return getSafeValue(produit, mapping.path.split(':')[0], mapping.defaultValue || ' ');
              });

              produits.push(ligne);
              total++;
            });

            // 没有下一页则结束循环
            if (!next_page) {
              fini = true;
            }
          } else {
            Logger.log(`API请求失败，响应码：${content.getResponseCode()}`);
            fini = true;
          }
        } catch (e) {
          Logger.log(`API调用报错：${e.message}`);
          fini = true;
        }
      }
    }

    // 将批量数据写入表格
    if (produits.length > 0) {
      ss.getRange(ss.getLastRow() + 1, 1, produits.length, produits[0].length).setValues(produits);
    }
  }
}

// 安全访问嵌套属性，避免字段缺失或null导致报错
function getSafeValue(obj, path, defaultValue = ' ') {
  return path.split('.').reduce((current, key) => {
    // 处理数组索引，比如namePublicLong[0]
    const arrayMatch = key.match(/(\w+)\[(\d+)\]/);
    if (arrayMatch) {
      const arrKey = arrayMatch[1];
      const index = parseInt(arrayMatch[2], 10);
      return current && current[arrKey] && current[arrKey][index] !== undefined 
        ? current[arrKey][index] 
        : defaultValue;
    }
    return current && current[key] !== undefined ? current[key] : defaultValue;
  }, obj);
}

优化说明

安全属性访问：通过getSafeValue函数统一处理所有嵌套属性的访问，避免因字段缺失或null导致的脚本中断。
动态映射配置：字段映射集中管理，新增或修改属性只需调整配置对象，无需修改业务逻辑。
批次容错处理：单个API请求或产品处理报错时，仅记录日志并跳过当前批次，不终止整个流程。
批量写入优化：将产品数据批量收集后再写入表格，减少Spreadsheet API调用次数，提升性能。

内容的提问来源于stack exchange，提问作者user19870352

动态API调用优化问询：Fetch函数下产品属性空值处理

问题背景 #

优化方案与代码实现 #

1. 新增安全属性访问工具函数 #

2. 重构字段映射逻辑 #

3. 优化主函数逻辑 #