CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Kal El
Kal El

Reputation: 73

How to extract only one field from JSON into Google Sheets using Google Sheets app 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",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?

Upvotes: 1

Views: 897

Answers (1)

Tanaike
Tanaike

Reputation: 201338

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:

Upvotes: 2

Related Questions