I have a google spreadsheet with Sheet A having JSONs in column A and I'd like to populate Sheet B with the data with respective rows and columns. However I'm not entirely sure how to start. I'm a bit confused about the whole process. First declaring sheet. I've seen people using the following:

function parseJSON()
{
    var ss = SpreadsheetApp.getActiveSpreadsheet();  // Gets the Active Spreadsheet
    var sheet = ss.getSheets()[0]; // Gets the first sheet
    var parsed = JSON.parse(json);

    sheet.appendRow([json.items[i].id, json.items[i].name, json.items[i].url]);
}

but I have in 1 active spreadsheet from sheet A to sheet B. So would i need to declare a second sheet (my sheet B)?

var ss = SpreadsheetApp.getActiveSpreadsheet();  // Gets the Active Spreadsheet
var sheet1 = ss.getSheets()[0]; // Gets the first sheet
var sheet2 = ss.getSheets()[1]; // Gets the first sheet

sheet2.appendRow([json.items[i].id, json.items[i].name, json.items[i].url]);

or i dont need those declarations and i just

function parseJSON(parsed)
{

    return [parsed.items[i].id, parsed.items[i].name, parsed.items[i].url]
}

so in Sheet B A1 i just put =parseJSON(SheetB!A) or something equivalent which im still unsure.

Also how do i declare what are headers and the values according to the JSON? Should the headers be declared?

const headers = ["dish_name","dish_price","dish_quantity"];

or is it possible to get the header from the JSON? How about the corresponding values? Does Apps Ssripts accept foreach loop?

For additional context (for selected answer that helped)

JSON Obj - {"55":{"dish_name":"(Ala Carte) Claypot Soup with Rice and Char Kuih","dish_price":17,"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}}

the selected answer was able to split it up in key value pairs. so constant keys were headers, rows were the corresponding values.

CodePudding user response：

In your situation, how about the following modification?

Modification points:

• About your following script,

    function parseJSON()
    {
        var ss = SpreadsheetApp.getActiveSpreadsheet();  // Gets the Active Spreadsheet
        var sheet = ss.getSheets()[0]; // Gets the first sheet
        var parsed = JSON.parse(json);
  
        sheet.appendRow([json.items[i].id, json.items[i].name, json.items[i].url]);
    }
  

  • In this case, sheet, json are not declared. By this, an error occurs.
  • When sheet, json are declared, parsed is not used.

Answer for question 1:

but I have in 1 active spreadsheet from sheet A to sheet B. So would i need to declare a second sheet (my sheet B)? I think that it's yes. I think that when you want to retrieve the values from the column "A" of Sheet "A" and the values are put to Sheet "B", it is required to declare both sheets.

Answer for question 2:

Also how do i declare what are headers and the values according to the JSON? Should the headers be declared? or is it possible to get the header from the JSON? How about the corresponding values? Does Apps Ssripts accept foreach loop?

I think that it's yes. When I saw your JSON data, it seems that several properties are existing. And, in your output situation, it is required to put the values in order. In this case, when the header value is used, the script can be easily prepared.

Sample script:

In order to achieve your goal, how about the following sample script?

function myFunction() {
  // 1. Retrieve source and destination sheets.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [srcSheet, dstSheet] = ["Sheet1", "Sheet2"].map(s => ss.getSheetByName(s));

  // 2. Retrieve values from source sheet.
  const values = srcSheet.getRange("A1:A"   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"];
  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);
}

• This sample script retrieves the values from column "A" of the source sheet "Sheet1" and converted the values to the 2-dimensional array and put the array to the destination sheet "Sheet2".
• Please modify the sheet names of the source "Sheet1" and destination "Sheet2" sheets for your actual situation, respectively.

References:

• map()
• getValues()
• setValues(values)