How to extract only one field from JSON into Google Sheets using Google Sheets app script-CodePudding

function import_inventory_test(e) {

var options = {

       "method" : "GET",
       "headers" : {
       'Content-Type' : 'application/json' ,
       'Prefer': 'code=200',
       'Prefer':'dynamic=true',
       "clientId" : "1",
       "key": "1"}}
 

  var text = UrlFetchApp.fetch("https://stoplight.io/mocks/flowhub/public-developer-portal/24055485/v0/inventory",options).getContentText();
  var json = JSON.parse(text);
     

}

How can convert one field from the response into an entire column in google sheets?

For example, let's say that there are 100 items in the JSON above and I want to extract only the "sku" for all 100 products and I would like to enter all of those 100 SKU's into the "A" column in Google Sheets?

CodePudding user response：

If you want to retrieve the values of sku in column "A" using your script, how about the following modification?

Modified script:

function import_inventory_test(e) {
  var options = {
    "method": "GET",
    "headers": {
      'Content-Type': 'application/json',
      'Prefer': 'code=200',
      'Prefer': 'dynamic=true',
      "clientId": "1",
      "key": "1",
    }
  }
  var text = UrlFetchApp.fetch("https://stoplight.io/mocks/flowhub/public-developer-portal/24055485/v0/inventory?max=100", options).getContentText();
  var json = JSON.parse(text);

  // I added the below script.
  var values = json.data.map(({ sku }) => [sku]);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set your sheet name.
  sheet.getRange(1, 1, values.length).setValues(values);
}

Note:

About For example, let's say that there are 100 items in the JSON above and I want to extract only the "sku" for all 100 products and I would like to enter all of those 100 SKU's into the "A" column in Google Sheets?, when I tested your script, 20 items are returned in json.data. If you want to retrieve 100 SKU values, how about the following modification?

function import_inventory_test(e) {
  var options = {
    "method": "GET",
    "headers": {
      'Content-Type': 'application/json',
      'Prefer': 'code=200',
      'Prefer': 'dynamic=true',
      "clientId": "1",
      "key": "1",
    }
  }

  // I added the below script.
  var requests = [...Array(5)].map(_ => ({ url: "https://stoplight.io/mocks/flowhub/public-developer-portal/24055485/v0/inventory", ...options }));
  var values = UrlFetchApp.fetchAll(requests).flatMap(r => JSON.parse(r.getContentText()).data.map(({ sku }) => [sku]));
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set your sheet name.
  sheet.getRange(1, 1, values.length).setValues(values);
}

In this modification, the empty values and the duplicated values are included. If you want to remove them, how about the following modification?

function import_inventory_test(e) {
  var options = {
    "method": "GET",
    "headers": {
      'Content-Type': 'application/json',
      'Prefer': 'code=200',
      'Prefer': 'dynamic=true',
      "clientId": "1",
      "key": "1",
    }
  }

  // I added the below script.
  var max = 100;
  var requests = [...Array(6)].map(_ => ({ url: "https://stoplight.io/mocks/flowhub/public-developer-portal/24055485/v0/inventory", ...options }));
  var values = [...new Set(UrlFetchApp.fetchAll(requests).flatMap(r => JSON.parse(r.getContentText()).data.map(({ sku }) => sku).filter(String)))].splice(0, max).map(e => [e]);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set your sheet name.
  sheet.getRange(1, 1, values.length).setValues(values);
}

Reference:

map()