如何用Google Apps Script实现setValues()与产品代码行匹配

问题描述 #

现有Google Apps Script脚本逻辑如下：

• 读取BLOCK ORDERS工作表P列的产品代码
• 若Quotation工作表存在匹配客户名称的子表，则从该子表提取对应价格；否则从PRODUCTS工作表提取价格
• 最终将价格写入BLOCK ORDERS的Z列

当前脚本的setValues()输出从第2行开始，无法将价格精准写入产品代码所在的对应行（产品代码可位于任意行）。

原脚本问题分析 #

原脚本中output数组仅收集了有有效产品代码的行的价格，数组结构紧凑，未保留原数据行的位置信息。写入时直接从第2行开始填充，导致价格与原产品行错位。

修改后的脚本 #

function updateQuotationPrice() { 
  var ss1 = SpreadsheetApp.openById("1VlLbwBtYyOQBRz-8JHRn_jVhaAC7Tl68OXSltoQwaBU"); // BLOCK ORDERS
  var ss2 = SpreadsheetApp.openById("10tb0zE_8i849T-hL6mU-Pw_4V-aMzNZJGNOFYG1F3qk"); // Quotation
  var ss3 = SpreadsheetApp.openById("1pt7YnN9fmoD4PE0o9oVPezK8Qz6ZmabyJbWXfdzFeMU"); // PRODUCTS

  // 获取BLOCK ORDERS数据（从第2行开始，F列到BH列）
  var blockSheet = ss1.getSheetByName("BLOCK ORDERS");
  var lr1 = blockSheet.getLastRow();
  var data1 = blockSheet.getRange(2, 6, lr1 - 1, 61).getValues(); // 修正行数范围：从第2行开始，总行为lr1-1

  // 预加载Quotation所有工作表数据并构建产品价格映射，提升运行效率
  var quotationPriceMap = {};
  ss2.getSheets().forEach(sheet => {
    var customerName = sheet.getName();
    var sheetData = sheet.getDataRange().getValues();
    quotationPriceMap[customerName] = {};
    sheetData.forEach(row => {
      var productCode = row[10]; // 产品代码对应K列（索引10）
      if (productCode) {
        quotationPriceMap[customerName][productCode] = {
          sterling: row[2], // 英镑价格对应C列（索引2）
          euro: row[3]      // 欧元价格对应D列（索引3）
        };
      }
    });
  });

  // 预加载PRODUCTS工作表的产品价格映射
  var productsSheet = ss3.getSheetByName("PRODUCTS");
  var productsData = productsSheet.getDataRange().getValues();
  var productPriceMap = {};
  productsData.forEach(row => {
    var productCode = row[10]; // 产品代码对应K列（索引10）
    if (productCode) {
      productPriceMap[productCode] = {
        sterling: row[2], // 英镑价格对应C列（索引2）
        euro: row[3]      // 欧元价格对应D列（索引3）
      };
    }
  });

  // 初始化output数组，长度与data1一致，每一行默认填充空值，确保位置对应
  var output = new Array(data1.length).fill([null]);

  // 逐行处理数据
  data1.forEach((row, index) => {
    var customerName = row[0];     // 客户名称对应F列（索引0）
    var currency = row[9];         // 币种对应O列（索引9）
    var productCode = row[10];     // 产品代码对应P列（索引10）
    var zoneSurcharge = row[58] || 0; // 区域附加费对应BH列（索引58），默认0避免NaN

    if (!productCode) return; // 无产品代码则跳过当前行

    var finalPrice = null;

    // 优先从Quotation对应客户表取价格
    if (quotationPriceMap[customerName] && quotationPriceMap[customerName][productCode]) {
      var priceObj = quotationPriceMap[customerName][productCode];
      finalPrice = (currency === "STERLING") ? priceObj.sterling : priceObj.euro;
    } 
    // 否则从PRODUCTS取价格
    else if (productPriceMap[productCode]) {
      var priceObj = productPriceMap[productCode];
      finalPrice = (currency === "STERLING") ? priceObj.sterling : priceObj.euro;
    }

    // 计算最终价格（叠加附加费）并赋值到对应位置
    if (finalPrice !== null) {
      output[index] = [Number(finalPrice) + Number(zoneSurcharge)];
    }
  });

  // 将结果写入Z列（第26列），从第2行开始，完全匹配原数据行位置
  blockSheet.getRange(2, 26, output.length, 1).setValues(output); 
}

关键修改点 #

1. 保留行位置的output数组：用new Array(data1.length).fill([null])创建与原数据行数一致的数组，确保每一行都有对应位置，彻底解决错位问题。
2. 预加载价格映射：提前将Quotation和PRODUCTS的产品价格存入对象映射，避免循环中重复读取工作表数据，大幅提升脚本运行效率。
3. 逐行精准赋值：处理每一行时，直接将价格赋值到output[index]，严格对应原数据的行位置。
4. 修正数据范围：原getRange(2,6,lr1,61)会多取一行，修正为lr1-1确保行数匹配。

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