How to Import JSON data to google sheet with for loop-CodePudding

I almost import my JSON data from API. But I don't know what is exactly the value of setvalue() in my case. I want to get a google sheet of the product list loop automatically.

The final output that I want to get is the product list continually looping from page 1 to the last page.
When I run my code on Apps script, I got the output like the screenshot below.
But, It repeats only the first product of the product list.

function dearAPI(endpoint,sheetname,dig) {
  const dataRows = [];
  let pagenumber = 1;

   const a = UrlFetchApp.fetch(
    'https://inventory.dearsystems.com/externalapi/v2/product?page=1&limit=10',
    {
      'method':'get',headers:
      {
        "api-auth-accountid": accountID,
        "api-auth-applicationkey": secret,
      },
        "contentType": 'application/json'
    });

    var sheet = SpreadsheetApp.getActiveSheet();
    var json = JSON.parse(a.getContentText());
    var headers = [Object.keys(json.Products[0])];
    sheet.getRange(1,1,headers.length,headers[0].length).setValues(headers);

    for(var i = 0; i < json.Products.length; i  ){
        var rows = [Object.values(json.Products[i])];
        var final = Object.values(json.Products[i]);
        getFinal = [final[i]];
         
    for (var j = 0; j < json.Products.length; j  ){
        sheet.getRange(2, i 1, json.Products.length, rows[0].length).setValue(getFinal);
          }
      }
}

CodePudding user response：

Suggestion:

Assuming this manually created sample JSON below has the same JSON structure from your API:

{
  Products=[
    {
      Name=DarknessPumpLarge,
      DropShipMode=NoDropShip,
      Brand=Darkness,
      DefaultLocation=Toronto,
      Category=Accessories,
      CostingMethod=FIFO,
      Type=Stock
    },
    {
      DropShipMode=NoDropShip,
      Name=AppleiPhone,
      Type=Stock,
      DefaultLocation=California,
      Category=Gadget,
      Brand=Apple,
      CostingMethod=FIFO
    },
    {
      CostingMethod=FIFO,
      Brand=Samsung,
      DropShipMode=NoDropShip,
      DefaultLocation=SouthKorea,
      Name=SamsungGalaxyS21,
      Category=Gadget,
      Type=Stock
    }
  ]
}

NOTE: It would also be better if you can share a snippet of your JSON value for better replication.

You can try this implementation, the script starting from the line with var = headers:

function dearAPI() {
  var getFinal = []; //Assuming the `getFinal` was initialized as `an array variable
  
  //Sample JSON value manually imputted here for replication
  const json = {
"Products":[
              {"Name":"Darkness Pump Large", "Category":"Accessories", "Brand":"Darkness", "Type":"Stock", "CostingMethod":"FIFO", "DropShipMode":"No Drop Ship", "DefaultLocation":"Toronto"},
              {"Name":"Apple iPhone", "Category":"Gadget", "Brand":"Apple", "Type":"Stock", "CostingMethod":"FIFO", "DropShipMode":"No Drop Ship", "DefaultLocation":"California"},
              {"Name":"Samsung Galaxy S21", "Category":"Gadget", "Brand":"Samsung", "Type":"Stock", "CostingMethod":"FIFO", "DropShipMode":"No Drop Ship", "DefaultLocation":"South Korea"}
            ]
  }

  var headers = [Object.keys(json.Products[0])];
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1,1,headers.length,headers[0].length).setValues(headers);

  for(var i = 0; i < json.Products.length; i  ){
      var final = Object.values(json.Products[i]);
      getFinal.push(final);
  }
  sheet.getRange(2,1,getFinal.length,getFinal[0].length).setValues(getFinal);
}

Suggestion:

Sample Result: