Google Sheet Apps Script : How to sort JSON objects based on Order ID-CodePudding

I have an Order_List table has column Order_ID & Stall_ID & Order_Menu(filled with JSON objects), how do i sort the JSON objects so it is ordered based on Order_ID & Stall ID if for 1 order there's multiple dishes?

the table in Sheet 1 is like:

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}
                       }

The current script that i have from this question is like:

// 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);

which does a great job at getting the key pair values for each dish; so based on table example above, dishes from order id 002 will be on different rows which is great. but id like to sort it based on order id; so maybe the result table in Sheet 2 would look like this:

Order_ID | Stall_ID | dish_name        | dish_price ...
   001       147    Chicken Wings 3pcs
   002       156    BBQ Pork Ribs
                    BBQ Chicken Wings
                    Buttermilk Meatball

CodePudding user response：

When the columns of Order_ID | Stall_ID | Order_Menu are the columns "A", "B" and "C" of the sheet "Order_List", how about the following modified script?

Modified script:

// 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);

When this script is run, the values of the table in Sheet 1 is like: of the source sheet "Order_List" are converted and the converted values are put to the destination sheet "Order_Menu_List".