CODEWITHSUNDEEP
jsongoogle-sheets
Pete Evans
Pete Evans

Reputation: 1

Google Sheets JSON Parse Loop Action

Worksheet Link: https://docs.google.com/spreadsheets/d/1MLlKnlS-IB0OwWGIJ9tVI38pvqoCktd1HKlpofzn8cI/edit?usp=sharing

Hello all. I am new to JSON parsing within GAS and hope someone can help. It seems like it should be something fairly simple, but I cant figure it out.

The JSON contains 2 properties that I am trying to extract, case_upc_14 and salsify:created_at. I am able to successfully extract individual values, but I need help creating a loop to extract all case_upc_14 and salsify:created_at properties and return the values on the export tab.

This is what I have, any assistance would be greatly appreciated. 

function parse() {

  var s = SpreadsheetApp.getActiveSpreadsheet(); 
  var t = s.getSheetByName('Sheet2'); 
  var response = UrlFetchApp.fetch('https://salsify-dandelion.s3-external-1.amazonaws.com/1/3889/user_data_export/1/2017/09/24/15%3A56%3A46-e227b249/export.json?response-content-disposition=attachment%3B%20filename%3D%22product-feed.json%22&response-content-type=application%2Fjson&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAJSAUHNZHZGLUDXDA%2F20170924%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20170924T160544Z&X-Amz-Expires=604800&X-Amz-SignedHeaders=host&X-Amz-Signature=b9407a842978363265472d27f4e8026506ef7d0a7e859a7c39feb0f4870fb9ad');

  var json = response.getContentText();
  var data = JSON.parse(json);

  Logger.log(data[4]['products'][1]['case_upc_14']);
  Logger.log(data[4]['products'][1]['salsify:created_at']);


}

Upvotes: 0

Views: 441

Answers (1)

Tanaike
Tanaike

Reputation: 201513

How about following modification? It seems that you try to import the retrieved data to Spreadsheet. So the modified script retrieves case_upc_14 and salsify:created_at, and imports the data to Sheet2 of Spreadsheet.

Modified script :

function parse() {
  var s = SpreadsheetApp.getActiveSpreadsheet(); 
  var t = s.getSheetByName('Sheet2'); 
  var response = UrlFetchApp.fetch('https://salsify-dandelion.s3-external-1.amazonaws.com/1/3889/user_data_export/1/2017/09/24/15%3A56%3A46-e227b249/export.json?response-content-disposition=attachment%3B%20filename%3D%22product-feed.json%22&response-content-type=application%2Fjson&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAJSAUHNZHZGLUDXDA%2F20170924%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20170924T160544Z&X-Amz-Expires=604800&X-Amz-SignedHeaders=host&X-Amz-Signature=b9407a842978363265472d27f4e8026506ef7d0a7e859a7c39feb0f4870fb9ad');
  var json = response.getContentText();
  var data = JSON.parse(json);

  var ar = [];
  data[4].products.forEach(function(e) {
    ar.push([e['salsify:created_at'], e['case_upc_14']])
  });
  t.getRange(1,1,ar.length,ar[0].length).setValues(ar);
}

If I misunderstand your question, I'm sorry.

Upvotes: 1

Related Questions