我有一個 Order_List 表，有 Order_ID & Stall_ID & Order_Menu 列（用 JSON 物件填充），如果 1 個訂單有多個菜，我如何對 JSON 物件進行排序，以便根據 Order_ID 和 Stall ID 進行排序？
Sheet 1 中的表格是這樣的：
Order_ID | Stall_ID | Order_Menu
001 147 {"396":{"dish_name":"Chicken Wings 3pcs","dish_price":20,"dish_quantity":1,"dish_size_name":"default","dish_size_price":0,"dish_addon_name":"default","dish_addon_price":0,"dish_variation_name":"default","dish_variation_price":0}}
002 156 {"286":{"dish_name":"BBQ PORK RIBS (6PCS)","dish_price":45,"dish_quantity":1,"dish_size_name":"default","dish_size_price":0,"dish_addon_name":"default","dish_addon_price":0,"dish_variation_name":"default","dish_variation_price":0},
"288":{"dish_name":"BBQ CHICKEN WING (3PCS)","dish_price":15,"dish_quantity":1,"dish_size_name":"default","dish_size_price":0,"dish_addon_name":"default","dish_addon_price":0,"dish_variation_name":"default","dish_variation_price":0},
"598":{"dish_name":"BUTTERMILK MEATBALL","dish_price":16,"dish_quantity":1,"dish_size_name":"default","dish_size_price":0,"dish_addon_name":"Tiger Crystal","dish_addon_price":6,"dish_variation_name":"Tiger Crystal","dish_variation_price":0}
}
我從這個問題中得到的當前腳本是這樣的：
// 1. Retrieve source and destination sheets.
const ss = SpreadsheetApp.getActiveSpreadsheet();
//maps srcSheet to Order_List, dstSheet to Order_MenuList
const [srcSheet, dstSheet] = ["Order_List", "Order_Menu_List"].map(s => ss.getSheetByName(s));
// 2. Retrieve values from source sheet.
const values = srcSheet.getRange("E2:E" srcSheet.getLastRow()).getValues();
// 3. Create an array for putting to the destination sheet using the header value.
const headers = ["dish_name","dish_price","dish_quantity","dish_size_name","dish_size_price","dish_addon_name","dish_addon_price","dish_variation_name","dish_variation_price"];
const ar = [["", ...headers], ...values.flatMap(([a]) => Object.entries(JSON.parse(a)).map(([k, v]) => [k, ...headers.map(h => v[h] || "")]))];
// 4. Put the array to the destination sheet.
dstSheet.getRange(1, 1, ar.length, ar[0].length).setValues(ar);
這在獲取每道菜的關鍵對值方面做得很好；因此，根據上面的表格示例，訂單 ID 002 中的菜肴將位于不同的行中，這很棒。但 id 喜歡根據訂單 id 對其進行排序；所以也許 Sheet 2 中的結果表看起來像這樣：
Order_ID | Stall_ID | dish_name | dish_price ...
001 147 Chicken Wings 3pcs
002 156 BBQ Pork Ribs
BBQ Chicken Wings
Buttermilk Meatball
uj5u.com熱心網友回復：
當Order_ID | Stall_ID | Order_Menu表“Order_List”的“A”、“B”和“C”列的列時，下面的修改腳本怎么樣？
修改后的腳本：
// 1. Retrieve source and destination sheets.
const ss = SpreadsheetApp.getActiveSpreadsheet();
//maps srcSheet to Order_List, dstSheet to Order_MenuList
const [srcSheet, dstSheet] = ["Order_List", "Order_Menu_List"].map(s => ss.getSheetByName(s));
// 2. Retrieve values from source sheet.
const [head, ...values] = srcSheet.getRange("A1:C" srcSheet.getLastRow()).getDisplayValues();
// 3. Create an array for putting to the destination sheet using the header value.
const headers = ["dish_name", "dish_price", "dish_quantity", "dish_size_name", "dish_size_price", "dish_addon_name", "dish_addon_price", "dish_variation_name", "dish_variation_price"];
const ar = [[head[0], head[1], ...headers], ...values.flatMap(([a, b, c]) => Object.entries(JSON.parse(c)).map(([, v], i) => [...(i == 0 ? [a, b] : ["", ""]), ...headers.map(h => v[h] || "")]))];
// 4. Put the array to the destination sheet.
const range = dstSheet.getRange(1, 1, ar.length, ar[0].length);
range.offset(0, 0, ar.length, 1).setNumberFormat("@");
range.setValues(ar);
- 當這個腳本運行時，
the table in Sheet 1 is like:源表“Order_List”的值被轉換，轉換后的值被放入目標表“Order_Menu_List”。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/372948.html