Here is the scenerio:

i have 2 google sheets that have related data

Orders

Details

I need to output to a JSON file that looks like this - I will need to transmit this file to to an API via curl or similar method. Help... I am a total newb to this and am looking for guidance...

{
 "Orders" : [
{
  "CustName" : "Cust 1",
  "OrderNum" : "1000",
 
  "Items" : [
    {
      "ItemNum" : "Item1",
      "SerNum" : "12345",
      "task_id" : 0
    },
    {
      "ItemNum" : "Item2",
      "SerNum" : "23456",
      "task_id" : 1
    },
    {
      "ItemNum" : "Item3",
      "SerNum" : "34567",
      "task_id" : 2
    ],
     },
   {
  "CustName" : "Cust 2",
  "OrderNum" : "1001",
 
  "Items" : [
    {
      "ItemNum" : "Item1",
      "SerNum" : "45678",
      "task_id" : 0
    },
    {
    {
      "ItemNum" : "Item2",
      "SerNum" : "56789",
      "task_id" : 1
    ],
     }

Thanks for help...

Since I am new to this I did not even know where to start... Data is in google sheets...

CodePudding user response：

In your situation, how about using a Google Apps Script as follows?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet, and please set the sheet names. And, run the function. By this, you can see the result values in the log.

function myFunction() {
  const sheetName1 = "Orders";
  const sheetName2 = "Details";

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName(sheetName1);
  const sheet2 = ss.getSheetByName(sheetName2);
  const values1 = sheet1.getRange("A2:B"   sheet1.getLastRow()).getValues();
  const values2 = sheet2.getRange("A2:C"   sheet2.getLastRow()).getValues();
  const obj2 = values2.reduce((o, [a, b, c]) => {
    const temp = { "ItemNum": b, "SerNum": c, "task_id": o[a] ? o[a].length : 0 };
    return Object.assign(o, { [a]: (o[a] ? [...o[a], temp] : [temp]) });
  }, {});
  const res = {
    "Orders": values1.reduce((ar, [a, b]) => {
      if (obj2[a]) {
        ar.push({ "CustName": b, "OrderNum": a, "Items": obj2[a] });
      }
      return ar;
    }, [])
  };
  console.log(res);
}

• When this script is run using your provided sample tables, the value of res is as follows.

  {
     "Orders":[
        {
           "CustName":"Cust 1",
           "OrderNum":1000,
           "Items":[
              {
                 "ItemNum":"Item 1",
                 "SerNum":12345,
                 "task_id":0
              },
              {
                 "ItemNum":"Item 2",
                 "SerNum":23456,
                 "task_id":1
              },
              {
                 "ItemNum":"Item 3",
                 "SerNum":34567,
                 "task_id":2
              }
           ]
        },
        {
           "CustName":"Cust 2",
           "OrderNum":1001,
           "Items":[
              {
                 "ItemNum":"Item 1",
                 "SerNum":45678,
                 "task_id":0
              },
              {
                 "ItemNum":"Item 2",
                 "SerNum":56789,
                 "task_id":1
              }
           ]
        }
     ]
  }
  

Note:

• If you want to use this script as a custom function, please use the following script. In this case, please put a custom function like =SAMPLE(Orders!A2:B, Details!A2:C) to a cell.

  function SAMPLE(values1, values2) {
    const obj2 = values2.reduce((o, [a, b, c]) => {
      if (a && b && c) {
        const temp = { "ItemNum": b, "SerNum": c, "task_id": o[a] ? o[a].length : 0 };
        return Object.assign(o, { [a]: (o[a] ? [...o[a], temp] : [temp]) });
      }
      return o;
    }, {});
    const res = {
      "Orders": values1.reduce((ar, [a, b]) => {
        if (obj2[a]) {
          ar.push({ "CustName": b, "OrderNum": a, "Items": obj2[a] });
        }
        return ar;
      }, [])
    };
    return JSON.stringify(res);
  }
  

References:

• reduce()
• map()