Google Sheet Apps Script 如何按Order ID与Stall ID对JSON对象排序

修改后可直接使用的完整脚本 #

// 1. 获取源表和目标表
const ss = SpreadsheetApp.getActiveSpreadsheet();
const [srcSheet, dstSheet] = ["Order_List", "Order_Menu_List"].map(s => ss.getSheetByName(s));

// 2. 读取源表所有行数据，默认A列为Order_ID、B列为Stall_ID、E列为Order_Menu，可根据实际列位置调整
const sourceRows = srcSheet.getRange("A2:E" + srcSheet.getLastRow()).getValues();
const headers = ["Order_ID", "Stall_ID", "dish_name","dish_price","dish_quantity","dish_size_name","dish_size_price","dish_addon_name","dish_addon_price","dish_variation_name","dish_variation_price"];

// 3. 解析JSON生成展开后的菜品条目
const dishEntries = [];
sourceRows.forEach(row => {
  const orderId = row[0];
  const stallId = row[1];
  const orderMenuJson = row[4];
  if (!orderMenuJson) return;
  const dishObj = JSON.parse(orderMenuJson);
  const dishList = Object.values(dishObj);
  // 遍历当前订单的所有菜品
  dishList.forEach((dish, index) => {
    const rowData = [
      // 仅同订单第一个菜品展示订单ID和档位ID，后续行对应位置留空
      index === 0 ? orderId : "",
      index === 0 ? stallId : "",
      dish.dish_name || "",
      dish.dish_price || "",
      dish.dish_quantity || "",
      dish.dish_size_name || "",
      dish.dish_size_price || "",
      dish.dish_addon_name || "",
      dish.dish_addon_price || "",
      dish.dish_variation_name || "",
      dish.dish_variation_price || ""
    ];
    dishEntries.push(rowData);
  });
});

// 4. 按Order_ID升序、Stall_ID升序排序
dishEntries.sort((a, b) => {
  const getOrderId = (row) => row[0] || dishEntries.find(item => item[0] && item[2] === row[2])?.[0] || "";
  const getStallId = (row) => row[1] || dishEntries.find(item => item[1] && item[2] === row[2])?.[1] || "";
  const orderIdCompare = getOrderId(a).localeCompare(getOrderId(b));
  if (orderIdCompare !== 0) return orderIdCompare;
  return getStallId(a).localeCompare(getStallId(b));
});

// 5. 写入目标表
const outputData = [headers, ...dishEntries];
dstSheet.clearContents();
dstSheet.getRange(1, 1, outputData.length, outputData[0].length).setValues(outputData);

适配说明 #

• 列位置调整：如果你的Order_ID、Stall_ID、Order_Menu不在默认的A、B、E列，修改getRange("A2:E" ...)中的列范围，同时对应调整row[0]、row[1]、row[4]的索引即可（列索引从0开始计数，A列对应0，B列对应1，以此类推）
• 排序规则调整：默认按Order_ID、Stall_ID升序排列，如需改为降序，把sort方法返回值的两个参数交换位置即可

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